npoi导出excel

只要sql就可以导出excel。

 

 

基类：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;

namespace Web.Common
{
public class ExcelHelper
{
//Datatable导出Excel
public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName,string sheetName)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.CreateSheet("Sheet1");
workbook.SetSheetName(0, sheetName);
CellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = CellBorderType.THIN;
HeadercellStyle.BorderLeft = CellBorderType.THIN;
HeadercellStyle.BorderRight = CellBorderType.THIN;
HeadercellStyle.BorderTop = CellBorderType.THIN;
HeadercellStyle.Alignment = HorizontalAlignment.CENTER;
//字体
NPOI.SS.UserModel.Font headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.BOLD;
HeadercellStyle.SetFont(headerfont);

//用column name 作为列名
int icolIndex = 0;
Row headerRow = sheet.CreateRow(0);
foreach (DataColumn item in dt.Columns)
{
Cell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}

CellStyle cellStyle = workbook.CreateCellStyle();

//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = CellBorderType.THIN;
cellStyle.BorderLeft = CellBorderType.THIN;
cellStyle.BorderRight = CellBorderType.THIN;
cellStyle.BorderTop = CellBorderType.THIN;

Font cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.NORMAL;
cellStyle.SetFont(cellfont);

//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
Row DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{

Cell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}

//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}

//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
}
catch (Exception ex)
{

}
}

}}

 

 

 

实现：

public string ExportExcel()
{
try
{
// excel保存路径
string saveFile = Server.MapPath("~/") + "Files\\Excel\\样例.xls";

string sql = "";

DataTable table = Helper.GetDataTable(sql); // 得到的数据信息表
ExcelHelper.GridToExcelByNPOI(table, saveFile, "批而未供"); // 导出excel方法
FileInfo DownloadFile = new FileInfo(saveFile); // 获取文件路径
string filePath = "Files/Excel/" + DownloadFile.Name; // 传到前台的信息
return filePath;

}
catch (Exception)
{
return "导出失败";
}
}

转载于:https://www.cnblogs.com/lovejingjing/p/10815033.html