62,074
社区成员
发帖
与我相关
我的任务
分享
{
//dv为要输出到Excel的数据
Excel.Application excel;// = new Application();
int rowIndex = 1;
int colIndex = 1;
Excel._Workbook xBk;
Excel._Worksheet xSt;
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
xSt.Name = "Summary";
//取得列标题
foreach (DataColumn col in dv.Table.Columns)
{//好像在这个循环里抛出的异常
excel.Cells[rowIndex, colIndex] = col.ColumnName;
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Name = "Arial";
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Size = "8";
colIndex++;
}
//取得表格中的数据
foreach (DataRowView row in dv)
{
colIndex = 1;
rowIndex++;
foreach (DataColumn col in dv.Table.Columns)
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;//设置字符型的字段格式为左对齐
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Name = "Arial";
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Size = "8";
colIndex++;
}
}
int rowSum = rowIndex;
colIndex--;
//设置报表表格为最适应宽度
xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//冻结
excel.ActiveWindow.SplitColumn = 1;
excel.ActiveWindow.SplitRow = 1;
excel.ActiveWindow.Split = true;
excel.ActiveWindow.FreezePanes = true;
//显示效果
HttpContext curContext = HttpContext.Current;
xBk.SaveCopyAs(curContext.Server.MapPath(".") + "\\" + fileName + ".xls");
//ds = null;
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
string path = curContext.Server.MapPath(fileName + ".xls");
FileInfo file = new FileInfo(path);
curContext.Response.Clear();
curContext.Response.Charset = "GB2312";
curContext.Response.ContentEncoding = Encoding.UTF8;
// 添加头信息,为"文件下载/另存为"对话框指定默认文件名
curContext.Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
// 添加头信息,指定文件大小,让浏览器能够显示下载进度
curContext.Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流,必须被下载
curContext.Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
curContext.Response.WriteFile(file.FullName);
curContext.Response.Flush();
File.Delete(file.FullName);
}
catch (Exception ex)
{
Loger.writeExceptionLog(ex.Message);
throw ex;
}
mySheet.get_Range(mySheet.Cells[beginRow, 1], mySheet.Cells[(beginRow + row-1), content.Length]).Value2 = dataArray;