因最近有需求场景，实现对文件的读写操作，又不单独生成新的文件，对于源文件的修改，做了一个简单实现，如下↓
// 要操作的excel文件路径
string fileName = Server.MapPath("~/Content/test.xlsx");
if (!System.IO.File.Exists(fileName)) return Content("文件不存在！");
IWorkbook workbook; //把文件内容导入到工作薄当中，然后关闭文件
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite); workbook = WorkbookFactory.Create(fs);
fs.Close();//编辑工作薄当中内容
ISheet sheet = workbook.GetSheetAt(); //workbook.GetSheetAt(0).GetRow(0).Cells[0].SetCellValue("TestCell");
for (int i = ; i <= sheet.LastRowNum; i++)
{
foreach (ICell cell in sheet.GetRow(i).Cells)
{
cell.SetCellType(cell.CellType);
if (cell.CellType == CellType.Numeric)
{
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
cell.SetCellValue(cell.DateCellValue.ToString());
}
else//其他数字类型
{
cell.SetCellValue(cell.NumericCellValue.ToString());
}
}
else
{
cell.SetCellValue(cell.StringCellValue.ToString());
}
}
if (i != )
{
ICell firstCell = sheet.GetRow(i).Cells[];
firstCell.SetCellType(firstCell.CellType);
var cellValue = firstCell.StringCellValue.ToDecimal().ToString();
firstCell.SetCellValue(cellValue);
}
} //把编辑过后的工作薄重新保存为excel文件
FileStream fs2 = System.IO.File.Create(fileName);
workbook.Write(fs2);
fs2.Close();
return Content("操作成功！！" + DateTime.Now);
读取文件内容更新文件状态
// 要操作的excel文件路径
string fileName = Server.MapPath("~/Content/test.xlsx");
if (!System.IO.File.Exists(fileName)) return Content("文件不存在,请检查路径及文件名称是否正确！");
//IWorkbook workbook;
//把文件内容导入到工作薄当中，然后关闭文件
//FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
//workbook = WorkbookFactory.Create(fs);
//fs.Close();//编辑工作薄当中内容
//如果是xls，使用HSSFWorkbook；如果是xlsx，使用XSSFWorkbook
XSSFWorkbook xssFWorkbook;
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite))
{
xssFWorkbook = new XSSFWorkbook(file);
} ISheet sheet = xssFWorkbook.GetSheetAt();
var userList = GetUserLists(sheet);
var userNames = userList.Select(u =>
{
return u.UserName.Replace(u.UserName, $"'{u.UserName}'");
});
var companyNames = userList.Select(u =>
{
return u.CompanyName.Replace(u.CompanyName, $"'{u.CompanyName}'");
}); var userIdCards = userList.Select((u, i) =>
{
return $" SELECT '{u.UserName}' AS UserName,'{u.CompanyName}' AS CompanyName,'{u.LegalPersonIdNumber}' AS LegalPersonIdNumber ";
});
var dataList = AsyncHelper.RunSync(() => _userIdFileAppService.RunSearchAndUpdateData(
new UserIdentityCardInput
{
UserNameString = string.Join(",", userNames),
CompanyNameString = string.Join(",", companyNames),
UserIdCardString = string.Format(@"SELECT * INTO #tbUserIdCardTable FROM ( {0} ) a;", string.Join(" UNION ", userIdCards))
}
));
for (int i = ; i <= sheet.LastRowNum; i++)
{
if (i != )
{
var userName = sheet.GetRow(i).Cells[].ToString();
var companyName = sheet.GetRow(i).Cells[].ToString();
//添加列
ICell lastCell = sheet.GetRow(i).CreateCell();
lastCell.SetCellType(lastCell.CellType);
ICellStyle newStyle = xssFWorkbook.CreateCellStyle();
//XSSFCellStyle styleTemp = ((XSSFCellStyle)newStyle);
//HSSFPalette palette = xssFWorkbook.GetCustomPalette(); if (dataList.Find(a => a.UserName == userName && a.CompanyName == companyName) != null)
{
lastCell.SetCellValue("已更新");
//Color c = Color.FromArgb(98, 185, 106);
//palette.SetColorAtIndex((short)9, c.R, c.G, c.B);
//var color = palette.FindColor(c.R, c.G, c.B);
//newStyle.FillPattern = FillPattern.SolidForeground;
//newStyle.FillForegroundColor = color.Indexed; XSSFColor color = new XSSFColor();
color.SetRgb(new byte[] { , , });
newStyle.FillPattern = FillPattern.SolidForeground;
newStyle.FillForegroundColor = ;
}
else
{
lastCell.SetCellValue("未更新");
//创建字体
XSSFFont ffont = (XSSFFont)xssFWorkbook.CreateFont();
XSSFColor color = new XSSFColor();
color.SetRgb(new byte[] { , , });
//给字体设置颜色
ffont.Color = color.Indexed;
//给样式添加字体
newStyle.SetFont(ffont); newStyle.FillPattern = FillPattern.SolidForeground;
newStyle.FillForegroundColor = ; }
lastCell.CellStyle = newStyle;
}
} //把编辑过后的工作薄重新保存为excel文件
FileStream fs2 = System.IO.File.Create(fileName);
xssFWorkbook.Write(fs2);
fs2.Close();
return Content("操作成功！！" + DateTime.Now);