Excel操作：导出到Excel并下载到web客户端
最近在弄一个导出到Excel的多个sheet的文件，查询后展示代码如下：
protected void btnExport_Click(object sender, EventArgs e)
{
DateTime checkTime = DateTime.Now;
if (DateTime.TryParse(hdCheckTime.Value, out checkTime))
{
DataSet ds = BusinessFacade.GranaryScoreManager.GetCompanyScoreData(hdSelectCompany.Value, checkTime);
Microsoft.Office.Interop.Excel.Application app = null;
Workbook wBook = null;
string filename = "各分公司" + checkTime.ToString("yyyy年MM月dd日") + "考核汇总";
string fullFilePath = Server.MapPath("~/Res/ReportForm/" + filename + Guid.NewGuid().ToString() + ".xls");
try
{
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
wBook = app.Workbooks.Add(true);
string s = wBook.Name;// = filename+DateTime.Now.ToString("yyyy年MM月dd HHmmssffff");
Dictionary<string, string> sheetNames = new Dictionary<string, string>();
int shindex = 0;
Worksheet wSheet = null;
var v = ds.Tables[0].AsEnumerable().OrderByDescending(a => int.Parse(a["HSort"].ToString()));
foreach (DataRow dr in v)
{
string cid = dr["CompanyID"].ToString();
string cname = dr["CompanyName"].ToString();
CheckRules rule = CheckRulesManager.GetModel(cid);
string lastCheckTimeStr = "";
DateTime lastCheckTime = DateTime.Now;
if (DateTime.TryParse(dr["CheckTime"].ToString(), out lastCheckTime))
lastCheckTimeStr = lastCheckTime.ToString("yyyy年MM月dd日");
if (shindex == 0)
{
wSheet = wBook.Sheets.get_Item(1);
shindex++;
}
else
{
wSheet = wBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
wSheet.Name = cname + DateTime.Now.ToString("HHmmssffff");
sheetNames.Add(wSheet.Name, string.IsNullOrWhiteSpace(dr["Alias"].ToString()) ? cname : dr["Alias"].ToString());
Microsoft.Office.Interop.Excel.Range rg = wSheet.Range["A1:K1"];//.get_Range(wSheet.Cells[rowIndex, 1], wSheet.Cells[rowIndex, 11]);
rg.Merge(rg.MergeCells);
rg.Font.Bold = true;
rg.Font.Size = 14;
rg.HorizontalAlignment = XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range rgColumnTitle = wSheet.Range["A2:K2"];
rgColumnTitle.Font.Bold = true;
rgColumnTitle.Font.Size = 12;
rgColumnTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter;
int rowIndex = 1;
wSheet.Cells[rowIndex, 1] = cname + lastCheckTimeStr + "考核汇总";
rowIndex = 2;
wSheet.Cells[rowIndex, 1] = "排名";
wSheet.Cells[rowIndex, 2] = "名称";
wSheet.Cells[rowIndex, 3] = "总分" + Environment.NewLine + "（" + (rule.TempComplete + rule.TempExact + rule.InsectComplete + rule.InsectExact + rule.StoreHouseInfo + rule.QualityReport + rule.RotationPlan + rule.LoginCheck) + "分）";
wSheet.Cells[rowIndex, 4] = "上传" + Environment.NewLine + "数据完整性" + Environment.NewLine + "（" + rule.TempComplete + "分）";
wSheet.Cells[rowIndex, 5] = "上传" + Environment.NewLine + "数据准确性" + Environment.NewLine + "（" + rule.TempExact + "分）";
wSheet.Cells[rowIndex, 6] = "上传" + Environment.NewLine + "数据完整性" + Environment.NewLine + "（" + rule.InsectComplete + "分）";
wSheet.Cells[rowIndex, 7] = "上传" + Environment.NewLine + "数据准确性" + Environment.NewLine + "（" + rule.InsectExact + "分）";
wSheet.Cells[rowIndex, 8] = "仓" + Environment.NewLine + "信息" + Environment.NewLine + "（" + rule.StoreHouseInfo + "分）";
wSheet.Cells[rowIndex, 9] = "检查" + Environment.NewLine + "报告" + Environment.NewLine + "（" + rule.QualityReport + "分）";
wSheet.Cells[rowIndex, 10] = "进出" + Environment.NewLine + "计划" + Environment.NewLine + "（" + rule.RotationPlan + "分）";
wSheet.Cells[rowIndex, 11] = "自检" + Environment.NewLine + "登录" + Environment.NewLine + "（" + rule.LoginCheck + "分）";
var details = ds.Tables[1].AsEnumerable().Where(a => a["CompanyID"].ToString() == cid);
foreach (DataRow detail in details)
{
rowIndex++;
wSheet.Cells[rowIndex, 1] = detail["Rank"].ToString();
wSheet.Cells[rowIndex, 2] = detail["GranaryName"].ToString().Trim();
wSheet.Cells[rowIndex, 3] = detail["TotalScore"].ToString();
wSheet.Cells[rowIndex, 4] = detail["TempComplete"].ToString();
wSheet.Cells[rowIndex, 5] = detail["TempExact"].ToString();
wSheet.Cells[rowIndex, 6] = detail["InsectComplete"].ToString();
wSheet.Cells[rowIndex, 7] = detail["InsectExact"].ToString();
wSheet.Cells[rowIndex, 8] = detail["StoreHouseInfo"].ToString();
wSheet.Cells[rowIndex, 9] = detail["QualityReport"].ToString();
wSheet.Cells[rowIndex, 10] = detail["RotationPlan"].ToString();
wSheet.Cells[rowIndex, 11] = detail["LoginCheck"].ToString();
}
Microsoft.Office.Interop.Excel.Range rg2 = wSheet.Range["A3:A" + rowIndex.ToString()];
rg2.HorizontalAlignment = XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range rgAll = wSheet.Range["A1:K" + rowIndex.ToString()];
rgAll.EntireColumn.AutoFit(); //自动调整列宽
wSheet.Range["c1:c" + rowIndex.ToString()].ColumnWidth = 11;
wSheet.Range["d1:d" + rowIndex.ToString()].ColumnWidth = 12;
wSheet.Range["e1:e" + rowIndex.ToString()].ColumnWidth = 12;
wSheet.Range["f1:f" + rowIndex.ToString()].ColumnWidth = 12;
wSheet.Range["g1:g" + rowIndex.ToString()].ColumnWidth = 12;
wSheet.Range["h1:h" + rowIndex.ToString()].ColumnWidth = 9.75;
wSheet.Range["i1:i" + rowIndex.ToString()].ColumnWidth = 9.75;
wSheet.Range["j1:j" + rowIndex.ToString()].ColumnWidth = 9.75;
wSheet.Range["k1:k" + rowIndex.ToString()].ColumnWidth = 9.75;
rgColumnTitle.WrapText = true;
//rgAll.Borders.LineStyle = ;
rgAll.Borders.Weight = 2;
wSheet.Range["A1:K1"].Borders.Weight = 3;
wSheet.Range["A1:A" + rowIndex.ToString()].Borders[XlBordersIndex.xlEdgeLeft].Weight = 3;
wSheet.Range["A" + rowIndex.ToString() + ":K" + rowIndex.ToString()].Borders[XlBordersIndex.xlEdgeBottom].Weight = 3;
wSheet.Range["K1:K" + rowIndex.ToString()].Borders[XlBordersIndex.xlEdgeRight].Weight = 3;
}
foreach (Worksheet ws in wBook.Sheets)
{
ws.Name = sheetNames[ws.Name];
}
//保存工作簿
//wBook.Save();
wBook.SaveAs(fullFilePath, XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);// Microsoft Excel 97- Excel 2003 & 5.0/95 工作簿(.*.xls)
//保存excel文件
//app.Save(fullFilePath);
//app.Save();
//app.ActiveWorkbook.SaveAs(fullFilePath, XlFileFormat.xlExcel7, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//app.SaveWorkspace(fullFilePath);
}
catch (Exception ex)
{
Common.MessageBox.Show(this, "导出过程异常。" + ex.Message);
}
finally
{
if (wBook != null)
{
wBook.Close(true, fullFilePath, null);
wBook = null;
}
if (app != null)
{
app.Quit();
app = null;
}
System.IO.FileInfo fi = new System.IO.FileInfo(fullFilePath);
if (fi.Exists)
{
ExportExcelToWebClient(fi, filename + ".xls");
fi.Delete();
GC.Collect();
GC.Collect();
HttpContext.Current.Response.End();
}
}
}
else
{
Common.MessageBox.Show(this, "日期格式不正确");
}
}
public static void ExportExcelToWebClient(System.IO.FileInfo fi, string newFileName)
{
//清除Response缓存内容
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower();
if (UserAgent.IndexOf("firefox") == -1)
newFileName = HttpUtility.UrlEncode(newFileName, System.Text.Encoding.UTF8);
//确定字符的编码格式
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + newFileName);
HttpContext.Current.Response.ContentType = "application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.WriteFile(fi.FullName);
HttpContext.Current.Response.Flush();
}
