Asp.net程序中用NPOI生成标准Excel报表，导入导出一应俱全[转] - HackerVirus

1 　 //普通形式的下载Excel（HTML格式的Excel）
2
3 　　//dt当然是你一些的要导出的数据返回一个DataTable
4 　　//fileName是自己定义的文件导出的名字
5 　　 protectedvoid CreateExcel(DataTable dt,string fileName)
6 　　 {
7 　　　　 StringBuilder strb =new StringBuilder();
8 　　　　 strb.Append(" <table align=\"center\" border='1px' style='border-collapse:collapse;table-layout:fixed;font-size:12px'> <tr>");
9
10 　　　　//写列标题
11 　　　　 int columncount = dt.Columns.Count;
12 　　　　for (int columi =0; columi < columncount; columi++)
13 　　　　{
14 　　　　　　 strb.Append(" <td> <b>"+ dt.Columns[columi] +" </b> </td>");
15 　　　　}
16 　　　　 strb.Append(" </tr>");
17 　　　　//写数据
18 　　　　 for (int i =0; i < dt.Rows.Count; i++)
19 　　　　{
20 　　　　　　strb.Append(" <tr>");
21 　　　　　　for (int j =0; j < dt.Columns.Count; j++)
22 　　　　　　{
23 　　　　　　　　 strb.Append(" <td>"+ dt.Rows[i][j].ToString() +" </td>");
24 　　　　　　}
25 　　　　　　strb.Append(" </tr>"); }
26 　　　　　　 strb.Append(" </table>");
27
28
29 　　　　　　Response.Clear();
30 　　　　　　Response.Buffer=true;
31 　　　　　　Response.Charset="GB2312";
32 　　　　　　Response.AppendHeader("Content-Disposition","attachment;filename="+ FileName +".xls");
33 　　　　　　Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
34 　　　　　　Response.ContentType ="application/vnd.xls";//设置输出文件类型为excel文件。
35 　　　　　　this.EnableViewState =false;
36 　　　　　　Response.Write(strb);
37 　　　　　　Response.End();
38 　　　　}
39
40

using System;
using System.Data;
using System.IO;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using System.Text;

publicclass ExcelHelper
{
///<summary>
/// 由DataSet导出Excel
///</summary>
///<param name="sourceTable">要导出数据的DataTable</param>
///<param name="sheetName">工作表名称</param>
///<returns>Excel工作表</returns>
privatestatic Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
{
HSSFWorkbook workbook =new HSSFWorkbook();
MemoryStream ms =new MemoryStream();
string [] sheetNames = sheetName.Split(',');
for(int i =0;i< sheetNames.Length; i++)
{
HSSFSheet sheet = workbook.CreateSheet(sheetNames[i]);
HSSFRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceDs.Tables[i].Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

// handling value.
int rowIndex =1;

foreach (DataRow row in sourceDs.Tables[i].Rows)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex);

foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
　　　　 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}

rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position =0;

workbook =null;
return ms;
}

///<summary>
/// 由DataSet导出Excel
///</summary>
///<param name="sourceTable">要导出数据的DataTable</param>
///<param name="fileName">指定Excel工作表名称</param>
///<returns>Excel工作表</returns>
publicstaticvoid ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName)
{
MemoryStream ms = ExportDataSetToExcel(sourceDs, sheetName) as MemoryStream;
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename="+ fileName);
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
ms.Close();
ms =null;
}

///<summary>
/// 由DataTable导出Excel
///</summary>
///<param name="sourceTable">要导出数据的DataTable</param>
///<returns>Excel工作表</returns>
privatestatic Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName)
{
HSSFWorkbook workbook =new HSSFWorkbook();
MemoryStream ms =new MemoryStream();
HSSFSheet sheet = workbook.CreateSheet(sheetName);
HSSFRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

// handling value.
int rowIndex =1;

foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex);

foreach (DataColumn column in sourceTable.Columns)
{
　　　　 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}

rowIndex++;
}

workbook.Write(ms);
ms.Flush();
ms.Position =0;

sheet =null;
headerRow =null;
workbook =null;

return ms;
}
///<summary>
/// 由DataTable导出Excel
///</summary>
///<param name="sourceTable">要导出数据的DataTable</param>
///<param name="fileName">指定Excel工作表名称</param>
///<returns>Excel工作表</returns>
publicstaticvoid ExportDataTableToExcel(DataTable sourceTable, string fileName, string sheetName)
{
MemoryStream ms = ExportDataTableToExcel(sourceTable, sheetName) as MemoryStream;
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename="+ fileName);
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
ms.Close();
ms =null;
}

///<summary>
/// 由Excel导入DataTable
///</summary>
///<param name="excelFileStream">Excel文件流</param>
///<param name="sheetName">Excel工作表名称</param>
///<param name="headerRowIndex">Excel表头行索引</param>
///<returns>DataTable</returns>
publicstatic DataTable ImportDataTableFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex)
{
HSSFWorkbook workbook =new HSSFWorkbook(excelFileStream);
HSSFSheet sheet = workbook.GetSheet(sheetName);

DataTable table =new DataTable();

HSSFRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;

for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column =new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}

for (int i = (sheet.FirstRowNum +1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();

for (int j = row.FirstCellNum; j < cellCount; j++)
dataRow[j] = row.GetCell(j).ToString();
}

excelFileStream.Close();
workbook =null;
sheet =null;
return table;
}

///<summary>
/// 由Excel导入DataTable
///</summary>
///<param name="excelFilePath">Excel文件路径，为物理路径。</param>
///<param name="sheetName">Excel工作表名称</param>
///<param name="headerRowIndex">Excel表头行索引</param>
///<returns>DataTable</returns>
publicstatic DataTable ImportDataTableFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ImportDataTableFromExcel(stream, sheetName, headerRowIndex);
}
}

