导出Excel 多个sheet
using Excel = Microsoft.Office.Interop.Excel; //"Microsoft Excel 9.0 Object Library"（Office 2000）
public DataTable CreatTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("EventID", System.Type.GetType("System.String"));
dt.Columns.Add("ItemName", System.Type.GetType("System.String"));
dt.Columns.Add("EventName", System.Type.GetType("System.String"));
DataRow dr1 = dt.NewRow();
dr1[0] = "1";
dr1[1] = "sss";
dr1[2] = "sdfs";
DataRow dr2 = dt.NewRow();
dr2[0] = "2";
dr2[1] = "sede";
dr2[2] = "lj";
DataRow dr3 = dt.NewRow();
dr3[0] = "3";
dr3[1] = "sfd";
dr3[2] = "dlo";
dt.Rows.Add(dr1);
dt.Rows.Add(dr2);
dt.Rows.Add(dr3);
return dt;
}
public void test3()
{
DataTable tempDataTable = CreatTable();
Excel.Application m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = false;
m_xlApp.Visible = false;
Excel.Workbooks workbooks = m_xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
try
{
for (int sc = 1; sc < 4; sc++)
{
if (sc > 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);
}
else
{
worksheet = (Excel.Worksheet)workbook.Worksheets[sc];
}
worksheet.Name = tempDataTable.Columns[sc - 1].ColumnName;
string[,] datas = new string[4, 3];
for (int i = 0; i < 3; i++)
{
datas[0, i] = tempDataTable.Columns[i].Caption;
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]);
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
range.Font.Size = 9;
for (int r = 0; r < 3; r++)
{
for (int k = 0; k < 3; k++)
{
object obj = tempDataTable.Rows[r][tempDataTable.Columns[k].ToString()];
datas[r + 1, k] = obj == null ? "" : "'" + obj.ToString().Trim();
}
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[4, 3]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
fchR.Font.Size = 9;
fchR.RowHeight = 14.25;
fchR.Borders.LineStyle = 1;
fchR.HorizontalAlignment = 1;
}
workbook.Saved = true;
string filepath = Server.MapPath(DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls");
object ob = System.Reflection.Missing.Value;
workbook.SaveAs(filepath, Excel.XlFileFormat.xlExcel7, ob, ob, ob, ob, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, ob, ob, ob, ob, ob);
m_xlApp.Quit();
GC.Collect();//强行销毁
System.Diagnostics.Process.Start(filepath); //打开EXCEL
}
catch (Exception ex)
{
}
}
public void test2()
{
DataTable dt2 = CreatTable();
DataTable dt = CreatTable();
Excel.Application excel1;
Excel._Workbook xworkbook;
Excel._Worksheet xworksheek;
excel1 = new Excel.ApplicationClass();
xworkbook = excel1.Workbooks.Add(true);
xworksheek = (Excel._Worksheet)excel1.ActiveSheet;
xworksheek.Name = "testone";
int i = 0;
foreach (DataColumn col in dt.Columns)
{
i++;
excel1.Cells[1, i] = col.ColumnName;
xworksheek.get_Range(excel1.Cells[1, i], excel1.Cells[1, i]).Font.Bold = true;
xworksheek.get_Range(excel1.Cells[1, i], excel1.Cells[1, i]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xworksheek.get_Range(excel1.Cells[1, i], excel1.Cells[1, i]).Select();
xworksheek.get_Range(excel1.Cells[1, i], excel1.Cells[1, i]).Interior.ColorIndex = 3;
xworksheek.get_Range(excel1.Cells[1, i], excel1.Cells[1, i]).Columns.AutoFit();
}
int j = 1;
foreach (DataRow dr in dt.Rows)
{
j++;
for (int k = 0; k < i; k++)
{
excel1.Cells[j, k + 1] = dr[k].ToString();
xworksheek.get_Range(excel1.Cells[j, k + 1], excel1.Cells[j, k + 1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xworksheek.get_Range(excel1.Cells[j, k + 1], excel1.Cells[j, k + 1]).Select();
}
}
foreach (DataColumn col in dt2.Columns)
{
i = 0;
Excel._Worksheet wks;
wks = (Excel._Worksheet)excel1.ActiveSheet;
wks.Name = col.ColumnName;
foreach (DataColumn col2 in dt2.Columns)
{
i++;
excel1.Cells[1, i] = col2.ColumnName;
wks.get_Range(excel1.Cells[1, i], excel1.Cells[1, i]).Font.Bold = true;
wks.get_Range(excel1.Cells[1, i], excel1.Cells[1, i]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
wks.get_Range(excel1.Cells[1, i], excel1.Cells[1, i]).Select();
wks.get_Range(excel1.Cells[1, i], excel1.Cells[1, i]).Interior.ColorIndex = 3;
wks.get_Range(excel1.Cells[1, i], excel1.Cells[1, i]).Columns.AutoFit();
}
}
excel1.Cells[5, 1] = "标题";
xworksheek.get_Range(excel1.Cells[5, 1], excel1.Cells[5, 3]).Select();
//string FilePath = ;
string filepath = Server.MapPath(DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls");
excel1.ActiveWorkbook.SaveAs(filepath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
xworkbook.Close(null, null, null);
excel1.Workbooks.Close();
excel1.Quit();
//注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
if (xworksheek != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xworksheek);
xworksheek = null;
}
if (xworkbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xworkbook);
xworkbook = null;
}
if (excel1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
excel1 = null;
}
GC.Collect();
}
