最近做项目遇到导出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);

}

}