c# NPOL 根据现有excel文件,在指定列写入数据

Question

问题遇到的现象和发生背景

我需要在现有的excel文件第32行中写入数据,通过复制上一行的格式再进行插入一行数据。

问题相关代码，请勿粘贴截图

 string result = "";
            try
            {
                string fileDir = System.Web.HttpContext.Current.Server.MapPath("~/UI/BQ/Temp/测试报表.xlsx");
                //string fileDir = System.Web.HttpContext.Current.Server.MapPath("~/UI/BQ/Temp/212车型整车Audit评审报告.xlsx");
                FileStream file = new FileStream(fileDir, FileMode.Open, FileAccess.Read);
                // HSSFWorkbook workbook = new HSSFWorkbook(file);
                XSSFWorkbook workbook = new XSSFWorkbook(file);
                NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);

                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);

                DataTable dt;
                string sql = "";

                CustomSqlSection customSqlSection = Gateway.Default.FromCustomSql(sql);
                dt = customSqlSection.ToDataSet().Tables[0];
                //用column name 作为列名
                int icolIndex = 0;
                IRow headerRow = sheet.GetRow(0);
                foreach (DataColumn item in dt.Columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(item.ColumnName);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();
                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);
                //建立内容行
                int iRowIndex = 1;
                int iCellIndex = 0;
                int SerialNo = 0;
                foreach (DataRow Rowitem in dt.Rows)
                {
                    IRow DataRow = sheet.GetRow(iRowIndex + 31);
                    foreach (DataColumn Colitem in dt.Columns)
                    {

                        ICell cell = DataRow.GetCell(iCellIndex);
                        DataRow.GetCell(0).SetCellValue(SerialNo + 1);
                        DataRow.GetCell(1).SetCellValue(dt.Rows[0]["VIN"].ToString());
                        DataRow.GetCell(2).SetCellValue(dt.Rows[0]["OPER_DATE"].ToString());
                        DataRow.GetCell(3).SetCellValue(dt.Rows[0]["POS_STATUS"].ToString());
                        DataRow.GetCell(4).SetCellValue(dt.Rows[0]["BAD_DESC"].ToString());
                        DataRow.GetCell(9).SetCellValue(dt.Rows[0]["BAD_PHE_PIC"].ToString());
                        DataRow.GetCell(11).SetCellValue(dt.Rows[0]["LEVEL_VAL"].ToString());
                        DataRow.GetCell(12).SetCellValue(dt.Rows[0]["factory_code"].ToString());
                        DataRow.GetCell(13).SetCellValue(dt.Rows[0]["PFP_SID"].ToString());
                        DataRow.GetCell(14).SetCellValue(dt.Rows[0]["OTHER_DESC"].ToString());
                        //cell.SetCellValue(Rowitem[Colitem].ToString());
                        //cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }


                //获取地址栏ip地址
                string url = HttpContext.Current.Request.Url.Host;
                int ipPort = HttpContext.Current.Request.Url.Port;
                //写Excel
                file = new FileStream(fileDir, FileMode.OpenOrCreate);
                workbook.Write(file);
                file.Flush();
                file.Close();
                //获取指定的路径
                var startL = fileDir.IndexOf(@"\UI\");
                var str = fileDir.Substring(startL, fileDir.Length - startL);
                string Rstr = str.Substring(0, str.Length - 0);
                fileDir = url + ":" + ipPort + Rstr;

                //返回URL地址
                return fileDir;

运行结果及报错内容

我的解答思路和尝试过的方法

使用NPOL 写入数据

我想要达到的结果

我需要在现有的excel文件第32行中写入数据,通过复制上一行的格式再进行插入一行数据
如果编码?

Answer 1

/**
 * Shifts rows between startRow and endRow n number of rows.
 * If you use a negative number, it will shift rows up.
 * Code ensures that rows don't wrap around<p>
 * 
 * Additionally shifts merged regions that are completely defined in these
 * rows (ie. merged 2 cells on a row to be shifted). All merged regions that are
 * completely overlaid by shifting will be deleted.<p>
 * 
 * TODO Might want to add bounds checking here
 *
 * @param startRow               the row to start shifting
 * @param endRow                 the row to end shifting
 * @param n                      the number of rows to shift
 * @param copyRowHeight          whether to copy the row height during the shift
 * @param resetOriginalRowHeight whether to set the original row's height to the default
 */
@Override
public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
  shiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight, true);
}

如果你想要使用NPOI实现插入行的效果，你可以试试 shiftRows() 方法,它用于把指定范围的所有行移动指定的行数以实现插入行的效果。 参考一下这篇文章的示例： https://www.cnblogs.com/yanwz/p/7795618.html