首页 > 代码库 > 导出excel
导出excel
public bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
{
if (File.Exists(fileName))
{
File.Delete(fileName);
}
DataTable dataTable = dataSet.Tables[0];
int rowNumber = dataTable.Rows.Count;//不包括字段名
int columnNumber = dataTable.Columns.Count;
//if (rowNumber == 0)
//{
// MessageBox.Show("没有任何数据可以导入到Excel文件!");
// return false;
//}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBook = excel.Application.Workbooks.Add(true);
//Microsoft.Office.Interop.Excel.Range excelR = null;
//Microsoft.Office.Interop.Excel.Worksheet excelWs = null;
excel.Visible = false;//是否打开该Excel文件
//excelWs = (Microsoft.Office.Interop.Excel.Worksheet)(workBook.Sheets.get_Item(1));//选择第一张表
////向Excel中添加列名
// for (int i = 0; i < dataTable.Columns.Count; i++)
// {
// string rangeName = ((Char)(i + 65)).ToString() + "1"; //计算出单元格的位置(例:第一行第二列在Excel中为B1,即为值为1+65的字符+"1"。)
// excelR = excelWs.get_Range(rangeName);
// excelR.Value = http://www.mamicode.com/dataTable.Columns[i].ColumnName; //在对应单元格中写入值
// }
string[] Colum_Name = { "FBCPZ", "FBCPHY", "JSCPZ", "JSCPHY", "ZHXGR", "ZHXGSJ", " XH" };
for (int i = 0; i < columnNumber; i++)
{
excel.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
}
//填充数据
for (int i = 0; i < rowNumber; i++)
{
for (int j = 0; j < columnNumber; j++)
{
excel.Cells[i + 2, j + 1] = dataTable.Rows[i].ItemArray[j];
}
}
//string fileName = path + "\\" + DateTime.Now.ToString().Replace(‘:‘, ‘_‘) + ".xls";
workBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlAddIn, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
try
{
workBook.Saved = true;
excel.UserControl = false;
//excelapp.Quit();
}
//catch (Exception exception)
//{
// MessageBox.Show(exception.Message);
//}
finally
{
workBook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
excel.Quit();
}
if (isShowExcle)
{
System.Diagnostics.Process.Start(fileName);
}
//向客户端发送文件...
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment;filename=excel.xls"); //设置回发内容为Excel
Response.ContentType = "application/ms-excel";
Response.WriteFile(fileName); //把刚刚生成的Excel文件写入Http流
Response.End();
return true;
}
导出excel