使用OpenXML将数据导入到Excel模板中

前言

之前文章中简单的介绍过将数据导入到excel中，在日常的工作的很多时候其实我们是需要将数据导入到指定的模板的。其实将数据导入到模板和将数据导入到新的Excel模板中没有多大的区别。本文主要介绍导入到模板应该注意的一些地方。

准备工作

1.Excel模板如下：

2.openxml相关类库：

3.控制台或者winform程序

注意事项

1.因为是导入到现有Excel模板，所以使用SpreadsheetDocument.Open(filePath, true)打开Excel文档；

using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
{
       //TODO:
}

2.插入数据前需要先获取待插入Excel的WorksheetPart，SheetData，以获取当前Excel中的行信息和插入数据用；

WorkbookPart workbook = document.WorkbookPart;

WorksheetPart worksheet = workbook.WorksheetParts.Last();

SheetData sheetData = worksheet.Worksheet.GetFirstChild<SheetData>();

Sheets sheets = workbook.Workbook.Sheets;

3.获取数据行的样式索引（StyleIndex）,使得新添加行的样式能够匹配已有数据行样式(下面代码仅为示意)；

//记录第二行样式
IEnumerable<Row> excelRows = worksheet.Worksheet.Descendants<Row>();

Row firstRow = excelRows.FirstOrDefault(r => r.RowIndex == 2);

UInt32 styleIndex = firstRow.RowIndex;

4.由于DataTable中数据列和表格中列不一定统一，这里就需要采取一定措施使得插入数据的正确性。本人采取的是比较笨得方式，定义一个同模板列顺序一致的枚举（枚举出所有的列名，列名是dataTable中的列名，枚举值是模板中列的单元格列索引）。根据枚举值获取待插入Cell，然后向指定Cell中插入数据；

如：DataTable的列为：ItemNo,ItemDesc,PCS,GrossWT,Height,Length,Width,Package,ChargeWT,Fee

则定义枚举以对应导出的列：

    public enum TestTemplateColumn
    {
        ItemNo,
        ItemDesc,
        PCS,
        GrossWT,
        Length,
        Width,
        Height,
        ChargeWT,
        Fee,
        Package
    }

5.创建Row时RowIndex注意不能重复，否则文档出错。另外，新创建的Row一定要添加到SheetData中 ，否则数据不会保存到Excel中；

Row row = excelRows.FirstOrDefault(r => r.RowIndex == rowIndex + 3);
                  
if (row == null)
{
   row = new Row();
   row.RowIndex = (UInt32)rowIndex + 3;//第一行，第二行为标题行
   sheetData.AppendChild(row);
}

6.如果需要也可以修改Excel的SheetName,如将”Sheet1”修改为“Test”

Sheets sheets = workbook.Workbook.Sheets;

Sheet sheet = sheets.GetFirstChild<Sheet>();

sheet.Name = "Test";

 

上述注意事项，是在使用OpenXML将DataTable数据导入到模板中， 总结出来的，不一定很全，还望补充。

 

（部分示例代码如下）

 private void InsertDataToExcel(DataTable dt, string filePath,string sheetName)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
            {
                WorkbookPart workbook = document.WorkbookPart;
                WorksheetPart worksheet = workbook.WorksheetParts.Last();
                SheetData sheetData = worksheet.Worksheet.GetFirstChild<SheetData>();

                Sheets sheets = workbook.Workbook.Sheets;
                Sheet sheet = sheets.GetFirstChild<Sheet>();

                if (string.IsNullOrEmpty(sheetName))
                {
                    sheet.Name = sheetName;
                }

                IEnumerable<Row> excelRows = worksheet.Worksheet.Descendants<Row>();

                Row firstRow = excelRows.FirstOrDefault(r => r.RowIndex == 2);
                IEnumerable<Cell> firstCells = firstRow.Descendants<Cell>();
                
                UInt32 styleIndex = 0;
                for(int rowIndex =0;rowIndex< dt.Rows.Count;rowIndex++)
                {
                    Row row = excelRows.FirstOrDefault(r => r.RowIndex == rowIndex + 3);
                  
                    if (row == null)
                    {
                        row = new Row();
                        row.RowIndex = (UInt32)rowIndex + 3;//第一行，第二行为标题行
                        sheetData.AppendChild(row);
                    }

                    IEnumerable<Cell> cells = row.Descendants<Cell>();
           
                    //具体插入代码省略了主要两步

//1. 先获取单元格

//2 .将数据插入单元格中
                }
            }
        }

        
        private Cell GetCell(RateColumn column, Row row, IEnumerable<Cell> cells)
        {
            Cell cell = null;
            string cellRef = GetCellReference((int)column+1) + row.RowIndex;
            cell = cells.FirstOrDefault(cel => cel.CellReference == cellRef);
            return cell;
        }

        private void InsertCell(RateColumn column, Row row, object value, Cell cell,uint? styleIndex)
        {
            string strValue = value == null ? string.Empty : value.ToString();
            int rowIndex = int.Parse(row.RowIndex.ToString());
            if (cell == null)
            {
                double val = 0;
                if (double.TryParse(strValue, out val))
                {
                    cell = CreateValueCell((int)column, rowIndex, val, styleIndex);
                }
                else
                {
                    cell = CreateTextCell((int)column, rowIndex, strValue, styleIndex);
                }
                row.AppendChild(cell);
            }
            else
            {
                double val = 0;
                if (double.TryParse(strValue, out val))
                {
                    cell.CellValue = new CellValue();
                    cell.CellValue.Text = strValue;
                }
                else
                {
                    InlineString inlineString = new InlineString();
                    Text t = new Text();
                    t.Text = strValue;
                    cell.DataType = CellValues.InlineString;
                    cell.InlineString = inlineString;
                    cell.InlineString.Text = t;
                }
            }
        }