using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel; // 支持 .xlsx
using NPOI.HSSF.UserModel;// 支持 .xls
using System.IO;
using System.Data;
using System;
using System.Windows.Forms;
namespace qt.Common
{
public class HdhCmsImportOutputExcel
{
public static DataTable HdhCmsImportExcelByNPOI(string filePath)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(filePath, FileMode.Open))
{
string fileExte=Path.GetExtension(filePath).Trim();
IWorkbook workbook;
if(fileExte == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else
{
workbook = new HSSFWorkbook(fs);
}
ISheet sheet = workbook.GetSheetAt(0);
// 读取首行作为列名
IRow headerRow = sheet.GetRow(0);
foreach (ICell cell in headerRow.Cells)
dt.Columns.Add(cell.ToString());
// 填充数据
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
dataRow[j] = row.GetCell(j)?.ToString();
dt.Rows.Add(dataRow);
}
}
return dt;
}
/// <summary>
/// WINFORM导出通用EXCEL模板
/// </summary>
/// <param name="strField">模板字段如：元件分类|元件名称|元件简称|元件型号|核心参数|完整参数|所属单位|所属品牌|利润率设置|元件描述|元件备注|元件附件|成本价</param>
/// <param name="strValue">模板值如：变频器|变频器|变频器|元件型号|75KW*3|75KW*3|个|台达|0.3|75KW*3|75KW*3|元件附件|23</param>
/// <param name="fileName">导出的文件名</param>
/// <returns></returns>
public static void HdhCmsGenerateExcel(string strField, string strValue, string fileName = "")
{
if (string.IsNullOrEmpty(fileName))
{
fileName = "模板" + DateTime.Now.ToString("yyyyMMddhhmmss") + "xlsx";
}
IWorkbook workbook = new XSSFWorkbook(); // 创建 .xlsx 文件（HSSFWorkbook 用于 .xls）
ISheet sheet = workbook.CreateSheet("Sheet1");
string[] dimField = strField.Split('|');
string[] dimValue = strValue.Split('|');
// 写入标题行
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < dimField.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(dimField[i]);
}
// 写入数据行
IRow dataRow = sheet.CreateRow(1);
for (int i = 0; i < dimValue.Length; i++)
{
dataRow.CreateCell(i).SetCellValue(dimValue[i]);
}
// 保存到内存流
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
byte[] bytes = ms.ToArray();
System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();
sfd.Filter = "EXCEL文件(xlsx)|*.xlsx";
sfd.Title = "导出文件";
sfd.FileName = fileName;
if (sfd.ShowDialog().Equals(DialogResult.OK))
{
File.WriteAllBytes(sfd.FileName, bytes);
}
}
}
}
}
查看更多关于使用 NPOI 库在无需安装 Office的情况下导入EXCEL文件，导出EXCEL模板的详细内容...