在ASP.NET中,经常会遇到数据库中数据导出为Excel表格的问题。以下代码为页面“导出”按钮的后台响应事件,以后类似事件可以如此写。
注意:其中副标题“时间”的计算,本算法通过将数据库中year和month合并为一个整型数(1~9月份要改为01~09),然后记录最大值和最小值。最后最小值为起始年月,最大值为终止年月。
protected void btnExport_Click(object sender, EventArgs e)
{
string cChangming = ddlDepartment.SelectedItem.Value.ToString();
string cYear = ddlYearlist.SelectedItem.Value.ToString();
string cMonth = ddlMonthlist.SelectedItem.Value.ToString();
object missing = Missing.Value;
DateTime beforeTime;
DateTime afterTime;
string cWherestr = " 1=1 ";
if (cChangming != "全部")
{
cWherestr = cWherestr + " and changmingid = '" + cChangming + "'";
}
if (cYear != "全部")
{
cWherestr = cWherestr + " and year = " + cYear;
}
if (cMonth != "全部")
{
cWherestr = cWherestr + " and month = " + cMonth;
}
DataSet cds = DataAccess.GetDataList_Export(cWherestr);//获取数据
int rowCount = cds.Tables[0].Rows.Count; //源DataTable行数
int colCount = cds.Tables[0].Columns.Count; //源DataTable列数
beforeTime = DateTime.Now;
Excel.Application cexcelapp = new Excel.Application();
cexcelapp.Visible = false;
afterTime = DateTime.Now;
String templetFile = System.Configuration.ConfigurationSettings.AppSettings["TempletFilePath"]+ "test.xls";
Excel.Workbook workBook = cexcelapp.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
sheet.Name = "XXXX月报";
/*表头添加时间段.****************************Start*********************************
*Add by woo, 20101128
*将年月合并为一个整型数,进而判断最大日期和最小日期.
*注意:对于1~9月在合并为整数时,需要变为01~09。Modified by woo,20110101*/
int minDate = 205012;
int maxDate = 190001;
for (int k = 0; k < rowCount; k++)
{
string strYear = cds.Tables[0].Rows[k][0].ToString();
string strMonth = cds.Tables[0].Rows[k][1].ToString();
if (1 == strMonth.Trim().Length)
{
strMonth = "0" + strMonth;
}
string temp;
temp = strYear + strMonth;
if (int.Parse(temp) < minDate)
{
minDate = int.Parse(temp);
}
if (int.Parse(temp) > maxDate)
{
maxDate = int.Parse(temp);
}
}
string timeLab = "";
if (minDate != maxDate)
{
//截取minDate和maxDate,前四位为年,后两位为月
timeLab = minDate.ToString().Substring(0, 4) + "年" + minDate.ToString().Substring(4, 2) + "月---";
timeLab += maxDate.ToString().Substring(0, 4) + "年" + maxDate.ToString().Substring(4, 2) + "月";
}
else
{
timeLab = minDate.ToString().Substring(0, 4) + "年" + minDate.ToString().Substring(4, 2) + "月";
}
sheet.Cells[2, 1] = "时间:" + timeLab;
/*****************************************End*****************************************/
int startRow = 7;
int startColumn = 1;
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
string cstr = cds.Tables[0].Rows[i][j].ToString();
//sheet.Cells[startRow + i, startColumn +j] = cstr;
if (cds.Tables[0].Rows[i][j].GetType() == System.Type.GetType("System.Double"))
{
sheet.Cells[startRow + i, startColumn + j] = Convert.ToDouble(cstr);
}
else if (cds.Tables[0].Rows[i][j].GetType() == System.Type.GetType("System.Int32"))
{
sheet.Cells[startRow + i, startColumn + j] = Convert.ToInt32(cstr);
}
else
{
sheet.Cells[startRow + i, startColumn + j] = cstr;
}
}
}
//Borders.LineStyle 单元格边框线
Excel.Range excelRange = sheet.get_Range(sheet.Cells[7, 1], sheet.Cells[rowCount+6, 32]);
//单元格边框线类型(线型,虚线型)
excelRange.Borders.LineStyle = 1;
string newid = Guid.NewGuid().ToString();
string outputFile = System.Configuration.ConfigurationSettings.AppSettings["SaveFilePath"] + newid + ".xls";
//输出Excel文件并退出
try
{
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
workBook.Close(null, null, null);
cexcelapp.Workbooks.Close();
cexcelapp.Application.Quit();
cexcelapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(cexcelapp);
workBook = null;
cexcelapp = null;
GC.Collect();
}
catch(Exception e1)
{
throw e1;
}
finally
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel进程ID,暂时只能判断进程启动时间
foreach (Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if (startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
FileInfo DownloadFile = new FileInfo(outputFile);
Response.Clear();
Response.ClearHeaders();
Response.Buffer = false;
Response.ContentType = "application/octet-stream";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("XXXX月报.xls", System.Text.Encoding.UTF8));
Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
Response.WriteFile(outputFile);
Response.Flush();
System.IO.File.Delete(outputFile); //在服务器端删除导出的Excel文件
Response.End();
}