using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations.Schema; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System.Configuration; using System.Linq.Expressions; using System.Reflection; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using CellType = NPOI.SS.UserModel.CellType; using CellValue = DocumentFormat.OpenXml.Spreadsheet.CellValue; namespace Comm { public class ExcelHelper { /// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param name="sheetName">excel工作薄sheet的名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> public DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn) { DataTable data = new DataTable(); int startRow = 0; try { IWorkbook workbook = null; // FileStream fs = null; //bool disposed; ISheet sheet = null; using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(fs); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(fs); if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { data.TableName = sheetName; IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } } return data; } } catch (Exception ex) { // Console.WriteLine("Exception: " + ex.Message); return null; } } /// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> public DataSet ExcelToDataSet(string fileName, bool isFirstRowColumn) { DataSet ds = new DataSet(); int startRow = 0; try { //var workbook = null; // FileStream fs = null; //bool disposed; //ISheet sheet = null; using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { //if (fileName.IndexOf(".xlsx") > 0) // 2007版本 // workbook = new XSSFWorkbook(fs); //else if (fileName.IndexOf(".xls") > 0) // 2003版本 HSSFWorkbook workbook = new HSSFWorkbook(fs); //if (sheetName != null) //{ // sheet = workbook.GetSheet(sheetName); // if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet // { // sheet = workbook.GetSheetAt(0); // } //} //else //{ // sheet = workbook.GetSheetAt(0); //} //InputStream input = new FileInputStream("文件路径"); for (int t = 0; t < workbook.NumberOfSheets; t++) { ISheet sheet = workbook.GetSheetAt(t); DataTable data = new DataTable(); if (sheet != null) { data.TableName = sheet.SheetName; IRow firstRow = sheet.GetRow(1); int cellCount = 5; //一行最后一个cell的编号 即总的列数 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum + 1; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 2; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null DataRow dataRow = data.NewRow(); for (int j = 1; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null dataRow[j - 1] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } ds.Tables.Add(data); } } return ds; } } catch (Exception ex) { // Console.WriteLine("Exception: " + ex.Message); return null; } } /// <summary> /// 获取DataTable /// </summary> /// <param name="fs"></param> /// <param name="typestring">通过.xlsx;.xls确认excel</param> /// <param name="sheetindex"></param> /// <param name="firstRowColumn"></param> /// <returns></returns> public DataTable ExcelToDataTable(Stream fs, string typestring, int sheetindex, int firstRowColumn) { DataTable data = new DataTable(); int startRow = 0; try { IWorkbook workbook = null; // FileStream fs = null; //bool disposed; ISheet sheet = null; if (typestring.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(fs); else if (typestring.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(fs); sheet = workbook.GetSheetAt(0); if (sheet != null) { IRow firstRow = sheet.GetRow(firstRowColumn); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 //if (isFirstRowColumn) //{ for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { if (!data.Columns.Contains(cellValue)) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } else { DataColumn column = new DataColumn(cellValue + i.ToString()); data.Columns.Add(column); } } } } startRow = sheet.FirstRowNum + 1 + firstRowColumn; //} //else //{ // startRow = sheet.FirstRowNum; //} //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) {//同理,没有数据的单元格都默认是null if (row.GetCell(j) != null) { row.GetCell(j).SetCellType(CellType.String); dataRow[j] = row.GetCell(j).StringCellValue; } } data.Rows.Add(dataRow); } } fs.Dispose(); return data; } catch (Exception ex) { fs.Dispose(); // Console.WriteLine("Exception: " + ex.Message); return null; } } /// <summary> /// DataTable转换成Excel文档流(导出数据量超出65535条,分sheet) /// </summary> /// <param name="table"></param> /// <returns></returns> public Stream RenderDataTableToExcel(DataTable sourceTable) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); int dtRowsCount = sourceTable.Rows.Count; int SheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtRowsCount) / 65536)); int SheetNum = 1; int rowIndex = 1; int tempIndex = 1; //标示 ISheet sheet = workbook.CreateSheet("sheet1" + SheetNum); for (int i = 0; i < dtRowsCount; i++) { if (i == 0 || tempIndex == 1) { IRow headerRow = sheet.CreateRow(0); foreach (DataColumn column in sourceTable.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); } HSSFRow dataRow = (HSSFRow)sheet.CreateRow(tempIndex); foreach (DataColumn column in sourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(sourceTable.Rows[i][column].ToString()); } if (tempIndex == 65535) { SheetNum++; sheet = workbook.CreateSheet("sheet" + SheetNum);// tempIndex = 0; } rowIndex++; tempIndex++; //AutoSizeColumns(sheet); } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; // headerRow = null; workbook = null; return ms; } public Stream RenderDataTableToXlsxExcel(DataTable sourceTable, int sheetsSize = 65536) { MemoryStream ms = new MemoryStream(); using (var workbook = SpreadsheetDocument.Create(ms, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); var sheets = new Sheets(); workbookPart.Workbook = new Workbook(sheets); int total = sourceTable.Rows.Count; int SheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(total) / sheetsSize)); for (var i = 1u; i <= SheetCount; i++) { var worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); var sheetData = new SheetData(); worksheetPart.Worksheet = new Worksheet(sheetData); var sheet = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), Name = $"sheet{i}", SheetId = i }; sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List<String> columns = new List<string>(); foreach (DataColumn column in sourceTable.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); DataTable current = sourceTable.AsEnumerable().Skip((int)(i - 1) * sheetsSize) .Take(sheetsSize).CopyToDataTable(); foreach (DataRow dsrow in current.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); // newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } } workbook.Close(); } ms.Position = 0; return ms; } /// <summary> /// 能合并行的导出 /// </summary> /// <param name="sourceTable"></param> /// <param name="mergeindex">根据哪列判断合并 从0开始算</param> /// <param name="hiddenmergeindex">是否隐藏合并列</param> /// <param name="columnWidthArr">列宽度</param> /// <param name="mergeArr">需要合并的列 从0开始算</param> /// <param name=""></param> /// <returns></returns> public Stream NPOIDataTableToExcel(DataTable sourceTable, int mergeindex, bool hiddenmergeindex = true, int[] columnWidthArr = null, int[] mergeArr = null) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); int dtRowsCount = sourceTable.Rows.Count; int SheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtRowsCount) / 65536)); int SheetNum = 1; int rowIndex = 1; int tempIndex = 1; //标示 ISheet sheet = workbook.CreateSheet("sheet1" + SheetNum); for (int i = 0; i < dtRowsCount; i++) { if (i == 0 || tempIndex == 1) { IRow headerRow = sheet.CreateRow(0); foreach (DataColumn column in sourceTable.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); } HSSFRow dataRow = (HSSFRow)sheet.CreateRow(tempIndex); foreach (DataColumn column in sourceTable.Columns) { ICellStyle style = workbook.CreateCellStyle();//创建样式 style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 ICell cell = dataRow.CreateCell(column.Ordinal); cell.SetCellValue(sourceTable.Rows[i][column].ToString()); cell.CellStyle = style; } if (tempIndex == 65535) { SheetNum++; sheet = workbook.CreateSheet("sheet" + SheetNum);// tempIndex = 0; } rowIndex++; tempIndex++; //AutoSizeColumns(sheet); } /*设置列宽*/ if (columnWidthArr != null && columnWidthArr.Length > 0) { for (int c = 0; c < columnWidthArr.Length; c++) { sheet.SetColumnWidth(c, columnWidthArr[c] * 256); } } /*合并单元格*/ if (mergeArr != null && mergeArr.Count() > 0) { for (int i = 0; i < dtRowsCount; i++) { if (i > 0 && sourceTable.Rows[i][mergeindex].ToString() == sourceTable.Rows[i - 1][mergeindex].ToString()) { for (int j = 0; j < mergeArr.Count(); j++) { sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i + 1, mergeArr[j], mergeArr[j])); } } } } /*是否隐藏判断合并的列*/ if (mergeindex >= 0) { sheet.SetColumnHidden(mergeindex, hiddenmergeindex); } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; // headerRow = null; workbook = null; return ms; } /// <summary> /// NPOI导出EXCEL /// </summary> /// <param name="dt">数据源</param> /// <param name="title">导出文件的名称</param> /// <param name="array">列宽数组</param> eventImages 扩展 public Stream NpoiExcel(DataTable dt, int[] array, bool isShowUrl = false) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0); headerrow.Height = 30 * 20; ICellStyle style = book.CreateCellStyle(); style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.WrapText = true; style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; /*标题*/ for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = headerrow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(dt.Columns[i].ColumnName); /*设置列宽*/ if (array.Length > 0) { for (int c = 0; c < array.Length; c++) { sheet.SetColumnWidth(c, array[c] * 256); } } } /*内容*/ for (int j = 1; j <= dt.Rows.Count; j++) { NPOI.SS.UserModel.IRow contentrow = sheet.CreateRow(j); contentrow.Height = 100 * 15; for (int k = 0; k < dt.Columns.Count; k++) { ICell cell = contentrow.CreateCell(k); cell.CellStyle = style; if (dt.Columns[k].ColumnName.Contains("图片")) { if (!isShowUrl)//图片下载 { if (dt.Rows[j - 1][k] != null) { string[] pathlist = dt.Rows[j - 1][k].ToString().Split(','); if (pathlist.Count() > 0) { int index = 0; foreach (var picurl in pathlist) { AddCellPicture(sheet, book, picurl, j, k, index); index++; } } } } else//给图片链接 { if (dt.Rows[j - 1][k] != null) { string[] pathlist = dt.Rows[j - 1][k].ToString().Split(','); if (pathlist.Count() > 0) { int index = 0; foreach (var picurl in pathlist) { if (index > 0) { cell = contentrow.CreateCell(k + index); } cell.CellStyle = style; cell.SetCellValue(picurl); index++; } } } } } else { cell.CellStyle = style; cell.SetCellValue(dt.Rows[j - 1][k].ToString()); } } } MemoryStream ms = new MemoryStream(); book.Write(ms); return ms; } /// <summary> /// 向sheet插入图片 /// </summary> /// <param name="sheet"></param> /// <param name="workbook"></param> /// <param name="fileurl"></param> /// <param name="row"></param> /// <param name="col"></param> private void AddCellPicture(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col, int index) { try { if (File.Exists(fileurl) == true) { byte[] bytes = System.IO.File.ReadAllBytes(fileurl); if (!string.IsNullOrEmpty(fileurl)) { int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col + index, row, col + 1 + index, row + 1); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); } } } catch (Exception ex) { throw ex; } } } }