使用NPOI做excel文件导出 :
1.下载npoi引用文件
下载地址1:https://archive.codeplex.com/?p=npoi
下载地址2:链接:https://pan.baidu.com/s/1Zxq4NOCnaekdTei74d6UiQ
提取码:zrwn
下载地址3:https://download.csdn.net/download/lmqzhzy/11175088
2.代码
public class ExcelToFile
{
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
public static string getFilePath()
{
string desktop = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
Console.WriteLine(desktop);//桌面路径
// Console.ReadLine();
string fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称
string urlPath = desktop + "/" + fileName; // 文件下载的URL地址
Console.WriteLine(urlPath);
return urlPath;
}
public static string exportExcel(T_Operation[] otn)
{
IWorkbook workbook;
string file = getFilePath();
string fileExt = Path.GetExtension(file).ToLower();
if (string.IsNullOrEmpty(fileExt) == false)
{
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook();
}
else
{
workbook = null;
}
if (workbook == null)
{
return "没有工作簿";
}
}
else
{
return "导出路径出错";
}
//ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
ISheet sheet = workbook.CreateSheet("sheet1");
//表头
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("ID");
row.CreateCell(1).SetCellValue("名称");
row.CreateCell(2).SetCellValue("名称");
row.CreateCell(3).SetCellValue("名称");
row.CreateCell(4).SetCellValue("名称");
row.CreateCell(5).SetCellValue("名称");
row.CreateCell(6).SetCellValue("名称");
row.CreateCell(7).SetCellValue("名称");
row.CreateCell(8).SetCellValue("名称");
//数据
for (int i = 0, count = otn.Count(); i < count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for(int j = 0; j < 9; j++)
{
ICell cell = row1.CreateCell(j);
}
T_Operation operation = otn[i];
row1.GetCell(0).SetCellValue(operation.ID);
row1.GetCell(1).SetCellValue(operation.字段1.ToString());
row1.GetCell(2).SetCellValue(operation.字段2.ToString());
row1.GetCell(3).SetCellValue(operation.字段3.ToString());
row1.GetCell(4).SetCellValue(operation.字段4.ToString());
row1.GetCell(5).SetCellValue(operation.字段5.ToString());
row1.GetCell(6).SetCellValue(operation.字段6.ToString());
row1.GetCell(7).SetCellValue(operation.字段7.ToString());
row1.GetCell(8).SetCellValue(operation.字段8.ToString());
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
return "ok";
}
}