最近做项目遇到导出Excel的问题总结一下：
看代码：
复制代码 代码如下:
/// <summary>
/// 生成Excel的方法
/// </summary>
/// <param name="ds">DataSet</param>
/// <param name="url">Excel存在服务器的相对地址</param>
/// <returns></returns>
private bool ExportExcel(DataSet ds, string path)
{
//创建标题行
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet("报名情况");
HSSFRow rowtitle = sheet.CreateRow(0);
//设置列宽
sheet.SetColumnWidth(0, 30 * 256);
sheet.SetColumnWidth(1, 30 * 256);
sheet.SetColumnWidth(2, 30 * 256);
sheet.SetColumnWidth(3, 30 * 256);
sheet.SetColumnWidth(4, 30 * 256);
sheet.SetColumnWidth(5, 30 * 256);
sheet.SetColumnWidth(6, 30 * 256);
sheet.SetColumnWidth(7, 30 * 256);
sheet.SetColumnWidth(8, 30 * 256);
sheet.SetColumnWidth(9, 30 * 256);
//创建列
rowtitle.CreateCell(0,HSSFCellType.STRING).SetCellValue("姓名");
rowtitle.CreateCell(1, HSSFCellType.STRING).SetCellValue("资质证书编号");
rowtitle.CreateCell(2, HSSFCellType.STRING).SetCellValue("职业资格等级");
rowtitle.CreateCell(3, HSSFCellType.STRING).SetCellValue("性别");
rowtitle.CreateCell(4, HSSFCellType.STRING).SetCellValue("身份证号");
rowtitle.CreateCell(5, HSSFCellType.STRING).SetCellValue("从业信息识别卡编号");
rowtitle.CreateCell(6, HSSFCellType.STRING).SetCellValue("原机构名称");
rowtitle.CreateCell(7, HSSFCellType.STRING).SetCellValue("原机构编号");
rowtitle.CreateCell(8, HSSFCellType.STRING).SetCellValue("变更机构名称");
rowtitle.CreateCell(9, HSSFCellType.STRING).SetCellValue("变更机构编号");
//DataSet是一个DataTale的集合，如果只是填充了1张表，则此表的ID为0
DataTable dt = ds.Tables[0];
int i = 1;
foreach (DataRow row in dt.Rows)
{
HSSFRow newrow = sheet.CreateRow(i);
newrow.CreateCell(0,HSSFCellType.STRING).SetCellValue(Convert.ToString(row["R_XM"]));
newrow.CreateCell(1, HSSFCellType.STRING).SetCellValue(Convert.ToString(row["R_newzzbh"]));
string jibie=string.Empty;
if (row["R_jb"].ToString()=="1")
{
jibie = "一级";
}
else if (row["R_jb"].ToString() == "2")
{
jibie = "二级";
}
else if (row["R_jb"].ToString() == "3")
{
jibie = "三级";
}
newrow.CreateCell(2,HSSFCellType.STRING).SetCellValue(jibie);
newrow.CreateCell(3,HSSFCellType.STRING).SetCellValue(Convert.ToString(row["R_XB"]));
newrow.CreateCell(4,HSSFCellType.STRING).SetCellValue(Convert.ToString(row["user_id"]));
newrow.CreateCell(5,HSSFCellType.STRING).SetCellValue(Convert.ToString(row["R_KH"]));
newrow.CreateCell(6,HSSFCellType.STRING).SetCellValue(Convert.ToString(row["yjgmc"]));
newrow.CreateCell(7,HSSFCellType.STRING).SetCellValue(Convert.ToString(row["yjgbh"]));
newrow.CreateCell(8,HSSFCellType.STRING).SetCellValue(Convert.ToString(row["bjgmc"]));
newrow.CreateCell(9,HSSFCellType.STRING).SetCellValue(Convert.ToString(row["bjgbh"]));
i++;
}
try
{
using (Stream stream = File.Open(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(stream);
}
return true;
}
catch (Exception)
{
return false;
throw;
}
}
导出方法：
复制代码 代码如下:
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void linkExport_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds = apryManager.Export();
Random rd = new Random();
int rd1= rd.Next(111111,999999);
string path = this.Server.MapPath("~anxieExecl") + DateTime.Now.ToString("yyyyMMddhhmmss")+ rd1.ToString() + ".xls";
if (!Directory.Exists(this.Server.MapPath("~anxieExecl")))
{
Directory.CreateDirectory(this.Server.MapPath("~anxieExecl"));
}
bool status = ExportExcel(ds,path);
string Redirectpath = "~anxieExecl" + path.Substring(path.LastIndexOf("") + 1);
if (status)
{
Response.Redirect(Redirectpath);
File.Delete(path);
}
else
{
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('生成Excel失败！')", true);
}
}