c# NPOL 根据现有excel文件,在指定列写入数据
问题遇到的现象和发生背景
我需要在现有的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行中写入数据,通过复制上一行的格式再进行插入一行数据
如果编码?
/**
* 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