将DataSet对象导出成Excel文档
一、不带格式控制
void btnExport_Click(object sender, EventArgs e)
{
IList
IList
fields.Add("数据库字段名1");
headers.Add("Excel字段名1");
fields.Add("数据库字段名2");
headers.Add("Excel字段名2");
StringBuilder strFlag = new StringBuilder();
foreach (string head in headers)
{
strFlag.Append(head + ",");
if (head == headers\[headers.Count - \])
{
strFlag = strFlag.Remove(strFlag.Length - , );
strFlag.Append("\\r\\n");
}
}
DataSet dt = new DataSet();//从数据库获取到数据
if (dt != null && dt.Tables\[\].Rows.Count > )
{
foreach (DataRow row in dt.Tables\[\].Rows)
{
foreach (string field in fields)
{
strFlag.Append(row\[field\]).Append(",");
if (field == fields\[headers.Count - \])
{
strFlag = strFlag.Remove(strFlag.Length - , );
strFlag.Append("\\r\\n");
}
}
}
Page.Response.Clear();
Page.Response.Buffer = false;
Page.Response.Charset = "GB2312";
Page.Response.AppendHeader("Content-Disposition", "attachment;filename=Excel文件名.csv");
Page.Response.ContentType = "application/octet-stream";
Page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Page.EnableViewState = false;
Page.Response.Write(strFlag.ToString());
Page.Response.End();
}
}
简洁版
二、可控制导出格式(是目前.net导出excel最适合的解决方案)
//引用NPOI.dll
//using NPOI.HSSF.UserModel;
//using NPOI.HPSF;
//using NPOI.POIFS.FileSystem;
//using NPOI.SS.UserModel;
DataSet dt = new DataSet();//从数据库读取数据;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
workbook.CreateSheet("Sheet2");
workbook.CreateSheet("Sheet3");//一般Excel都有三个Sheet
HSSFRow row = (HSSFRow)sheet1.CreateRow();
row.CreateCell().SetCellValue("姓名");
row.CreateCell().SetCellValue("性别");
row.CreateCell().SetCellValue("年龄");
//样式,更多样式见文档
ICellStyle cellStyle_cny = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
//cellStyle_cny.DataFormat = format.GetFormat("¥#,##0.00;¥-#,##0.00");//价格样式
cellStyle_cny.Alignment = HorizontalAlignment.Center;//左右居中
cellStyle_cny.VerticalAlignment = VerticalAlignment.Center;//上下居中
if (dt != null && dt.Tables[].Rows.Count > )
{
for (int i = ; i < dt.Tables[].Rows.Count; i++)
{
row = (HSSFRow)sheet1.CreateRow(i + );
row.CreateCell().SetCellValue(dt.Tables\[\].Rows\[i\]\["Name"\].ToString());
row.CreateCell().SetCellValue(dt.Tables\[\].Rows\[i\]\["Sex"\].ToString()); row.CreateCell().SetCellValue(Convert.ToInt32(dt.Tables\[\].Rows\[i\]\["Age"\]));
row.GetCell().CellStyle = cellStyle\_cny;//设置样式
}
}
//sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1,4,2,2)); //合并单元格,将第3列的第二行到第五行合并为一个单元格(下标从0开始)
//导出
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
Page.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=我的消费记录{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Page.Response.BinaryWrite(ms.ToArray());
workbook = null;
ms.Close();
ms.Dispose();
导出带样式的Excel
注:
1、NPOI.dll可到官网下载,或网上搜索下载。官网:http://npoi.codeplex.com/
2、关于NPOI是什么,以及相关文档,请浏览:http://blog.csdn.net/pan_junbiao/article/details/39717443
3、导出文件名如果是中文的话会出现乱码的解决方法:
//将中文名转码之后再加入文件头
string filename = HttpUtility.UrlEncode(System.Text.UTF8Encoding.UTF8.GetBytes("文件名.xls"));
Page.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);
补充第二点,设置所导出的文档的详细信息,就是右击文件--属性--详细信息,所看到的信息
//代码为第二点的代码补充(一些变量在第二点中声明)
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "xx公司";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Title = "说明标题1";
si.Subject = "主题1";
si.Comments = "备注1";
workbook.DocumentSummaryInformation = dsi;
workbook.SummaryInformation = si;
设置导出的Excel文档的详细信息
手机扫一扫
移动阅读更方便
你可能感兴趣的文章