欢迎加入技术交流QQ群80669150 (附加信息：珠海 -＂Lzw )一起学习 !
导出Excel所引用的dll，Aspose.Cells.dll下载地址：
http://share.weiyun.com/a280eb4771aedebcec22e806fc77cb9b
/// <summary>
/// 导出正式Excel文件，用于导入盘点数据
/// </summary>
/// <param name="ds2"></param>
private void ToExcel(DataSet ds2)
{
if (ds2.Tables[0].Rows.Count > 0)
{
//临时存放路径
string filePath = Server.MapPath("~/upload/KCPD_" + Session["UserName"].ToString() + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls");
//将数据写入Excel
CreateSheet(ds2.Tables[0], filePath);
//文件名称
string Redirectpath = "~\\upload\\" + filePath.Substring(filePath.LastIndexOf("\\") + 1);
//输出副本的二进制字节流
HttpContext.Current.Response.Charset = "UTF-8"; // 或UTF-7 以防乱码
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/ms-excel";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(System.Text.Encoding.GetEncoding(65001).GetBytes("KCPD_" + Session["UserName"].ToString() + "_" + this.txtTimeBegin.Text)) + ".xls;");
Response.BinaryWrite(File.ReadAllBytes(filePath));
//删除临时文件
if (File.Exists(filePath))
File.Delete(filePath);
Response.End();
}
}
/// <summary>
/// 创建Excel表
/// </summary>
/// <param name="dt">数据源_设备</param>
/// <param name="filePath">Excel临时存储路径</param>
public void CreateSheet(DataTable dt, string filePath)
{
int cells = dt.Columns.Count;
int Rows = dt.Rows.Count;
Workbook workbook = new HSSFWorkbook();//创建Workbook对象
Sheet sheet = workbook.CreateSheet("Sheet1");//创建工作表
sheet.CreateFreezePane(0, 1);//锁定第一行
//sheet.SetColumnWidth(1, 30 * 256);
//sheet.SetColumnWidth(2, 30 * 256);
//sheet.SetColumnWidth(3, 30 * 256);
//sheet.SetColumnWidth(6, 14 * 256);
//sheet.SetColumnWidth(7, 23 * 256);
SetCell(dt, Rows, workbook, sheet);
using (Stream stream = File.Open(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(stream);
}
}
/// <summary>
/// 追加内容到Excel Sheet1表中
/// </summary>
/// <param name="dt">数据源_设备</param>
/// <param name="Rows">总记录数</param>
/// <param name="sheet">Excel表格名称</param>
private void SetCell(DataTable dt, int Rows, Workbook workbook, Sheet sheet)
{
#region 列名部分
/*
*1 配件ID,2 识别码,3 配件名称,4 配件规格,5 配件代码,6 版本,7 上月库存,8 采购入库,
*9 良品入库,10 维修返回,11 维修入库,12 调整库存,13 调拨入库,14 销售出库,15 免费出库,
*16 调拨出库,17 维修出库,18 其它出库,19 现有库存,20 盘点库存
*
*/
Row row0 = sheet.CreateRow(0);
row0.HeightInPoints = 15; //行高
Cell cell0 = row0.CreateCell(0); //在行中添加一列
cell0.SetCellValue("配件ID");//设置列的内容
Cell cell1 = row0.CreateCell(1);
cell1.SetCellValue("识别码");
Cell cell2 = row0.CreateCell(2);
cell2.SetCellValue("配件名称");
Cell cell3 = row0.CreateCell(3);
cell3.SetCellValue("配件规格");
Cell cell4 = row0.CreateCell(4);
cell4.SetCellValue("配件代码");
Cell cell5 = row0.CreateCell(5);
cell5.SetCellValue("版本");
Cell cell6 = row0.CreateCell(6);
cell6.SetCellValue("上月库存");
Cell cell7 = row0.CreateCell(7);
cell7.SetCellValue("采购入库");
Cell cell8 = row0.CreateCell(8);
cell8.SetCellValue("良品入库");
Cell cell9 = row0.CreateCell(9);
cell9.SetCellValue("维修返回");
Cell cell10 = row0.CreateCell(10);
cell10.SetCellValue("维修入库");
Cell cell11 = row0.CreateCell(11);
cell11.SetCellValue("调整库存");
Cell cell12 = row0.CreateCell(12);
cell12.SetCellValue("调拨入库");
Cell cell13 = row0.CreateCell(13);
cell13.SetCellValue("销售出库");
Cell cell14 = row0.CreateCell(14);
cell14.SetCellValue("免费出库");
Cell cell15 = row0.CreateCell(15);
cell15.SetCellValue("调拨出库");
Cell cell16 = row0.CreateCell(16);
cell16.SetCellValue("维修出库");
Cell cell17 = row0.CreateCell(17);
cell17.SetCellValue("其它出库");
Cell cell18 = row0.CreateCell(18);
cell18.SetCellValue("现有库存");
Cell cell19 = row0.CreateCell(19);
cell19.SetCellValue("盘点库存");
#endregion
#region 数据部分
for (int r = 0; r < Rows; r++)
{
Row row = sheet.CreateRow(r + 1);//在工作表中添加一行
/*
*1 配件ID,2 识别码,3 配件名称,4 配件规格,5 配件代码,6 版本,7 上月库存,8 采购入库,
*9 良品入库,10 维修返回,11 维修入库,12 调整库存,13 调拨入库,14 销售出库,15 免费出库,
*16 调拨出库,17 维修出库,18 其它出库,19 现有库存,20 盘点库存
*
*/
int cell = 0;
row.HeightInPoints = 15; //行高
row.CreateCell(cell++).SetCellValue(Convert.ToInt32(dt.Rows[r]["配件ID"]));
row.CreateCell(cell++).SetCellValue(dt.Rows[r]["识别码"].ToString());
row.CreateCell(cell++).SetCellValue(dt.Rows[r]["配件名称"].ToString());
row.CreateCell(cell++).SetCellValue(dt.Rows[r]["配件规格"].ToString());
row.CreateCell(cell++).SetCellValue(dt.Rows[r]["配件代码"].ToString());
row.CreateCell(cell++).SetCellValue(dt.Rows[r]["版本"].ToString());
row.CreateCell(cell++).SetCellValue(Convert.ToInt32(dt.Rows[r]["上月库存"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["采购入库"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["良品入库"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["维修返回"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["维修入库"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["调整库存"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["调拨入库"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["销售出库"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["免费出库"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["调拨出库"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["维修出库"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["其它出库"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["现有库存"]));
row.CreateCell(cell++).SetCellValue(Convert.ToDouble(dt.Rows[r]["盘点库存"]));
}
#endregion
}
