首页 > 代码库 > 初探：使用NPOI导出Excel（已有Excel模板）

初探：使用NPOI导出Excel（已有Excel模板）

2024-09-17 12:21:18   219人阅读

/// <summary>
/// 应用开源NPOI，导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnNPOIExport_Click(object sender, EventArgs e)
{
if (!File.Exists(sExePath + "NPOI.dll"))
{
MessageBox.Show("导出所需动态链接库NPOI.dll不存在，不支持导出。", "提示");
return;
}
if (!File.Exists(sExePath + sExcelName))
{
MessageBox.Show("DataUsageReportingFileSample.xls模板文件不存在，请确认与.EXE同路径下包含此文件。", "提示");
return;
}
 
// 填充数据
using (SaveFileDialog saveExcel = new SaveFileDialog())
{
saveExcel.Filter = "Excel文件 (*.xls)|*.xls";
string sNewFileName = string.Empty;
if (saveExcel.ShowDialog() == DialogResult.OK)
{
sNewFileName = saveExcel.FileName;
// 文件已被打开，则提示关闭
if (CFileHasOpened.FileHasOpen(sNewFileName))
{
MessageBox.Show("文件已被打开，请关闭后再重试保存。", "提示");
return;
}
// 复制模板，以后的操作都在复制的文件上进行
File.Copy(sExePath + sExcelName, sNewFileName, true);
 
InitializeWorkbook(sNewFileName);
if (null == hssfworkbook)
{ return; }
ISheet modelSheet = hssfworkbook.GetSheet("Market Data Usage");
// 单元格格式
ICellStyle CenterStyle = hssfworkbook.CreateCellStyle();
CenterStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
 
if (null == modelSheet)
{ return; }
if (null == QueriedPermissions)
{ return; }
 
for (Int32 rowIndex = 0; rowIndex < QueriedPermissions.Count; rowIndex++)
{
IRow tmpRow = modelSheet.GetRow(rowIndex + 5);
if (null == tmpRow)
{ continue; }
for (Int32 colIndex = 0; colIndex < QueriedPermissions[rowIndex].Count; colIndex++)
{
ICell tmpCell = tmpRow.GetCell(colIndex);
if (null == tmpCell)
{ continue; }
if (colIndex < 7)
{
tmpCell.SetCellValue(QueriedPermissions[rowIndex][colIndex].ToString().Trim());
}
else
{
tmpCell.SetCellValue(Convert.ToInt32(QueriedPermissions[rowIndex][colIndex].ToString().Trim()));
//tmpCell.CellStyle = CenterStyle;
}
}
}
 
//Force excel to recalculate all the formula while open
modelSheet.ForceFormulaRecalculation = true;
 
WriteToExcelWithNPOI(sNewFileName);
}
}
}
 
private static HSSFWorkbook hssfworkbook;
/// <summary>
/// 初始化工作簿
/// </summary>
private void InitializeWorkbook(string sNewFileName)
{
FileStream file = new FileStream(sNewFileName, FileMode.Open, FileAccess.Read);
if (null == file)
{ return; }
hssfworkbook = new HSSFWorkbook(file);
if (null == hssfworkbook)
{ return; }
//create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "test";
hssfworkbook.DocumentSummaryInformation = dsi;
 
//create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "test";
hssfworkbook.SummaryInformation = si;
}
 
/// <summary>
/// 把工作簿写到本地文件
/// </summary>
private void WriteToExcelWithNPOI(string sNewFileName)
{
FileStream file = new FileStream(sNewFileName, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}

初探：使用NPOI导出Excel（已有Excel模板）


联系
我们