///<summary>
/// 由Excel导入DataTable
///</summary>
///<param name="excelFileStream">Excel文件流</param>
///<param name="sheetName">Excel工作表索引</param>
///<param name="headerRowIndex">Excel表头行索引</param>
///<returns>DataTable</returns>
publicstatic DataTable ImportDataTableFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex)
{
HSSFWorkbook workbook =new HSSFWorkbook(excelFileStream);
HSSFSheet sheet = workbook.GetSheetAt(sheetIndex);

DataTable table =new DataTable();

HSSFRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;

for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) ==null|| headerRow.GetCell(i).StringCellValue.Trim() =="")
{
// 如果遇到第一个空列，则不再继续向后读取
cellCount = i +1;
break;
}
DataColumn column =new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}

for (int i = (sheet.FirstRowNum +1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
if (row ==null|| row.GetCell(0) ==null|| row.GetCell(0).ToString().Trim() =="")
{
// 如果遇到第一个空行，则不再继续向后读取
break;
}

DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[j] = row.GetCell(j);
}
table.Rows.Add(dataRow);
}
excelFileStream.Close();
workbook =null;
sheet =null;
return table;
}
///<summary>
/// 由Excel导入DataTable
///</summary>
///<param name="excelFilePath">Excel文件路径，为物理路径。</param>
///<param name="sheetName">Excel工作表索引</param>
///<param name="headerRowIndex">Excel表头行索引</param>
///<returns>DataTable</returns>
publicstatic DataTable ImportDataTableFromExcel(string excelFilePath, int sheetIndex, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ImportDataTableFromExcel(stream, sheetIndex, headerRowIndex);
}
}

///<summary>
/// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
///</summary>
///<param name="excelFileStream">Excel文件流</param>
///<param name="headerRowIndex">Excel表头行索引</param>
///<returns>DataSet</returns>
publicstatic DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex)
{
DataSet ds =new DataSet();
HSSFWorkbook workbook =new HSSFWorkbook(excelFileStream);
for (int a =0, b = workbook.NumberOfSheets; a < b; a++)
{
HSSFSheet sheet = workbook.GetSheetAt(a);
DataTable table =new DataTable();

HSSFRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) ==null|| headerRow.GetCell(i).StringCellValue.Trim() =="")
{
// 如果遇到第一个空列，则不再继续向后读取
cellCount = i +1;
break;
}

DataColumn column =new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}

for (int i = (sheet.FirstRowNum +1); i <= sheet.LastRowNum ; i++)
{
HSSFRow row = sheet.GetRow(i);
if (row ==null|| row.GetCell(0) ==null|| row.GetCell(0).ToString().Trim() =="")
{
// 如果遇到第一个空行，则不再继续向后读取
break;
}

DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) !=null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}

table.Rows.Add(dataRow);
}
ds.Tables.Add(table);
}

excelFileStream.Close();
workbook =null;

return ds;
}

///<summary>
/// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
///</summary>
///<param name="excelFilePath">Excel文件路径，为物理路径。</param>
///<param name="headerRowIndex">Excel表头行索引</param>
///<returns>DataSet</returns>
publicstatic DataSet ImportDataSetFromExcel(string excelFilePath, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ImportDataSetFromExcel(stream, headerRowIndex);
}
}

///<summary>
/// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...
///</summary>
///<param name="index">列索引</param>
///<returns>列名，如第0列为A，第1列为B...</returns>
publicstaticstring ConvertColumnIndexToColumnName(int index)
{
index = index +1;
int system =26;
char[] digArray =newchar[100];
int i =0;
while (index >0)
{
int mod = index % system;
if (mod ==0) mod = system;
digArray[i++] = (char)(mod -1+'A');
index = (index -1) /26;
}
StringBuilder sb =new StringBuilder(i);
for (int j = i -1; j >=0; j--)
{
sb.Append(digArray[j]);
}
return sb.ToString();
}

///<summary>
/// 转化日期
///</summary>
///<param name="date">日期</param>
///<returns></returns>
publicstatic DateTime ConvertDate(string date)
{
DateTime dt =new DateTime();
string[] time = date.Split('-');
int year = Convert.ToInt32(time[2]);
int month = Convert.ToInt32(time[0]);
int day = Convert.ToInt32(time[1]);
string years = Convert.ToString(year);
string months = Convert.ToString(month);
string days = Convert.ToString(day);
if(months.Length ==4)
{
dt = Convert.ToDateTime(date);
}
else
{
string rq ="";
if(years.Length ==1)
{
years ="0"+ years;
}
if(months.Length ==1)
{
months ="0"+ months;
}
if(days.Length ==1)
{
days ="0"+ days;
}
rq ="20"+ years +"-"+ months +"-"+ days;
dt = Convert.ToDateTime(rq);
}
return dt;
}

}

//导出高低温类型产品信息
privatevoid btnExportCp_Click(object sender, System.EventArgs e)
{
string sql =@"
SELECT CPBM AS 产品编码,CPMC + GGXH AS 产品名称 FROM XS_CPBM WHERE CPLX = '03'AND ZT = '1'

SELECT CPBM AS 产品编码,CPMC + GGXH AS 产品名称 FROM XS_CPBM WHERE CPLX = '06'AND ZT = '1'
";
DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text,sql);

string fileName ="高低温产品档案导出.xls";
ExcelHelper.ExportDataSetToExcel(ds,fileName,"高温,低温");

}

导航