C# 导出Excel 多个Sheet

以下代码中最关键的代码是

                    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 (可以到网上搜索或以下地址下载:https://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;
                }
            }
        }

  源码下载地址:http://download.csdn.net/detail/jn40105/7124495

posted on 2014-03-29 19:18  冬夜冷雨  阅读(4472)  评论(0编辑  收藏  举报

导航