asp.net 利用NPOI導(dǎo)出Excel通用類(lèi)的方法
更新時(shí)間：2017年06月23日 11:01:49 作者：暖楓無(wú)敵
本篇文章主要介紹了asp.net 利用NPOI導(dǎo)出Excel通用類(lèi)的方法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下
解決中文文件名保存Excel亂碼問(wèn)題，主要是判斷火狐或者IE瀏覽器，然后做對(duì)應(yīng)的判斷處理，核心代碼如下：
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
//設(shè)置下載的Excel文件名\
if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1)
{
//火狐瀏覽器
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?="));
}
else
{
//IE等瀏覽器
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)));
}
廢話(huà)不多說(shuō)，直接上類(lèi)庫(kù)代碼，ATNPOIHelper.cs：
using System;
using System.Linq;
using System.Web;
using System.IO;
using NPOI;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.Collections.Generic;
using System.Text;
namespace AT.Utility.DotNetFile
{
/*
導(dǎo)出Excel包含的功能：
1.多表頭導(dǎo)出最多支持到三行，表頭格式說(shuō)明
相鄰父列頭之間用'#'分隔,父列頭與子列頭用空格(' ‘)分隔,相鄰子列頭用逗號(hào)分隔(‘,')
兩行：序號(hào)#分公司#組別#本日成功簽約單數(shù) 預(yù)警,續(xù)約,流失,合計(jì)#累計(jì)成功簽約單數(shù) 預(yù)警,續(xù)約,流失,合計(jì)#任務(wù)數(shù)#完成比例#排名
三行：等級(jí)#級(jí)別#上期結(jié)存 件數(shù),重量,比例#本期調(diào)入 收購(gòu)調(diào)入 件數(shù),重量,比例#本期發(fā)出 車(chē)間投料 件數(shù),重量,比例#本期發(fā)出 產(chǎn)品外銷(xiāo)百分比 件數(shù),重量,比例#平均值
三行時(shí)請(qǐng)注意：列頭要重復(fù)
2.添加表頭標(biāo)題功能
3.添加序號(hào)功能
4.根據(jù)數(shù)據(jù)設(shè)置列寬
缺陷：
數(shù)據(jù)內(nèi)容不能合并列合并行
改進(jìn)思路：
添加一屬性：設(shè)置要合并的列，為了實(shí)現(xiàn)多列合并可以這樣設(shè)置{“列1,列2”,”列4”}
*/
/// <summary>
/// 利用NPOI實(shí)現(xiàn)導(dǎo)出Excel
/// </summary>
public class ATNPOIHelper
{
#region 初始化
/// <summary>
/// 聲明 HSSFWorkbook 對(duì)象
/// </summary>
private static HSSFWorkbook _workbook;
/// <summary>
/// 聲明 HSSFSheet 對(duì)象
/// </summary>
private static HSSFSheet _sheet;
#endregion
#region Excel導(dǎo)出
/// <summary>
/// Excel導(dǎo)出
/// </summary>
/// <param name="fileName">文件名稱(chēng) 如果為空或NULL，則默認(rèn)“新建Excel.xls”</param>
/// <param name="list"></param>
/// <param name="ColMergeNum">合計(jì)：末行合計(jì)時(shí)，合并的列數(shù)</param>
/// <param name="method">導(dǎo)出方式 1：WEB導(dǎo)出（默認(rèn)）2：按文件路徑導(dǎo)出</param>
/// <param name="filePath">文件路徑 如果WEB導(dǎo)出，則可以為空；如果按文件路徑導(dǎo)出，則默認(rèn)桌面路徑</param>
public static void Export(string fileName, IList<NPOIModel> list, int ColMergeNum, int method = 1, string filePath = null)
{
// 文件名稱(chēng)
if (!string.IsNullOrEmpty(fileName))
{
if (fileName.IndexOf('.') == -1)
{
fileName += ".xls";
}
else
{
fileName = fileName.Substring(1, fileName.IndexOf('.')) + ".xls";
}
}
else
{
fileName = "新建Excel.xls";
}
// 文件路徑
if (2 == method && string.IsNullOrEmpty(filePath))
{
filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
}
// 調(diào)用導(dǎo)出處理程序
Export(list, ColMergeNum);
// WEB導(dǎo)出
if (1 == method)
{
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
//設(shè)置下載的Excel文件名\
if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1)
{
//火狐瀏覽器
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?="));
}
else
{
//IE等瀏覽器
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)));
}
using (MemoryStream ms = new MemoryStream())
{
//將工作簿的內(nèi)容放到內(nèi)存流中
_workbook.Write(ms);
//將內(nèi)存流轉(zhuǎn)換成字節(jié)數(shù)組發(fā)送到客戶(hù)端
System.Web.HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
System.Web.HttpContext.Current.Response.End();
_sheet = null;
_workbook = null;
}
}
else if (2 == method)
{
using (FileStream fs = File.Open(filePath, FileMode.Append))
{
_workbook.Write(fs);
_sheet = null;
_workbook = null;
}
}
}
/// <summary>
/// 導(dǎo)出方法實(shí)現(xiàn)
/// </summary>
/// <param name="list"></param>
private static void Export(IList<NPOIModel> list, int ColMergeNum)
{
#region 變量聲明
// 初始化
_workbook = new HSSFWorkbook();
// 聲明 Row 對(duì)象
IRow _row;
// 聲明 Cell 對(duì)象
ICell _cell;
// 總列數(shù)
int cols = 0;
// 總行數(shù)
int rows = 0;
// 行數(shù)計(jì)數(shù)器
int rowIndex = 0;
// 單元格值
string drValue = null;
#endregion
foreach (NPOIModel model in list)
{
// 工作薄命名
if (model.sheetName != null)
_sheet = (HSSFSheet)_workbook.CreateSheet(model.sheetName);
else
_sheet = (HSSFSheet)_workbook.CreateSheet();
// 獲取數(shù)據(jù)源
DataTable dt = model.dataSource;
// 初始化
rowIndex = 0;
// 獲取總行數(shù)
rows = GetRowCount(model.headerName);
// 獲取總列數(shù)
cols = GetColCount(model.headerName);
//合計(jì)：合并表格末行N列,rows為表頭行數(shù)，dt.Rows.Count為數(shù)據(jù)行數(shù)
if (ColMergeNum > 1)
{
CellRangeAddress region_Merge = new CellRangeAddress(rows + dt.Rows.Count, rows + dt.Rows.Count, 0, ColMergeNum - 1);
_sheet.AddMergedRegion(region_Merge);
}
ICellStyle myBodyStyle = bodyStyle;
ICellStyle myTitleStyle = titleStyle;
ICellStyle myDateStyle = dateStyle;
ICellStyle myBodyRightStyle = bodyRightStyle;
// 循環(huán)行數(shù)
foreach (DataRow row in dt.Rows)
{
#region 新建表，填充表頭，填充列頭，樣式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
_sheet = (HSSFSheet)_workbook.CreateSheet();
// 構(gòu)建行
for (int i = 0; i < rows + model.isTitle; i++)
{
_row = _sheet.GetRow(i);
// 創(chuàng)建行
if (_row == null)
_row = _sheet.CreateRow(i);
for (int j = 0; j < cols; j++)
_row.CreateCell(j).CellStyle = myBodyStyle;
}
// 如果存在表標(biāo)題
if (model.isTitle > 0)
{
// 獲取行
_row = _sheet.GetRow(0);
// 合并單元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, (cols - 1));
_sheet.AddMergedRegion(region);
// 填充值
_row.CreateCell(0).SetCellValue(model.tableTitle);
// 設(shè)置樣式
_row.GetCell(0).CellStyle = myTitleStyle;
// 設(shè)置行高
_row.HeightInPoints = 20;
}
// 取得上一個(gè)實(shí)體
NPOIHeader lastRow = null;
IList<NPOIHeader> hList = GetHeaders(model.headerName, rows, model.isTitle);
// 創(chuàng)建表頭
foreach (NPOIHeader m in hList)
{
var data = hList.Where(c => c.firstRow == m.firstRow && c.lastCol == m.firstCol - 1);
if (data.Count() > 0)
{
lastRow = data.First();
if (m.headerName == lastRow.headerName)
m.firstCol = lastRow.firstCol;
}
// 獲取行
_row = _sheet.GetRow(m.firstRow);
// 合并單元格
CellRangeAddress region = new CellRangeAddress(m.firstRow, m.lastRow, m.firstCol, m.lastCol);
_sheet.AddMergedRegion(region);
// 填充值
_row.CreateCell(m.firstCol).SetCellValue(m.headerName);
}
// 填充表頭樣式
for (int i = 0; i < rows + model.isTitle; i++)
{
_row = _sheet.GetRow(i);
for (int j = 0; j < cols; j++)
{
_row.GetCell(j).CellStyle = myBodyStyle;
//設(shè)置列寬
_sheet.SetColumnWidth(j, (model.colWidths[j] + 1) * 450);
}
}
rowIndex = (rows + model.isTitle);
}
#endregion
#region 填充內(nèi)容
// 構(gòu)建列
_row = _sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
// 添加序號(hào)列
if (1 == model.isOrderby && column.Ordinal == 0)
{
_cell = _row.CreateCell(0);
_cell.SetCellValue(rowIndex - rows);
_cell.CellStyle = myBodyStyle;
}
// 創(chuàng)建列
_cell = _row.CreateCell(column.Ordinal + model.isOrderby);
// 獲取值
drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String"://字符串類(lèi)型
_cell.SetCellValue(drValue);
_cell.CellStyle = myBodyStyle;
break;
case "System.DateTime"://日期類(lèi)型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
_cell.SetCellValue(dateV);
_cell.CellStyle = myDateStyle;//格式化顯示
break;
case "System.Boolean"://布爾型
bool boolV = false;
bool.TryParse(drValue, out boolV);
_cell.SetCellValue(boolV);
_cell.CellStyle = myBodyStyle;
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
_cell.SetCellValue(intV);
_cell.CellStyle = myBodyRightStyle;
break;
case "System.Decimal"://浮點(diǎn)型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
_cell.SetCellValue(doubV.ToString("f2"));
_cell.CellStyle = myBodyRightStyle;
break;
case "System.DBNull"://空值處理
_cell.SetCellValue("");
break;
default:
_cell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
}
}
#region 輔助方法
/// <summary>
/// 表頭解析
/// </summary>
/// <remarks>
/// </remarks>
/// <param name="header">表頭</param>
/// <param name="rows">總行數(shù)</param>
/// <param name="addRows">外加行</param>
/// <param name="addCols">外加列</param>
/// <returns></returns>
private static IList<NPOIHeader> GetHeaders(string header, int rows, int addRows)
{
// 臨時(shí)表頭數(shù)組
string[] tempHeader;
string[] tempHeader2;
// 所跨列數(shù)
int colSpan = 0;
// 所跨行數(shù)
int rowSpan = 0;
// 單元格對(duì)象
NPOIHeader model = null;
// 行數(shù)計(jì)數(shù)器
int rowIndex = 0;
// 列數(shù)計(jì)數(shù)器
int colIndex = 0;
//
IList<NPOIHeader> list = new List<NPOIHeader>();
// 初步解析
string[] headers = header.Split(new string[] { "#" }, StringSplitOptions.RemoveEmptyEntries);
// 表頭遍歷
for (int i = 0; i < headers.Length; i++)
{
// 行數(shù)計(jì)數(shù)器清零
rowIndex = 0;
// 列數(shù)計(jì)數(shù)器清零
colIndex = 0;
// 獲取所跨行數(shù)
rowSpan = GetRowSpan(headers[i], rows);
// 獲取所跨列數(shù)
colSpan = GetColSpan(headers[i]);
// 如果所跨行數(shù)與總行數(shù)相等，則不考慮是否合并單元格問(wèn)題
if (rows == rowSpan)
{
colIndex = GetMaxCol(list);
model = new NPOIHeader(headers[i],
addRows,
(rowSpan - 1 + addRows),
colIndex,
(colSpan - 1 + colIndex),
addRows);
list.Add(model);
rowIndex += (rowSpan - 1) + addRows;
}
else
{
// 列索引
colIndex = GetMaxCol(list);
// 如果所跨行數(shù)不相等，則考慮是否包含多行
tempHeader = headers[i].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
for (int j = 0; j < tempHeader.Length; j++)
{
// 如果總行數(shù)=數(shù)組長(zhǎng)度
if (1 == GetColSpan(tempHeader[j]))
{
if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
{
model = new NPOIHeader(tempHeader[j],
(j + addRows),
(j + addRows) + (rows - tempHeader.Length),
colIndex,
(colIndex + colSpan - 1),
addRows);
list.Add(model);
}
else
{
model = new NPOIHeader(tempHeader[j],
(j + addRows),
(j + addRows),
colIndex,
(colIndex + colSpan - 1),
addRows);
list.Add(model);
}
}
else
{
// 如果所跨列數(shù)不相等，則考慮是否包含多列
tempHeader2 = tempHeader[j].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
for (int m = 0; m < tempHeader2.Length; m++)
{
// 列索引
colIndex = GetMaxCol(list) - colSpan + m;
if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
{
model = new NPOIHeader(tempHeader2[m],
(j + addRows),
(j + addRows) + (rows - tempHeader.Length),
colIndex,
colIndex,
addRows);
list.Add(model);
}
else
{
model = new NPOIHeader(tempHeader2[m],
(j + addRows),
(j + addRows),
colIndex,
colIndex,
addRows);
list.Add(model);
}
}
}
rowIndex += j + addRows;
}
}
}
return list;
}
/// <summary>
/// 獲取最大列
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
private static int GetMaxCol(IList<NPOIHeader> list)
{
int maxCol = 0;
if (list.Count > 0)
{
foreach (NPOIHeader model in list)
{
if (maxCol < model.lastCol)
maxCol = model.lastCol;
}
maxCol += 1;
}
return maxCol;
}
/// <summary>
/// 獲取表頭行數(shù)
/// </summary>
/// <param name="newHeaders">表頭文字</param>
/// <returns></returns>
private static int GetRowCount(string newHeaders)
{
string[] ColumnNames = newHeaders.Split(new char[] { '@' });
int Count = 0;
if (ColumnNames.Length <= 1)
ColumnNames = newHeaders.Split(new char[] { '#' });
foreach (string name in ColumnNames)
{
int TempCount = name.Split(new char[] { ' ' }).Length;
if (TempCount > Count)
Count = TempCount;
}
return Count;
}
/// <summary>
/// 獲取表頭列數(shù)
/// </summary>
/// <param name="newHeaders">表頭文字</param>
/// <returns></returns>
private static int GetColCount(string newHeaders)
{
string[] ColumnNames = newHeaders.Split(new char[] { '@' });
int Count = 0;
if (ColumnNames.Length <= 1)
ColumnNames = newHeaders.Split(new char[] { '#' });
Count = ColumnNames.Length;
foreach (string name in ColumnNames)
{
int TempCount = name.Split(new char[] { ',' }).Length;
if (TempCount > 1)
Count += TempCount - 1;
}
return Count;
}
/// <summary>
/// 列頭跨列數(shù)
/// </summary>
/// <remarks>
/// </remarks>
/// <param name="newHeaders">表頭文字</param>
/// <returns></returns>
private static int GetColSpan(string newHeaders)
{
return newHeaders.Split(',').Count();
}
/// <summary>
/// 列頭跨行數(shù)
/// </summary>
/// <remarks>
/// </remarks>
/// <param name="newHeaders">列頭文本</param>
/// <param name="rows">表頭總行數(shù)</param>
/// <returns></returns>
private static int GetRowSpan(string newHeaders, int rows)
{
int Count = newHeaders.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).Length;
// 如果總行數(shù)與當(dāng)前表頭所擁有行數(shù)相等
if (rows == Count)
Count = 1;
else if (Count < rows)
Count = 1 + (rows - Count);
else
throw new Exception("表頭格式不正確！");
return Count;
}
#endregion
#region 單元格樣式
/// <summary>
/// 數(shù)據(jù)單元格樣式
/// </summary>
private static ICellStyle bodyStyle
{
get
{
ICellStyle style = _workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER; //居中
style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
style.WrapText = true;//自動(dòng)換行
// 邊框
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
style.BorderTop = BorderStyle.THIN;
// 字體
//IFont font = _workbook.CreateFont();
//font.FontHeightInPoints = 10;
//font.FontName = "宋體";
//style.SetFont(font);
return style;
}
}
/// <summary>
/// 數(shù)據(jù)單元格樣式
/// </summary>
private static ICellStyle bodyRightStyle
{
get
{
ICellStyle style = _workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.RIGHT; //居中
style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
style.WrapText = true;//自動(dòng)換行
// 邊框
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
style.BorderTop = BorderStyle.THIN;
// 字體
//IFont font = _workbook.CreateFont();
//font.FontHeightInPoints = 10;
//font.FontName = "宋體";
//style.SetFont(font);
return style;
}
}
/// <summary>
/// 標(biāo)題單元格樣式
/// </summary>
private static ICellStyle titleStyle
{
get
{
ICellStyle style = _workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER; //居中
style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
style.WrapText = true;//自動(dòng)換行
//IFont font = _workbook.CreateFont();
//font.FontHeightInPoints = 14;
//font.FontName = "宋體";
//font.Boldweight = (short)FontBoldWeight.BOLD;
//style.SetFont(font);
return style;
}
}
/// <summary>
/// 日期單元格樣式
/// </summary>
private static ICellStyle dateStyle
{
get
{
ICellStyle style = _workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER; //居中
style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
style.WrapText = true;//自動(dòng)換行
// 邊框
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
style.BorderTop = BorderStyle.THIN;
// 字體
//IFont font = _workbook.CreateFont();
//font.FontHeightInPoints = 10;
//font.FontName = "宋體";
//style.SetFont(font);
IDataFormat format = _workbook.CreateDataFormat();
style.DataFormat = format.GetFormat("yyyy-MM-dd");
return style;
}
}
#endregion
#endregion
}
/// <summary>
/// 實(shí)體類(lèi)
/// </summary>
public class NPOIModel
{
/// <summary>
/// 數(shù)據(jù)源
/// </summary>
public DataTable dataSource { get; private set; }
/// <summary>
/// 要導(dǎo)出的數(shù)據(jù)列數(shù)組
/// </summary>
public string[] fileds { get; private set; }
/// <summary>
/// 工作薄名稱(chēng)數(shù)組
/// </summary>
public string sheetName { get; private set; }
/// <summary>
/// 表標(biāo)題
/// </summary>
public string tableTitle { get; private set; }
/// <summary>
/// 表標(biāo)題是否存在 1：存在 0：不存在
/// </summary>
public int isTitle { get; private set; }
/// <summary>
/// 是否添加序號(hào)
/// </summary>
public int isOrderby { get; private set; }
/// <summary>
/// 表頭
/// </summary>
public string headerName { get; private set; }
/// <summary>
/// 取得列寬
/// </summary>
public int[] colWidths { get; private set; }
/// <summary>
/// 構(gòu)造函數(shù)
/// </summary>
/// <remarks>
/// </remarks>
/// <param name="dataSource">數(shù)據(jù)來(lái)源 DataTable</param>
/// <param name="filed">要導(dǎo)出的字段，如果為空或NULL，則默認(rèn)全部</param>
/// <param name="sheetName">工作薄名稱(chēng)</param>
/// <param name="headerName">表頭名稱(chēng) 如果為空或NULL，則默認(rèn)數(shù)據(jù)列字段
/// 相鄰父列頭之間用'#'分隔,父列頭與子列頭用空格(' ')分隔,相鄰子列頭用逗號(hào)分隔(',')
/// 兩行：序號(hào)#分公司#組別#本日成功簽約單數(shù) 預(yù)警,續(xù)約,流失,合計(jì)#累計(jì)成功簽約單數(shù) 預(yù)警,續(xù)約,流失,合計(jì)#任務(wù)數(shù)#完成比例#排名
/// 三行：等級(jí)#級(jí)別#上期結(jié)存 件數(shù),重量,比例#本期調(diào)入 收購(gòu)調(diào)入 件數(shù),重量,比例#本期發(fā)出 車(chē)間投料 件數(shù),重量,比例#本期發(fā)出 產(chǎn)品外銷(xiāo)百分比 件數(shù),重量,比例#平均值
/// 三行時(shí)請(qǐng)注意：列頭要重復(fù)
/// </param>
/// <param name="tableTitle">表標(biāo)題</param>
/// <param name="isOrderby">是否添加序號(hào) 0：不添加 1：添加</param>
public NPOIModel(DataTable dataSource, string filed, string sheetName, string headerName, string tableTitle = null, int isOrderby = 0)
{
if (!string.IsNullOrEmpty(filed))
{
this.fileds = filed.ToUpper().Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
// 移除多余數(shù)據(jù)列
for (int i = dataSource.Columns.Count - 1; i >= 0; i--)
{
DataColumn dc = dataSource.Columns[i];
if (!this.fileds.Contains(dataSource.Columns[i].Caption.ToUpper()))
{
dataSource.Columns.Remove(dataSource.Columns[i]);
}
}
// 列索引
int colIndex = 0;
// 循環(huán)排序
for (int i = 0; i < dataSource.Columns.Count; i++)
{
// 獲取索引
colIndex = GetColIndex(dataSource.Columns[i].Caption.ToUpper());
// 設(shè)置下標(biāo)
dataSource.Columns[i].SetOrdinal(colIndex);
}
}
else
{
this.fileds = new string[dataSource.Columns.Count];
for (int i = 0; i < dataSource.Columns.Count; i++)
{
this.fileds[i] = dataSource.Columns[i].ColumnName;
}
}
this.dataSource = dataSource;
if (!string.IsNullOrEmpty(sheetName))
{
this.sheetName = sheetName;
}
if (!string.IsNullOrEmpty(headerName))
{
this.headerName = headerName;
}
else
{
this.headerName = string.Join("#", this.fileds);
}
if (!string.IsNullOrEmpty(tableTitle))
{
this.tableTitle = tableTitle;
this.isTitle = 1;
}
// 取得數(shù)據(jù)列寬 數(shù)據(jù)列寬可以和表頭列寬比較，采取最長(zhǎng)寬度
colWidths = new int[this.dataSource.Columns.Count];
foreach (DataColumn item in this.dataSource.Columns)
{
colWidths[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
// 循環(huán)比較最大寬度
for (int i = 0; i < this.dataSource.Rows.Count; i++)
{
for (int j = 0; j < this.dataSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(this.dataSource.Rows[i][j].ToString()).Length;
if (intTemp > colWidths[j])
{
colWidths[j] = intTemp;
}
}
}
if (isOrderby > 0)
{
this.isOrderby = isOrderby;
this.headerName = "序號(hào)#" + this.headerName;
}
}
/// <summary>
/// 獲取列名下標(biāo)
/// </summary>
/// <param name="colName">列名稱(chēng)</param>
/// <returns></returns>
private int GetColIndex(string colName)
{
for (int i = 0; i < this.fileds.Length; i++)
{
if (colName == this.fileds[i])
return i;
}
return 0;
}
}
/// <summary>
/// 表頭構(gòu)建類(lèi)
/// </summary>
public class NPOIHeader
{
/// <summary>
/// 表頭
/// </summary>
public string headerName { get; set; }
/// <summary>
/// 起始行
/// </summary>
public int firstRow { get; set; }
/// <summary>
/// 結(jié)束行
/// </summary>
public int lastRow { get; set; }
/// <summary>
/// 起始列
/// </summary>
public int firstCol { get; set; }
/// <summary>
/// 結(jié)束列
/// </summary>
public int lastCol { get; set; }
/// <summary>
/// 是否跨行
/// </summary>
public int isRowSpan { get; private set; }
/// <summary>
/// 是否跨列
/// </summary>
public int isColSpan { get; private set; }
/// <summary>
/// 外加行
/// </summary>
public int rows { get; set; }
public NPOIHeader() { }
/// <summary>
/// 構(gòu)造函數(shù)
/// </summary>
/// <param name="headerName">表頭</param>
/// <param name="firstRow">起始行</param>
/// <param name="lastRow">結(jié)束行</param>
/// <param name="firstCol">起始列</param>
/// <param name="lastCol">結(jié)束列</param>
/// <param name="rows">外加行</param>
/// <param name="cols">外加列</param>
public NPOIHeader(string headerName, int firstRow, int lastRow, int firstCol, int lastCol, int rows = 0)
{
this.headerName = headerName;
this.firstRow = firstRow;
this.lastRow = lastRow;
this.firstCol = firstCol;
this.lastCol = lastCol;
// 是否跨行判斷
if (firstRow != lastRow)
isRowSpan = 1;
if (firstCol != lastCol)
isColSpan = 1;
this.rows = rows;
}
}
}
3、導(dǎo)出代碼示例如下：
/// <summary>
/// 導(dǎo)出測(cè)點(diǎn)列表表格
/// </summary>
[HttpGet]
[AllowAnonymous]
public void ExportMeasurePointData(string TreeID, string TreeType)
{
DataTable dtResult = new DataTable();
DataTable dtExcel = new DataTable();
try
{
string sql = string.Format("EXEC P_GET_ZXJG_TagList '{0}','{1}'", TreeID, TreeType);
dtResult = QuerySQL.GetDataTable(sql);
dtExcel = dtResult.Copy();
dtExcel.Columns.Add("xuhao", typeof(string));
dtExcel.Columns.Add("StrValueTime", typeof(string));
dtExcel.Columns["xuhao"].SetOrdinal(0);
dtExcel.Columns["StrValueTime"].SetOrdinal(2);
for (int i = 0; i < dtResult.Rows.Count; i++)
{
dtExcel.Rows[i]["xuhao"] = (i + 1).ToString();
dtExcel.Rows[i]["StrValueTime"] = Convert.ToDateTime(dtResult.Rows[i]["F_ValueTime"]).ToString("yyyy-MM-dd HH:mm:ss");
}
List<NPOIModel> list = new List<NPOIModel>();
list.Add(new NPOIModel(dtExcel, "xuhao;F_Description;StrValueTime;F_Value;F_Unit;F_AlmLow;F_AlmUp", "sheet", "序號(hào)#監(jiān)測(cè)點(diǎn)#采集時(shí)間#當(dāng)前數(shù)值#工程單位#報(bào)警下限#報(bào)警上限"));
ATNPOIHelper.Export("測(cè)點(diǎn)列表", list, 0);
}
catch (Exception ex)
{
}
}
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
您可能感興趣的文章:
- Asp.net MVC實(shí)現(xiàn)生成Excel并下載功能
- ASP.NET實(shí)現(xiàn)上傳Excel功能
- ASP.NET保存PDF、Word和Excel文件到數(shù)據(jù)庫(kù)
- ASP.NET Core 導(dǎo)入導(dǎo)出Excel xlsx 文件實(shí)例
- ASP.NET MVC使用EPPlus，導(dǎo)出數(shù)據(jù)到Excel中
- asp.net DataTable導(dǎo)出Excel自定義列名的方法
- Asp.net實(shí)現(xiàn)直接在瀏覽器預(yù)覽Word、Excel、PDF、Txt文件（附源碼）
- asp.net實(shí)現(xiàn)導(dǎo)出DataTable數(shù)據(jù)到Word或者Excel的方法
- ASP.Net動(dòng)態(tài)讀取Excel文件最簡(jiǎn)方法
相關(guān)文章
這篇文章主要介紹了.NET中文亂碼的解決方法，有需要的朋友可以參考一下2013-12-12
這篇文章主要介紹了asp.net簡(jiǎn)單生成XML文件的方法,結(jié)合實(shí)例形式分析了2種生成XML文件的技巧,需要的朋友可以參考下2016-04-04
asp.net 關(guān)于字符串內(nèi)范圍截取的一點(diǎn)方法總結(jié)
前兩天有一位網(wǎng)友提出了一個(gè)字符串內(nèi)截取字符串的問(wèn)題，除了用普通的字符串截取的方式外，我推薦的是用LINQ方式來(lái)截取。兩者實(shí)際上差別不是很大，都是采用字符串截取方式，但后者從寫(xiě)法和觀(guān)察效果會(huì)比前者簡(jiǎn)單實(shí)用得多。2010-02-02
asp.net 通過(guò)httpModule計(jì)算頁(yè)面的執(zhí)行時(shí)間
有時(shí)候我們想檢測(cè)一下網(wǎng)頁(yè)的執(zhí)行效率。記錄下開(kāi)始請(qǐng)求時(shí)的時(shí)間和頁(yè)面執(zhí)行完畢后的時(shí)間點(diǎn)，這段時(shí)間差就是頁(yè)面的執(zhí)行時(shí)間了。要實(shí)現(xiàn)這個(gè)功能，通過(guò)HttpModule來(lái)實(shí)現(xiàn)是最方便而且準(zhǔn)確的。2011-02-02
dz asp.net論壇中函數(shù)--根據(jù)Url獲得源文件內(nèi)容
從asp.net dz論壇發(fā)現(xiàn)的這個(gè)函數(shù)，學(xué)習(xí)一下高手的經(jīng)驗(yàn)代碼2008-09-09
這篇文章主要介紹了ASP.NET MVC中的AJAX應(yīng)用的相關(guān)資料,需要的朋友可以參考下2014-10-10
ASP.NET Core MVC如何實(shí)現(xiàn)運(yùn)行時(shí)動(dòng)態(tài)定義Controller類(lèi)型
這篇文章主要介紹了ASP.NET Core MVC如何實(shí)現(xiàn)運(yùn)行時(shí)動(dòng)態(tài)定義Controller類(lèi)型，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-06-06
asp.net實(shí)現(xiàn)在線(xiàn)音樂(lè)播放器示例
這篇文章主要介紹了asp.net實(shí)現(xiàn)在線(xiàn)音樂(lè)播放器示例,需要的朋友可以參考下2014-02-02
asp.net 擴(kuò)展GridView 增加單選按鈕列的代碼
asp.net 擴(kuò)展GridView 增加單選按鈕列的代碼2010-02-02
httpHandler實(shí)現(xiàn).Net無(wú)后綴名Web訪(fǎng)問(wèn)的實(shí)現(xiàn)解析
有時(shí)候我們看到很多網(wǎng)站是網(wǎng)址是沒(méi)有后綴名的，其實(shí).net中可以通過(guò)httpHandler來(lái)實(shí)現(xiàn)。2011-10-10