NPOI将数据导入指定Excel模板中

帮助类

public class ExcelHelper
{
    private string templatePath;
    private string newFileName;
    private string templdateName;

    private string sheetName;

    public string SheetName
    {
        get { return sheetName; }
        set { sheetName = value; }
    }

    public ExcelHelper(string templdateName, string newFileName)
    {
        this.sheetName = "sheet1";
        templatePath = AppDomain.CurrentDomain.BaseDirectory + "Template/";
        this.templdateName = string.Format("{0}{1}", templatePath, templdateName);
        this.newFileName = newFileName;
    }
    public void SetDataToExcel(Action<ISheet> actionMethod)
    {
        //Load template file
        FileStream file = new FileStream(templdateName, FileMode.Open, FileAccess.Read);
        string extension = System.IO.Path.GetExtension(templdateName); //获取扩展名
        IWorkbook workbook = null;
        if (extension.Equals(".xls"))
        {
            workbook = new HSSFWorkbook(file);
        }
        if (extension.Equals(".xlsx"))
        {
            workbook = new XSSFWorkbook(file);
        }
        ISheet sheet = workbook.GetSheet(SheetName);

        if (actionMethod != null) actionMethod(sheet);

        sheet.ForceFormulaRecalculation = true;
        
        // 创建一个 FileStream 对象，用于将工作簿写入文件
        using (FileStream file1 = new FileStream(outputFilePath, FileMode.Create, FileAccess.Write))
         {
            // 将工作簿写入 FileStream
              workbook.Write(file1);
          }
    }

    public void SetDataTableValue(ISheet sheet, int rowIndex, int columnIndex, DataTable dt)
    {
        IRow row = null;
        ICell cell = null;
        foreach (DataRow dataRow in dt.Rows)
        {
            row = sheet.GetRow(rowIndex);
            columnIndex = 0;
            foreach (DataColumn column in dt.Columns)
            {
                cell = row.GetCell(columnIndex);
                string drValue = dataRow[column].ToString();

                switch (column.DataType.ToString())
                {
                    case "System.String":
                        cell.SetCellValue(drValue);
                        break;
                    case "System.DateTime":
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        cell.SetCellValue(dateV);

                        break;
                    case "System.Boolean":
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        cell.SetCellValue(boolV);
                        break;
                    case "System.Int16":
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        cell.SetCellValue(intV);
                        break;
                    case "System.Decimal":
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        cell.SetCellValue(doubV);
                        break;
                    case "System.DBNull":
                        cell.SetCellValue("");
                        break;
                    default:
                        cell.SetCellValue("");
                        break;
                }
                columnIndex++;
            }

            rowIndex++;
        }
    }
}

调用帮助类导出到指定Excel模板中

private void button1_Click(object sender, EventArgs e)
{
    ExcelHelper printManager = new ExcelHelper("test.xlsx", "newTest.xlsx");
    printManager.SetDataToExcel(SetPurchaseOrder);
}

private void SetPurchaseOrder(ISheet sheet)
{
    IRow row = null;
    ICell cell = null;

    row = sheet.GetRow(2);//获取第2行 行号是从0开始
    cell = row.GetCell(1);//获取第1列 列号是从0开始 注意列不能为空，可以在模板cell填个空格
    cell.SetCellValue("C0000001"); //设置第2行第1列的值为 C0000001
    cell = row.GetCell(7); //获取第7列 列号是从0开始
    cell.SetCellValue("2020-06-22");//设置第2行第7列的值为 2020-06-22


    DataTable itemDT = PrepareItemDTForTest();
    SetDataTableValue(sheet, 7, 0, itemDT);

    row = sheet.GetRow(14);
    cell = row.GetCell(0);
    cell.SetCellValue("NOKIA");
    cell = row.GetCell(6);
    cell.SetCellValue("CMCC");
}

转载请保留http://www.luofenming.com/show.aspx?id=ART2020062200001

源码下载: https://pan.baidu.com/s/1xBWg8dxKcCt_BABnLUQiTw 提取码: c8v8