1  public class CreateXlsx  2     {  3         public static void Create(string filePath, DataTable dt)  4         {  5             SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook);  6             WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();  7             workbookpart.Workbook = new Workbook();  8   9             WorksheetPart worksheetpart = workbookpart.AddNewPart<WorksheetPart>(); 10             worksheetpart.Worksheet = new Worksheet(new SheetData()); 11  12             Sheets sheets = workbookpart.Workbook.AppendChild<Sheets>(new Sheets()); 13             Sheet sheet = new Sheet(); 14             sheet.Id = workbookpart.GetIdOfPart(worksheetpart); 15             sheet.SheetId = 1; 16             sheet.Name = "sheet"; 17             sheets.Append(sheet); 18  19             SheetData sheetdata = http://www.mamicode.com/worksheetpart.Worksheet.GetFirstChild(); 20  21             string columnName = ""; 22             Cell cell = null; 23             for (int i = 0; i < dt.Columns.Count; i++) 24             { 25                 columnName = ConvertToChar(i + 1); 26                 cell = InsertCellInWorksheet(columnName, 1, sheetdata); 27                 cell.CellValue = http://www.mamicode.com/new CellValue(dt.Columns[i].ColumnName); 28                 cell.DataType = new EnumValue<CellValues>(CellValues.String); 29             } 30             for (int i = 0; i < dt.Rows.Count; i++) 31             { 32                 for (int j = 0; j < dt.Columns.Count; j++) 33                 { 34                     columnName = ConvertToChar(j + 1); 35                     cell = InsertCellInWorksheet(columnName, (uint)(i + 2), sheetdata); 36                     cell.CellValue = http://www.mamicode.com/new CellValue(dt.Rows[i][j].ToString()); 37                     cell.DataType = new EnumValue<CellValues>(CellValues.Number); 38                 } 39             } 40  41             spreadsheetDocument.Close(); 42  43         } 44  45         private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, SheetData sheetData) 46         { 47             string cellReference = columnName + rowIndex; 48  49             // If the worksheet does not contain a row with the specified row index, insert one. 50             Row row; 51             var count = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count(); 52             if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) 53             { 54                 row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First(); 55             } 56             else 57             { 58                 row = new Row() { RowIndex = rowIndex }; 59                 sheetData.Append(row); 60             } 61  62             // If there is not a cell with the specified column name, insert one.   63             if (row.Elements<Cell>().Where(c => c.CellReference.Value =http://www.mamicode.com/= columnName + rowIndex).Count() > 0) 64             { 65                 return row.Elements<Cell>().Where(c => c.CellReference.Value =http://www.mamicode.com/= cellReference).First(); 66             } 67             else 68             { 69                 // Cells must be in sequential order according to CellReference. Determine where to insert the new cell. 70                 Cell refCell = null; 71                 foreach (Cell cell in row.Elements<Cell>()) 72                 { 73                     if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) 74                     { 75                         refCell = cell; 76                         break; 77                     } 78                 } 79  80                 Cell newCell = new Cell() { CellReference = cellReference }; 81                 row.InsertBefore(newCell, refCell); 82                 return newCell; 83             } 84         } 85  86         private static string ConvertToChar(int value) 87         { 88             string rtn = string.Empty; 89             List<int> iList = new List<int>(); 90  91             //To single Int 92             while (value / 26 != 0 || value % 26 != 0) 93             { 94                 iList.Add(value % 26); 95                 value /= 26; 96             } 97  98             //Change 0 To 26 99             for (int j = 0; j < iList.Count - 1; j++)100             {101                 if (iList[j] == 0)102                 {103                     iList[j + 1] -= 1;104                     iList[j] = 26;105                 }106             }107             //Remove 0 at last108             if (iList[iList.Count - 1] == 0)109             {110                 iList.Remove(iList[iList.Count - 1]);111             }112 113             //To String114             for (int j = iList.Count - 1; j >= 0; j--)115             {116                 char c = (char)(iList[j] + 64);117                 rtn += c.ToString();118             }119 120             return rtn;121         }122     }