NOPI 做Excel 导出确实很方便 ，但是一直在用没好好研究。
在网上没找到自定义Columns的方法 ，于是乎自己就在原来的方法上简单地改改。
想用的童鞋们可以直接拿去用！
/// 数据大于65536时使用
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="Columns">列名</param>
/// <returns></returns>
public static byte[] ExportToExcel_Columns(DataTable dt, ArrayList Columns)
{
DataColumnCollection str = dt.Columns;
if (str.Count == ) return null;
HSSFWorkbook hssfworkbook;
hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
List<NPOI.SS.UserModel.ISheet> sheetList = new List<NPOI.SS.UserModel.ISheet>();
sheetList.Add(sheet1); int rows = dt.Rows.Count + ;
int p = rows % == ? rows / : (rows / ) + ;
for (int i = ; i < p; i++)
{
ISheet sheet = hssfworkbook.CreateSheet("sheet" + (i + ).ToString());
sheetList.Add(sheet);
}
LargeDataExport_Columns(hssfworkbook, sheetList, dt, Columns);
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
file.Close();
return file.ToArray();
} private static void LargeDataExport_Columns(NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook, List<NPOI.SS.UserModel.ISheet> sheetCollection, DataTable dt, ArrayList Columns)
{
// DataColumnCollection str = dt.Columns; //定义表头，原来是从datatable中获取的columns for (int i = ; i < sheetCollection.Count; i++)
{
ISheet sheet1 = sheetCollection[i]; if (i == )
{
IRow headerRow = sheet1.CreateRow();
for (int m = , len = Columns.Count; m < len; m++)
{
ICell curCell = headerRow.CreateCell(m);
headerRow.Height = * ;
ICellStyle style = hssfworkbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = HSSFColor.Grey25Percent.LightOrange.Index;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = ;
font.Color = HSSFColor.White.Index; //HSSFColor.WHITE.index;
style.SetFont(font);
curCell.CellStyle = style;
curCell.SetCellValue(Columns[m].ToString());
sheet1.SetColumnWidth(m, * );
}
} for (int j = i * ; j < (i + ) * ; j++)
{
if (j > dt.Rows.Count - )
break;
IRow row = sheet1.CreateRow(j - * i + );
row.Height = * ; for (int k = ; k < dt.Columns.Count; k++)
{
ICell rowCell = row.CreateCell(k);
rowCell.SetCellValue(dt.Rows[j][k].ToString());
}
}
}
}
程序调用：
/// <summary>
///xxxx数据导出
/// </summary>
/// <param name="flg"></param>
/// <returns></returns>
public FileResult ExportExcel_ProfitDetails()
{
DataSet ds = new DataSet();
//获取当前操作用户
string userRealName = "";
if (!string.IsNullOrEmpty(Session["user"].ToString()))
{
userRealName = Session["user"].ToString();
} tempLog.Info(string.Format("用户：{0}正在做xxxx数据导出操作", userRealName));
string date = Request["datetime"].ToString();
ds = Automation.Common.DbHelperSQL.Query("ColligateExport");
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
System.IO.MemoryStream ms = new System.IO.MemoryStream(); NPOIExcelHelper n = new NPOIExcelHelper();
byte[] fileArr = null;
try
{
string[] a = new string[] { "ID", "应支付金额", "应支付日期", "支付期数","支付状态","公司收入","公司支出"
,"支付类型","工单ID","收益明细ID", "转让债权价值","还款金额","还款日期","实际支付金额","实际本金","实际利息"};
ArrayList alist = new ArrayList();
alist.AddRange(a);
fileArr = NPOIExcelHelper.ExportToExcel_Columns(ds.Tables[],alist);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
string fileName = "xxxx数据" + System.DateTime.Now.ToString("yyyyMMddhhssmm");
tempLog.Info(string.Format("导出xxxx数据的Excel文件名为：{0}", fileName));
return File(fileArr, "application/vnd.ms-excel", fileName + ".xls");
}
最后：使用时记得加上NOPI 引用哦！