/// <summary>
/// 导出Excel 把一个dataset的多个datatable导入到一个excel的多个sheet中
/// </summary>
/// <param name="ds">DataSet</param>
/// <param name="tableNames">ds里每个表的表名</param>
/// <param name="strExcelFileName">导出Excel名称(YYYY-MM-DD.xls)</param>
public void doExport(DataSet ds, string[] tableNames, string strExcelFileName)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
try
{
excel.Visible = false;
//设置禁止弹出保存和覆盖的询问提示框
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
//增加一个工作簿
Workbook book = excel.Workbooks.Add(true);
//添加工作表
Worksheet sheets = (Microsoft.Office.Interop.Excel.Worksheet)
book.Worksheets.Add(Missing.Value, Missing.Value, 19, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);for (int i = 0; i < ds.Tables.Count; i++)
{
System.Data.DataTable table = ds.Tables[i];
//获取一个工作表
Worksheet sheet = book.Worksheets[i + 1] as Worksheet;
int rowIndex = 1;
int colIndex = 0;foreach (DataColumn col in table.Columns)
{
colIndex++;
sheet.Cells[1, colIndex] = col.ColumnName;
}
foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
//在这里要在数字前加前单引号
String typeName = row[col.ColumnName].GetType().ToString();
sheet.Cells[rowIndex, colIndex] = typeCheckAdd(row[col.ColumnName].ToString(), typeName);
}
}
sheet.Name = tableNames[i];
}//删除多余Sheet
for (int g = 1; g <= book.Worksheets.Count; g++)
{
Worksheet sheet = book.Worksheets[g] as Worksheet;
if (sheet.Name.Substring(0, 5) == "Sheet")
{
sheet.Delete();
g--;
}
}
book.Save();
book.SaveAs(strExcelFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);excel.Quit();
excel = null;
GC.Collect();SaveXls(strExcelFileName);//保存到选定路径
}
catch(Exception e)
{
Response.Write(e.Message);
}
}
#region 若是大数需加前导引号变成字符串
public String typeCheckAdd(String cellContent, String strType)
{
String cellContentAdd;
switch (strType)
{
case "System.String":
cellContentAdd = "'" + cellContent;
break;
default:
cellContentAdd = cellContent;
break;
}
return cellContentAdd;
}
#endregion
#region 保存已生成Excel到选定路径
protected void SaveXls(string fileURL)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileURL);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode(fileInfo.Name.ToString()));
Response.AddHeader("content-length", fileInfo.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.WriteFile(fileURL);
}
#endregion
=======================================================================================================================
导出Excel 把一个dataset的多个datatable导入到一个excel的多个sheet中
最新推荐文章于 2022-07-21 08:32:17 发布