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 MemoryStream 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;
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            workbook = null;

            byte[] bb = new byte[ms.Length];
            ms.Read(bb, 0, (int)ms.Length);
            using (FileStream file2 = new FileStream(newFileName, FileMode.Create, FileAccess.Write))
            {
                file2.Write(bb, 0, bb.Length);
            }
            return ms;
        }
    }

    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");
}

/// <summary>
/// 将dataTable导入到Excel
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="dt"></param>
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++;
    }
}

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

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