• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

C#导出Excel多个Sheet

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

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

                    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


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
C#练习题利用条件运算符的嵌套来完成分数等级划分发布时间:2022-07-13
下一篇:
浅析C#中的Attribute发布时间:2022-07-13
热门推荐
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap