npoi导出excel

系统管理员 2021-12-09 05:45 427阅读 0赞

只要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