以下代码中最关键的代码是
Worksheet mSheet = (Microsoft.Office.Interop.Excel.Worksheet)mBook.Worksheets.Add(miss, miss, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
网上很资料中都没有提到,我也是试了很久才搞出来的,上面这一行是新建了2个Sheet,想建多个就修改Add的第3个参数,数量为参数值+1
xlapp.Visible = false; //是否显示导出过程 ,建议关闭,否则在导出过程中鼠标点击Excel文件时会出错。
引用:COM --》 Microsoft Office xx.x Object Library 再引用 Microsoft.Office.Interop.Excel (可以到网上搜索或以下地址下载:http://files.cnblogs.com/fan0136/Microsoft.Office.Interop.Excel.rar)
完整的代码台下,实际使用中数据表参数可以修改成List<object>:
public class ToExcel
{
/// <summary>
/// 可以自定义导出Excel的格式,传的参数为GridView
/// </summary>
/// <param name="gridView"></param>
/// <param name="filename"></param>
public static void ExportGridViewToExcel(DevExpress.XtraGrid.Views.Grid.GridView gridView, DevExpress.XtraGrid.Views.Grid.GridView gridView1, string filename)
{
//System.Data.DataTable dt = (System.Data.DataTable)gridView.DataSource;
SaveFileDialog sfd = new SaveFileDialog();
filename += DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString();
sfd.FileName = filename;
sfd.Filter = "Excel files (*xls) | *.xls";
sfd.RestoreDirectory = true;
if (sfd.ShowDialog() == DialogResult.OK && sfd.FileName.Trim() != null)
{
int rowIndex = 1;
int rowIndex1 = 1;
int colIndex = 0;
int colNum = gridView.Columns.Count;
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
xlapp.Visible = true;
Microsoft.Office.Interop.Excel.Workbooks mBooks = (Microsoft.Office.Interop.Excel.Workbooks)xlapp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook mBook = (Microsoft.Office.Interop.Excel.Workbook)mBooks.Add(miss);
Worksheet mSheet = (Microsoft.Office.Interop.Excel.Worksheet)mBook.Worksheets.Add(miss, miss, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Worksheet sm = mBook.Worksheets[1];
sm.Name = "q";
Worksheet ws = mBook.Worksheets[2];
ws.Name = "B";
//设置对齐方式
mSheet.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//设置文字自动换行
//mSheet.Cells.WrapText = true;
//设置第一行高度,即标题栏
((Microsoft.Office.Interop.Excel.Range)mSheet.Rows["1:1", System.Type.Missing]).RowHeight = 20;
//设置数据行行高度
((Microsoft.Office.Interop.Excel.Range)mSheet.Rows["2:" + gridView.RowCount + 1, System.Type.Missing]).RowHeight = 16;
//设置字体大小(10号字体)
mSheet.Range[mSheet.Cells[1, 1], mSheet.Cells[gridView.RowCount + 1, gridView.Columns.Count]].Font.Size = 10;
//设置单元格边框
Microsoft.Office.Interop.Excel.Range range1 = mSheet.Range[mSheet.Cells[1, 1], mSheet.Cells[gridView.RowCount + 1, gridView.Columns.Count]];
range1.Borders.LineStyle = 1;
//写标题
for (int row = 1; row <= gridView.Columns.Count; row++)
{
sm.Cells[1, row] = gridView.Columns[row - 1].GetTextCaption();
} //写标题
for (int row = 1; row <= gridView1.Columns.Count; row++)
{
ws.Cells[1, row] = gridView1.Columns[row - 1].GetTextCaption();
}
try
{
for (int i = 0; i < gridView.RowCount; i++)
{
rowIndex++;
colIndex = 0;
for (int j = 0; j < gridView.Columns.Count; j++)
{
colIndex++;
sm.Cells[rowIndex, colIndex] = gridView.GetRowCellValue(i, gridView.Columns[j]);
}
}
for (int i = 0; i < gridView1.RowCount; i++)
{
rowIndex1++;
colIndex = 0;
for (int j = 0; j < gridView1.Columns.Count; j++)
{
colIndex++;
ws.Cells[rowIndex1, colIndex] = gridView1.GetRowCellValue(i, gridView1.Columns[j]);
}
}
mBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
miss, miss, miss, miss, miss);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
mBooks.Close();
xlapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp);
GC.Collect();
}
}
else
{
//return false;
}
}
}