C# OpenXml组件
using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Reflection; using System.Text; using System.Text.RegularExpressions; using System.Threading.Tasks; namespace DYData.Comm { public class OpenXmlHelper { #region 导入 /// <summary> /// 按照给定的地址读取Excel转换成DataTable /// </summary> /// <param name="path">路径</param> /// <param name="page">页码</param> /// <param name="pagesize">页行数</param> /// <param name="sheetName">Excel的Sheet名;默认第一个Sheet</param> /// <param name="index">列头行;默认第一列</param> /// <returns></returns> /// OpenXml只支持Excel2007版后的.xlsx public static DataTable Read(string path, int page = 0, int pagesize = 0, string sheetName = null, int index = 1) { try { DataTable dt = new DataTable(); if (!path.ToLower().Trim().EndsWith(".xlsx")) { throw new Exception("OpenXml组件只支持Office Excel2007版后的.xlsx格式文件"); } using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, false)) { //打开Stream IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>(); if (!string.IsNullOrEmpty(sheetName)) { sheets = sheets.Where(s => s.Name.ToString().ToLower() == sheetName.ToLower()); if (sheets.Count() == 0) { throw new Exception("没有找到该Sheet"); } } WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id); //获取Excel中共享数据 SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable; IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行 foreach (Row row in rows) { if (row.RowIndex == index)//Excel的列名列 { GetDataColumn(row, stringTable, ref dt); break; } } if (page != 0 && pagesize != 0) { rows = rows.Skip((pagesize * (page - 1)) + 1).Take(pagesize); foreach (Row row in rows) { if (row.RowIndex != index) { GetDataRow(row, stringTable, ref dt, document.WorkbookPart);//Excel中行数大于列头的都作为数据列处理 } } } else { foreach (Row row in rows) { if (row.RowIndex != index) { GetDataRow(row, stringTable, ref dt, document.WorkbookPart);//Excel中行数大于列头的都作为数据列处理 } } } return dt; } } catch (Exception ex) { throw ex; } } /// <summary> /// 按照给定的流读取Excel转换成DataTable /// </summary> /// <param name="path">路径</param> /// <param name="page">页码</param> /// <param name="pagesize">页行数</param> /// <param name="sheetName">Excel的Sheet名;默认第一个Sheet</param> /// <param name="index">列头行;默认第一列</param> /// <returns></returns> /// OpenXml只支持Excel2007版后的.xlsx public static DataTable Read(Stream stream, int page = 0, int pagesize = 0, string sheetName = null, int index = 1) { try { DataTable dt = new DataTable(); using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false)) { //打开Stream IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>(); if (!string.IsNullOrEmpty(sheetName)) { sheets = sheets.Where(s => s.Name.ToString().ToLower() == sheetName.ToLower()); if (sheets.Count() == 0) { throw new Exception("没有找到该Sheet"); } } WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id); //获取Excel中共享数据 SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable; IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行 foreach (Row row in rows) { if (row.RowIndex == index)//Excel的列名列 { GetDataColumn(row, stringTable, ref dt); } else { break; } } if (page != 0 && pagesize != 0) { rows = rows.Skip((pagesize * (page - 1)) + 1).Take(pagesize); foreach (Row row in rows) { if (row.RowIndex != index) { GetDataRow(row, stringTable, ref dt, document.WorkbookPart);//Excel中行数大于列头的都作为数据列处理 } } } else { foreach (Row row in rows) { if (row.RowIndex != index) { GetDataRow(row, stringTable, ref dt, document.WorkbookPart);//Excel中行数大于列头的都作为数据列处理 } } } return dt; } } catch (Exception ex) { throw ex; } } /// <summary> /// 获取excel列头 /// </summary> /// <returns></returns> /// OpenXml只支持Excel2007版后的.xlsx public static DataTable ReadHeaders(Stream stream, ref int count, string sheetName = null, int index = 1) { try { DataTable dt = new DataTable(); using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false)) { //打开Stream IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>(); if (!string.IsNullOrEmpty(sheetName)) { sheets = sheets.Where(s => s.Name.ToString().ToLower() == sheetName.ToLower()); if (sheets.Count() == 0) { throw new Exception("没有找到该Sheet"); } } WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id); //获取Excel中共享数据 SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable; IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行 foreach (Row row in rows) { if (row.RowIndex == index)//Excel的列名列 { GetDataColumn(row, stringTable, ref dt); } else { break; } } count = rows.Count() - 1; return dt; } } catch (Exception ex) { throw ex; } } /// <summary> /// Excel数据列总行数(不包括列头) /// </summary> /// <param name="path"></param> /// <param name="sheetName"></param> /// <param name="index"></param> /// <returns></returns> public static int GetExcelCount(string path, string sheetName = null, int index = 1) { try { DataTable dt = new DataTable(); if (!path.ToLower().Trim().EndsWith(".xlsx")) { throw new Exception("OpenXml组件只支持Office Excel2007版后的.xlsx格式文件"); } using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, false)) { //打开Stream IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>(); if (!string.IsNullOrEmpty(sheetName)) { sheets = sheets.Where(s => s.Name.ToString().ToLower() == sheetName.ToLower()); if (sheets.Count() == 0) { throw new Exception("没有找到该Sheet"); } } WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id); //获取Excel中共享数据 SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable; IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行 return rows.Count() - 1; } } catch (Exception ex) { throw ex; } } /// <summary> /// 获取Excel原始列下标 /// </summary> /// <param name="str">原始列名</param> /// <returns></returns> private static int GetIndexNumber(string value) { if (string.IsNullOrEmpty(value)) { return -1; } char[] chars = value.ToLower().ToCharArray(); int index = 0; for (int i = 0; i < chars.Length; i++) { index += ((int)chars[i] - (int)'a' + 1) * (int)Math.Pow(26, chars.Length - i - 1); } return index - 1; } /// <summary> /// 构建DataTable的列 /// </summary> /// <param name="row">OpenXML定义的Row对象</param> /// <param name="stringTablePart"></param> /// <param name="dt">须要返回的DataTable对象</param> /// <returns></returns> private static void GetDataColumn(Row row, SharedStringTable stringTable, ref DataTable dt) { DataColumn col = new DataColumn(); Dictionary<string, int> columnCount = new Dictionary<string, int>(); foreach (Cell cell in row) { string cellVal = GetValue(cell, stringTable); col = new DataColumn(cellVal); if (IsContainsColumn(dt, col.ColumnName)) { if (!columnCount.ContainsKey(col.ColumnName)) columnCount.Add(col.ColumnName, 0); col.ColumnName = col.ColumnName + (columnCount[col.ColumnName]++); } dt.Columns.Add(col); } } /// <summary> /// 构建DataTable的每一行数据,并返回该Datatable /// </summary> /// <param name="row">OpenXML的行</param> /// <param name="stringTablePart"></param> /// <param name="dt">DataTable</param> private static void GetDataRow(Row row, SharedStringTable stringTable, ref DataTable dt, WorkbookPart workbookPart) { //读取数据 DataRow dr = dt.NewRow(); int number = 0; foreach (Cell cell in row) { //获取Excel列头 var column = Regex.Replace(cell.CellReference.Value, "[0-9]", ""); //列头转换成下标 var index = GetIndexNumber(column); if (index < 0) { continue; } string cellVal = GetValue(cell, stringTable); if (!string.IsNullOrEmpty(cellVal)) { if (index < dt.Columns.Count) { if (cell.DataType != null) { switch ((int)cell.DataType?.Value) { case (int)CellValues.Date: cellVal = Convert.ToDateTime(cellVal).ToString("yyyy-MM-dd HH:mm:ss"); break; default: break; } } else { #region NumberFormatId 含义 //0 = 'General'; //1 = '0'; //2 = '0.00'; //3 = '#,##0'; //4 = '#,##0.00'; //5 = '$#,##0;\\-$#,##0'; //6 = '$#,##0;[Red]\\-$#,##0'; //7 = '$#,##0.00;\\-$#,##0.00'; //8 = '$#,##0.00;[Red]\\-$#,##0.00'; //9 = '0%'; //10 = '0.00%'; //11 = '0.00E+00'; //12 = '# ?/?'; //13 = '# ??/??'; //14 = 'mm-dd-yy'; //15 = 'd-mmm-yy'; //16 = 'd-mmm'; //17 = 'mmm-yy'; //18 = 'h:mm AM/PM'; //19 = 'h:mm:ss AM/PM'; //20 = 'h:mm'; //21 = 'h:mm:ss'; //22 = 'm/d/yy h:mm'; //37 = '#,##0 ;(#,##0)'; //38 = '#,##0 ;[Red](#,##0)'; //39 = '#,##0.00;(#,##0.00)'; //40 = '#,##0.00;[Red](#,##0.00)'; //44 = '_("$"* #,##0.00_);_("$"* \\(#,##0.00\\);_("$"*"-"??_);_(@_)'; //45 = 'mm:ss'; //46 = '[h]:mm:ss'; //47 = 'mmss.0'; //48 = '##0.0E+0'; //49 = '@'; //27 = '[$-404]e/m/d'; //30 = 'm/d/yy'; //36 = '[$-404]e/m/d'; //50 = '[$-404]e/m/d'; //57 = '[$-404]e/m/d'; //59 = 't0'; //60 = 't0.00'; //61 = 't#,##0'; //62 = 't#,##0.00'; //67 = 't0%'; //68 = 't0.00%'; //69 = 't# ?/?'; //70 = 't# ??/??'; #endregion //日期格式 if (workbookPart.WorkbookStylesPart != null) { var styleSheet = workbookPart.WorkbookStylesPart.Stylesheet; if (styleSheet != null && cell.StyleIndex!=null) { CellFormat cellFormat = (CellFormat)styleSheet.CellFormats.ChildElements[(int)cell.StyleIndex.Value]; int formatId = (int)cellFormat.NumberFormatId.Value; switch (formatId) { case 14: case 15: case 16: case 17: case 18: case 19: case 20: case 21: case 22: case 166: case 176: case 177: cellVal = DateTime.FromOADate(double.Parse(cellVal)).ToString("yyyy-MM-dd HH:mm:ss"); break; default: break; } } } } } } if (dt.Columns.Count - 1 >= index) { dr[index] = cellVal; } if (!string.IsNullOrEmpty(cellVal)) { number++; } } if (number != 0) { dt.Rows.Add(dr); } } /// <summary> /// 获取单位格的值 /// </summary> /// <param name="cell"></param> /// <param name="stringTablePart"></param> /// <returns></returns> private static string GetValue(Cell cell, SharedStringTable stringTable) { //因为Excel的数据存储在SharedStringTable中,须要获取数据在SharedStringTable 中的索引 string value = string.Empty; try { if (cell.ChildElements.Count == 0) return value; value = double.Parse(cell.CellValue.InnerText).ToString(); if ((cell.DataType != null)) { if ((cell.DataType == CellValues.SharedString)) { value = stringTable.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText; } } } catch (Exception) { value = string.Empty; } return value; } /// <summary> /// 判断网格是否存在列 /// </summary> /// <param name="dt">网格</param> /// <param name="columnName">列名</param> /// <returns></returns> private static bool IsContainsColumn(DataTable dt, string columnName) { if (dt == null || columnName == null) { return false; } return dt.Columns.Contains(columnName); } #endregion #region 导出 /// <summary> /// 获取Excel原始Colum名称 /// </summary> /// <param name="index"></param> /// <returns></returns> static string GetLetter(int index) { if (index < 0) { throw new Exception($"参数{nameof(index)}异常"); } List<string> chars = new List<string>(); do { if (chars.Count > 0) index--; chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString()); index = (int)((index - index % 26) / 26); } while (index > 0); return String.Join(string.Empty, chars.ToArray()); } /// <summary> /// 添加WorkSheet /// </summary> private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName) { //创建新的WorksheetPart WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); //在末尾追加一个Sheets Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); Sheet sheet = new Sheet() { Id = relationshipId, SheetId = 1, Name = sheetName }; //添加Sheet sheets.Append(sheet); workbookPart.Workbook.Save(); return newWorksheetPart; } [Obsolete] private static Dictionary<int, int> GetMaxCharacterWidth(SheetData sheetData) { //iterate over all cells getting a max char value for each column Dictionary<int, int> maxColWidth = new Dictionary<int, int>(); var row = sheetData.Elements<Row>()?.FirstOrDefault(); if (row != null) { var cells = row.Elements<Cell>().ToArray(); //defalut width for (int i = 0; i < cells.Length; i++) { maxColWidth.Add(i, 50); } } return maxColWidth; } [Obsolete] private static Columns AutoSize(SheetData sheetData) { var maxColWidth = GetMaxCharacterWidth(sheetData); Columns columns = new Columns(); //this is the width of my font - yours may be different double maxWidth = 7; foreach (var item in maxColWidth) { //width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256 double width = Math.Truncate((item.Value * maxWidth + 5) / maxWidth * 256) / 256; //pixels=Truncate(((256 * {width} + Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width}) double pixels = Math.Truncate(((256 * width + Math.Truncate(128 / maxWidth)) / 256) * maxWidth); //character width=Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100 double charWidth = Math.Truncate((pixels - 5) / maxWidth * 100 + 0.5) / 100; Column col = new Column() { BestFit = true, Min = (UInt32)(item.Key + 1), Max = (UInt32)(item.Key + 1), CustomWidth = true, Width = (DoubleValue)width }; columns.Append(col); } return columns; } /// <summary> /// 添加单元格样式 /// </summary> /// <param name="workbookPart"></param> private static void InitializeStyleSheet(WorkbookPart workbookPart) { workbookPart.AddNewPart<WorkbookStylesPart>(); workbookPart.WorkbookStylesPart.Stylesheet = new Stylesheet(); Stylesheet stylesheet = workbookPart.WorkbookStylesPart.Stylesheet; stylesheet.Fonts = new Fonts(new Font(new FontSize() { Val = 11D }, new Color() { Theme = 1U }, new FontName() { Val = "Calibri" }, new FontFamily() { Val = 2 }, new FontScheme() { Val = FontSchemeValues.Minor })) { Count = 1U }; stylesheet.Fills = new Fills(new Fill(new PatternFill() { PatternType = PatternValues.None })) { Count = 2U }; stylesheet.Borders = new Borders(new Border(new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder())) { Count = 1U }; stylesheet.CellFormats = new CellFormats(); stylesheet.CellFormats.Count = 2; CellFormat cf0 = stylesheet.CellFormats.AppendChild(new CellFormat()); cf0.NumberFormatId = 49; cf0.FontId = 0; cf0.BorderId = 0; cf0.FillId = 0; CellFormat cf = stylesheet.CellFormats.AppendChild(new CellFormat()); cf.Alignment = new Alignment(); cf.ApplyAlignment = true; cf.NumberFormatId = 49; cf.FontId = 0; cf.BorderId = 0; cf.FillId = 0; cf.Alignment.WrapText = true; workbookPart.WorkbookStylesPart.Stylesheet.Save(); } /// <summary> /// DataTable导出Excel到MemoryStream; ///Return File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "test.xlsx"); ///MVC模式下返回文件流结果,参数一为流,参数二为文件类型,参数三为文件名 /// </summary> /// <param name="dtSource">数据源</param> /// <param name="sheetName">Sheet名称</param> /// <returns></returns> public static MemoryStream ToExcelIntoMemoryStream(DataTable dtSource, string sheetName = "Sheet0") { try { var memoryStream = new MemoryStream(); using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook)) { var workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); InitializeStyleSheet(workbookPart); //创建新的SharedStringTablePart SharedStringTablePart shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>(); // 添加一个WorkSheet WorksheetPart worksheetPart = InsertWorksheet(workbookPart, sheetName); // 添加SharedStringTable shareStringPart.SharedStringTable = new SharedStringTable(); int rowIndex = 1; int cellIndex = 0; Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild<SheetData>(); //Excel表头列 SetSheetDataHeadRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex); //Excel数据列 SetSheetDataRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex); Columns columns = AutoSize(sheetData); worksheet.Append(columns); shareStringPart.SharedStringTable.Save(); worksheet.Save(); worksheetPart.Worksheet.Save(); } memoryStream.Seek(0, SeekOrigin.Begin); return memoryStream; } catch (Exception ex) { throw ex; } } /// <summary> /// 实体集合直接转为Excel 可配合XmlPropertyAttribute使用 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <param name="sheetName"></param> /// <returns></returns> public static MemoryStream ToExcelIntoMemoryStream<T>(List<T> list, string sheetName = "Sheet0") { try { var dtSource = ToDataTable(list); List<XmlPropertyAttribute> attrs = XmlPropertyAttribute.GetTypeAttributes<T>(); SetTableProperty(dtSource, attrs); var memoryStream = new MemoryStream(); using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook)) { var workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); InitializeStyleSheet(workbookPart); //创建新的SharedStringTablePart SharedStringTablePart shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>(); // 添加一个WorkSheet WorksheetPart worksheetPart = InsertWorksheet(workbookPart, sheetName); // 添加SharedStringTable shareStringPart.SharedStringTable = new SharedStringTable(); int rowIndex = 1; int cellIndex = 0; Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild<SheetData>(); //Excel表头列 SetSheetDataHeadRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex); //Excel数据列 SetSheetDataRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex); Columns columns = GetColumnsProperty(sheetData, document.WorkbookPart.SharedStringTablePart.SharedStringTable, attrs); worksheet.Append(columns); shareStringPart.SharedStringTable.Save(); worksheet.Save(); worksheetPart.Worksheet.Save(); } memoryStream.Seek(0, SeekOrigin.Begin); return memoryStream; } catch (Exception ex) { throw ex; } } /// <summary> /// DataTable固定路径导出Excel,单个Sheet生成 /// 指定路径 /// </summary> /// <param name="excelFilePath">存储路径</param> /// <param name="table">数据</param> /// <param name="sheetName">Sheet名称</param> public static void ToExcelFixUrl(string excelFilePath, DataTable dtSource, string sheetName = "Sheet0") { try { using (var document = SpreadsheetDocument.Create(excelFilePath, SpreadsheetDocumentType.Workbook)) { var workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); //创建新的SharedStringTablePart SharedStringTablePart shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>(); // 添加WorkSheet WorksheetPart worksheetPart = InsertWorksheet(workbookPart, sheetName); // 添加SharedStringTable shareStringPart.SharedStringTable = new SharedStringTable(); int rowIndex = 1; int cellIndex = 0; Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild<SheetData>(); //Excel表头列 SetSheetDataHeadRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex); //Excel数据列 SetSheetDataRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex); shareStringPart.SharedStringTable.Save(); worksheet.Save(); worksheetPart.Worksheet.Save(); } } catch (Exception ex) { throw ex; } } /// <summary> /// 将DataTable的ColumnsName转换为Excel表头Row /// </summary> /// <param name="dtSource">数据</param> /// <param name="shareStringPart"></param> /// <param name="sheetData"></param> /// <param name="rowIndex">行下标</param> /// <param name="cellIndex">单元格下标/param> private static void SetSheetDataHeadRow(DataTable dtSource, ref SharedStringTablePart shareStringPart, ref SheetData sheetData, ref int rowIndex, ref int cellIndex) { { Row row = new Row() { RowIndex = Convert.ToUInt32(rowIndex) }; sheetData.Append(row); for (int c = 0; c < dtSource.Columns.Count; c++) { string cellReference = GetLetter(c) + rowIndex; //SharedStringTable里,取数据也是根据SharedStringTable来取 shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(Convert.ToString(dtSource.Columns[c])))); Cell refCell = null; foreach (Cell cell in row.Elements<Cell>()) { if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) { refCell = cell; break; } } Cell newCell = new Cell() { CellReference = cellReference }; row.InsertBefore(newCell, refCell); newCell.CellValue = new CellValue(cellIndex++.ToString()); newCell.DataType = new EnumValue<CellValues>(CellValues.SharedString); if (c == dtSource.Columns.Count - 1) { rowIndex++; } } } } /// <summary> /// 将DataTable的ColumnsData转换为数据Row /// </summary> /// <param name="dtSource">数据</param> /// <param name="shareStringPart"></param> /// <param name="sheetData"></param> /// <param name="rowIndex">行下标</param> /// <param name="cellIndex">单元格下标/param> private static void SetSheetDataRow(DataTable dtSource, ref SharedStringTablePart shareStringPart, ref SheetData sheetData, ref int rowIndex, ref int cellIndex) { { //Excel的数据列生成 for (int r = 0; r < dtSource.Rows.Count; r++) { //CellFormats Row dataRow = new Row() { RowIndex = Convert.ToUInt32(rowIndex) }; sheetData.Append(dataRow); for (int c = 0; c < dtSource.Columns.Count; c++) { //特殊字符串处理 string cellValue= Convert.ToString(dtSource.Rows[r][c])?.Replace("\0", " "); string cellReference = GetLetter(c) + rowIndex; shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(cellValue))); Cell refCell = null; foreach (Cell cell in dataRow.Elements<Cell>()) { if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) { refCell = cell; break; } } Cell newCell = new Cell() { CellReference = cellReference, StyleIndex = 1, }; dataRow.InsertBefore(newCell, refCell); newCell.CellValue = new CellValue(cellIndex++.ToString()); newCell.DataType = new EnumValue<CellValues>(CellValues.SharedString); } dataRow.Height = 7U; rowIndex++; } } } /// <summary> /// 根据枚举重新定义table属性 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dt"></param> public static void SetTableProperty(DataTable dt, List<XmlPropertyAttribute> attrs) { foreach (var attr in attrs) { if (attr.Sort > 0) { attr.Sort = attr.Sort > (dt.Columns.Count - 1) ? (dt.Columns.Count - 1) : attr.Sort; dt.Columns[attr.FieldName].SetOrdinal(attr.Sort); } if (!string.IsNullOrEmpty(attr.Disc)) { dt.Columns[attr.FieldName].ColumnName = attr.Disc; } } } /// <summary> /// 根据枚举设置单元格宽度 /// </summary> /// <param name="sheetData"></param> /// <param name="stringTable"></param> /// <param name="attrs"></param> /// <returns></returns> private static Dictionary<int, int> GetPropertyWidth(SheetData sheetData, SharedStringTable stringTable,List<XmlPropertyAttribute> attrs) { //iterate over all cells getting a max char value for each column Dictionary<int, int> maxColWidth = new Dictionary<int, int>(); var firstRow = sheetData.Elements<Row>()?.FirstOrDefault(); if (firstRow != null) { var cells= firstRow.Elements<Cell>().ToArray(); for (int i = 0; i < cells.Length; i++) { var cell = cells[i]; var cellValue = GetValue(cell, stringTable); var cellTextLength = 50; var attr = attrs.FirstOrDefault(t => t.Disc == cellValue); if (attr != null) { cellTextLength = attr.Width > 0 ? attr.Width : cellTextLength; } maxColWidth.Add(i, cellTextLength); } } return maxColWidth; } private static Columns GetColumnsProperty(SheetData sheetData, SharedStringTable sharedStringTable, List<XmlPropertyAttribute> attrs) { var maxColWidth = GetPropertyWidth(sheetData, sharedStringTable,attrs); Columns columns = new Columns(); //this is the width of my font - yours may be different double maxWidth = 7; foreach (var item in maxColWidth) { //width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256 double width = Math.Truncate((item.Value * maxWidth + 5) / maxWidth * 256) / 256; //pixels=Truncate(((256 * {width} + Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width}) double pixels = Math.Truncate(((256 * width + Math.Truncate(128 / maxWidth)) / 256) * maxWidth); //character width=Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100 double charWidth = Math.Truncate((pixels - 5) / maxWidth * 100 + 0.5) / 100; Column col = new Column() { BestFit = true, Min = (UInt32)(item.Key + 1), Max = (UInt32)(item.Key + 1), CustomWidth = true, Width = (DoubleValue)width }; columns.Append(col); } return columns; } #endregion /// <summary> /// list模型转换为DataTable /// </summary> /// <typeparam name="T">模型</typeparam> /// <param name="list">数据集合</param> /// <returns></returns> public static DataTable ToDataTable<T>(List<T> list) { if (list == null || list.Count <= 0) { throw new Exception("参数异常"); } DataTable dt = new DataTable(); if (list.Count <= 0 || list == null) { return dt; } //添加列名 { var type = list.First().GetType(); PropertyInfo[] PropertyList = type.GetProperties(); foreach (PropertyInfo item in PropertyList) { DataColumn dc = new DataColumn(); string name = item.Name; dc.ColumnName = name;//反射类字段添加列名 dt.Columns.Add(dc); } } //添加数据列 foreach (var model in list) { var type = model.GetType(); PropertyInfo[] PropertyList = type.GetProperties(); DataRow dr = dt.NewRow(); foreach (PropertyInfo item in PropertyList) { string name = item.Name; string value = Convert.ToString(item.GetValue(model, null)); dr[name] = value; } dt.Rows.Add(dr); } return dt; } } public class XmlPropertyAttribute : Attribute { /// <summary> /// 排序 最小值1 /// </summary> public int Sort { get; set; } /// <summary> /// 单元格宽度 默认50 /// </summary> public int Width { get; set; } /// <summary> /// 别名 /// </summary> public string Disc { get; set; } public string FieldName { get; private set; } public XmlPropertyAttribute() { } public XmlPropertyAttribute(string fieldName) { FieldName = fieldName; } public static List<XmlPropertyAttribute> GetTypeAttributes<T>() { var type = typeof(T); PropertyInfo[] PropertyList = type.GetProperties(); List<XmlPropertyAttribute> attrs = new List<XmlPropertyAttribute>(); foreach (PropertyInfo item in PropertyList) { string name = item.Name; var xmlAttr = item.CustomAttributes.FirstOrDefault(t => t.AttributeType == typeof(XmlPropertyAttribute)); if (xmlAttr != null) { int sort = Convert.ToInt32(xmlAttr.NamedArguments.FirstOrDefault(t => t.MemberName == nameof(Sort)).TypedValue.Value); int width = Convert.ToInt32(xmlAttr.NamedArguments.FirstOrDefault(t => t.MemberName == nameof(Width)).TypedValue.Value); string disc = Convert.ToString(xmlAttr.NamedArguments.FirstOrDefault(t => t.MemberName == nameof(Disc)).TypedValue.Value); attrs.Add(new XmlPropertyAttribute(name) { Sort = sort, Disc = !string.IsNullOrWhiteSpace(disc) ? disc : name, Width = width, }); } } return attrs; } } }