利用npoi把多个DataTable导入Excel多个sheet中
｛
题外拓展：把datatable插入dataset
DataTable fuben = new DataTable();//定义的datatable
fuben = table.Tables[0].Copy(); 把获取的datatable复制到新的表中
fuben.TableName = "sheet" + userid + "";定义表名
ds.Tables.Add(fuben);插入dataset
之所以这么麻烦是因为 我用了返回dataset的方法来获取的datatable，导致提示此datatable已经属于另外一个dataset，所以只有把他复制到另外一个表，然后在插入dataset
｝
我就直接把类复制进来了
调用的时候
参数ds为你定义的DataSet
ExcelRender.RenderToExcel(ds, Context, "报表.xls");
using System.Data; using System.IO; using System.Text; using System.Web; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel;////// 使用NPOI操作Excel，无需Office COM组件 /// Created By 囧月 http://lwme.cnblogs.com /// 部分代码取自http://msdn.microsoft.com/zh-tw/ee818993.aspx /// NPOI是POI的.NET移植版本，目前稳定版本中仅支持对xls文件（Excel 97-2003）文件格式的读写 /// NPOI官方网站http://npoi.codeplex.com/ /// public class ExcelRender {/// /// 根据Excel列类型获取列的值/// /// Excel列/// private static string GetCellValue(ICell cell){if (cell == null)return string.Empty;switch (cell.CellType){case CellType.BLANK:return string.Empty;case CellType.BOOLEAN:return cell.BooleanCellValue.ToString();case CellType.ERROR:return cell.ErrorCellValue.ToString();case CellType.NUMERIC:case CellType.Unknown:default:return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a numbercase CellType.STRING:return cell.StringCellValue;case CellType.FORMULA:try{HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);e.EvaluateInCell(cell);return cell.ToString();}catch{return cell.NumericCellValue.ToString();}}}/// /// 自动设置Excel列宽/// /// Excel表private static void AutoSizeColumns(ISheet sheet){if (sheet.PhysicalNumberOfRows > 0){IRow headerRow = sheet.GetRow(0);for (int i = 0, l = headerRow.LastCellNum; i < l; i++){sheet.AutoSizeColumn(i);}}}/// /// 保存Excel文档流到文件/// /// Excel文档流/// 文件名private static void SaveToFile(MemoryStream ms, string fileName){using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)){byte[] data = ms.ToArray();fs.Write(data, 0, data.Length);fs.Flush();data = null;}}/// /// 输出文件到浏览器/// /// Excel文档流/// HTTP上下文/// 文件名private static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName){if (context.Request.Browser.Browser == "IE")fileName = HttpUtility.UrlEncode(fileName);context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);context.Response.BinaryWrite(ms.ToArray());}/// /// DataReader转换成Excel文档流/// /// /// public static MemoryStream RenderToExcel(IDataReader reader){MemoryStream ms = new MemoryStream();using (reader){IWorkbook workbook = new HSSFWorkbook();ISheet sheet = workbook.CreateSheet();IRow headerRow = sheet.CreateRow(0);int cellCount = reader.FieldCount;// handling header.for (int i = 0; i < cellCount; i++){headerRow.CreateCell(i).SetCellValue(reader.GetName(i));}// handling value.int rowIndex = 1;while (reader.Read()){IRow dataRow = sheet.CreateRow(rowIndex);for (int i = 0; i < cellCount; i++){dataRow.CreateCell(i).SetCellValue(reader[i].ToString());}rowIndex++;}AutoSizeColumns(sheet);workbook.Write(ms);ms.Flush();ms.Position = 0;}return ms;}/// /// DataReader转换成Excel文档流，并保存到文件/// /// /// 保存的路径public static void RenderToExcel(IDataReader reader, string fileName){using (MemoryStream ms = RenderToExcel(reader)){SaveToFile(ms, fileName);}}/// /// DataReader转换成Excel文档流，并输出到客户端/// /// /// HTTP上下文/// 输出的文件名public static void RenderToExcel(IDataReader reader, HttpContext context, string fileName){using (MemoryStream ms = RenderToExcel(reader)){RenderToBrowser(ms, context, fileName);}}/// /// DataTable转换成Excel文档流/// /// /// public static MemoryStream RenderToExcel(DataTable table){MemoryStream ms = new MemoryStream();using (table){IWorkbook workbook = new HSSFWorkbook();ISheet sheet = workbook.CreateSheet("sheet1");IRow headerRow = sheet.CreateRow(0);// handling header.foreach (DataColumn column in table.Columns)headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value// handling value.int rowIndex = 1;foreach (DataRow row in table.Rows){IRow dataRow = sheet.CreateRow(rowIndex);foreach (DataColumn column in table.Columns){dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());}rowIndex++;}AutoSizeColumns(sheet);workbook.Write(ms);ms.Flush();ms.Position = 0;}return ms;}/// /// DataSet转换成Excel文档流/// /// /// public static MemoryStream RenderToExcel(DataSet ds){MemoryStream ms = new MemoryStream();IWorkbook workbook = new HSSFWorkbook();for (int i = 0; i < ds.Tables.Count; i++){DataTable table = ds.Tables[i];ISheet sheet = workbook.CreateSheet(table.TableName);IRow headerRow = sheet.CreateRow(0);// handling header.foreach (DataColumn column in table.Columns)headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value// handling value.int rowIndex = 1;foreach (DataRow row in table.Rows){IRow dataRow = sheet.CreateRow(rowIndex);foreach (DataColumn column in table.Columns){dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());}rowIndex++;}AutoSizeColumns(sheet);workbook.Write(ms);ms.Flush();ms.Position = 0;}return ms;}/// /// DataTable转换成Excel文档流，并保存到文件/// /// /// 保存的路径public static void RenderToExcel(DataTable table, string fileName){using (MemoryStream ms = RenderToExcel(table)){SaveToFile(ms, fileName);}}/// /// DataTable转换成Excel文档流，并输出到客户端/// /// /// /// 输出的文件名public static void RenderToExcel(DataTable table, HttpContext context, string fileName){using (MemoryStream ms = RenderToExcel(table)){RenderToBrowser(ms, context, fileName);}}/// /// DataSet转换成Excel文档流，并输出到客户端/// /// /// /// 输出的文件名public static void RenderToExcel(DataSet ds, HttpContext context, string fileName){using (MemoryStream ms = RenderToExcel(ds)){RenderToBrowser(ms, context, fileName);}}/// /// Excel文档流是否有数据/// /// Excel文档流/// public static bool HasData(Stream excelFileStream){return HasData(excelFileStream, 0);}/// /// Excel文档流是否有数据/// /// Excel文档流/// 表索引号，如第一个表为0/// public static bool HasData(Stream excelFileStream, int sheetIndex){using (excelFileStream){IWorkbook workbook = new HSSFWorkbook(excelFileStream);if (workbook.NumberOfSheets > 0){if (sheetIndex < workbook.NumberOfSheets){ISheet sheet = workbook.GetSheetAt(sheetIndex);return sheet.PhysicalNumberOfRows > 0;}}}return false;}/// /// Excel文档流转换成DataTable/// 第一行必须为标题行/// /// Excel文档流/// 表名称/// public static DataTable RenderFromExcel(Stream excelFileStream, string sheetName){return RenderFromExcel(excelFileStream, sheetName, 0);}/// /// Excel文档流转换成DataTable/// /// Excel文档流/// 表名称/// 标题行索引号，如第一行为0/// public static DataTable RenderFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex){DataTable table = null;using (excelFileStream){IWorkbook workbook = new HSSFWorkbook(excelFileStream);{ISheet sheet = workbook.GetSheet(sheetName);table = RenderFromExcel(sheet, headerRowIndex);}}return table;}/// /// Excel文档流转换成DataTable/// 默认转换Excel的第一个表/// 第一行必须为标题行/// /// Excel文档流/// public static DataTable RenderFromExcel(Stream excelFileStream){return RenderFromExcel(excelFileStream, 0, 0);}/// /// Excel文档流转换成DataTable/// 第一行必须为标题行/// /// Excel文档流/// 表索引号，如第一个表为0/// public static DataTable RenderFromExcel(Stream excelFileStream, int sheetIndex){return RenderFromExcel(excelFileStream, sheetIndex, 0);}/// /// Excel文档流转换成DataTable/// /// Excel文档流/// 表索引号，如第一个表为0/// 标题行索引号，如第一行为0/// public static DataTable RenderFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex){DataTable table = null;using (excelFileStream){IWorkbook workbook = new HSSFWorkbook(excelFileStream);ISheet sheet = workbook.GetSheetAt(sheetIndex);table = RenderFromExcel(sheet, headerRowIndex);}return table;}/// /// Excel表格转换成DataTable/// /// 表格/// 标题行索引号，如第一行为0/// private static DataTable RenderFromExcel(ISheet sheet, int headerRowIndex){DataTable table = new DataTable();IRow headerRow = sheet.GetRow(headerRowIndex);int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCellsint rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1//handling header.for (int i = headerRow.FirstCellNum; i < cellCount; i++){DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);table.Columns.Add(column);}for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++){IRow row = sheet.GetRow(i);DataRow dataRow = table.NewRow();if (row != null){for (int j = row.FirstCellNum; j < cellCount; j++){if (row.GetCell(j) != null)dataRow[j] = GetCellValue(row.GetCell(j));}}table.Rows.Add(dataRow);}return table;}/// /// Excel文档导入到数据库/// 默认取Excel的第一个表/// 第一行必须为标题行/// /// Excel文档流/// 插入语句/// 更新到数据库的方法/// public static int RenderToDb(Stream excelFileStream, string insertSql, DBAction dbAction){return RenderToDb(excelFileStream, insertSql, dbAction, 0, 0);}public delegate int DBAction(string sql, params IDataParameter[] parameters);/// /// Excel文档导入到数据库/// /// Excel文档流/// 插入语句/// 更新到数据库的方法/// 表索引号，如第一个表为0/// 标题行索引号，如第一行为0/// public static int RenderToDb(Stream excelFileStream, string insertSql, DBAction dbAction, int sheetIndex, int headerRowIndex){int rowAffected = 0;using (excelFileStream){IWorkbook workbook = new HSSFWorkbook(excelFileStream);ISheet sheet = workbook.GetSheetAt(sheetIndex);StringBuilder builder = new StringBuilder();IRow headerRow = sheet.GetRow(headerRowIndex);int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCellsint rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++){IRow row = sheet.GetRow(i);if (row != null){builder.Append(insertSql);builder.Append(" values (");for (int j = row.FirstCellNum; j < cellCount; j++)
{builder.AppendFormat("'{0}',", GetCellValue(row.GetCell(j)).Replace("'", "''"));}builder.Length = builder.Length - 1;builder.Append(");");}if ((i % 50 == 0 || i == rowCount) && builder.Length > 0){//每50条记录一次批量插入到数据库rowAffected += dbAction(builder.ToString());builder.Length = 0;}}}return rowAffected;} }
转载于:https://www.cnblogs.com/xuxiaoshuan/p/4434387.html
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！