Chinaunix首页 | 论坛 | 博客
  • 博客访问: 12300853
  • 博文数量: 1293
  • 博客积分: 13501
  • 博客等级: 上将
  • 技术积分: 17974
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-08 18:11
文章分类

全部博文(1293)

文章存档

2019年(1)

2018年(1)

2016年(118)

2015年(257)

2014年(128)

2013年(222)

2012年(229)

2011年(337)

分类: 嵌入式

2012-10-12 20:13:28

这样的代码,单纯看注释,就会烦死人!!!!!


  1. #region 导出EXCEL方法二
  2.        ///
  3.        /// 此方法关键之处是使用Range一次存储内存中的多行多列数据到Excel
  4.        /// 此方法效率明显高的多,推荐使用
  5.        /// 此方法个人觉得很精彩,大家在看代码的时候可以使用设置断点来看每个变量值的变化
  6.        ///
  7.        ///
  8.        ///
  9.        public void SaveToExcel(DataGridView gridView, SaveFileDialog saveFileDialog)
  10.        {
  11.            /* 文件保存对话框 */
  12.            saveFileDialog.Filter = InfoListUs[36]; // "Execl files (*.xls)|*.xls";
  13.            saveFileDialog.FilterIndex = 0;
  14.            saveFileDialog.CreatePrompt = true;

  15.            saveFileDialog.Title = InfoListUs[37]; // "导出文件保存路径";
  16.            saveFileDialog1.RestoreDirectory = true; //控制对话框在关闭之前是否恢复当前目录

  17.            if (saveFileDialog.ShowDialog() == DialogResult.OK)
  18.            {
  19.                string strName = saveFileDialog.FileName; //从对话框中取得文件名
  20.                /* 文件名不能为空 */
  21.                if (strName.Length != 0)
  22.                {
  23.                    System.Reflection.Missing miss = System.Reflection.Missing.Value; //这是什么玩意的?
  24.                    /* 创建EXCEL对象: appExcel,Workbook,Worksheet,Range */
  25.                    Microsoft.Office.Interop.Excel.Application appExcel;
  26.                    appExcel = new Microsoft.Office.Interop.Excel.Application();

  27.                    Microsoft.Office.Interop.Excel.Workbook workbookData;
  28.                    Microsoft.Office.Interop.Excel.Worksheet worksheetData;
  29.                    Microsoft.Office.Interop.Excel.Range rangedata;
  30.                    appExcel.Visible = false; //设置对象不可见

  31.                    // Microsoft.Office.Interop.Excel.Range Range = (Microsoft.Office.Interop.Excel.Range)NewExcel.get_Range("A1", "H2");//获取多个单元格区域

  32.                    /*
  33.                      * 在调用Excel应用程序,或创建Excel工作簿之前,记着加上下面的两行代码。
  34.                      * 因为Excel有一个Bug,如果你的操作系统的环境不是英文的,而Excel就会在执行下面的代码时,报异常。
  35.                    */
  36.                    System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
  37.                    System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
  38.                    workbookData = appExcel.Workbooks.Add(miss);
  39.                    worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.Add(miss, miss, miss, miss);
  40.                    worksheetData.Name = "saved"; //给工作表赋名称
  41.                    // 清零计数器并开始计数
  42.                    TimeP = new System.DateTime(0);
  43.                    timer1.Start();

  44.                    /*
  45.                     * 保存到WorkSheet的表头,你应该看到,是一个Cell一个Cell的存储,
  46.                     * 这样效率特别低,解决的办法是,使用Rang,一块一块地存储到Excel
  47.                     */
  48.                    int ColumnCellIndex = 0;
  49.                    for (int i = 0; i < gridView.ColumnCount; i++)
  50.                    {
  51.                        if ((i== 4) || (i == 8) || (i == 10) || (i == 11))
  52.                            continue;
  53.                        worksheetData.Cells[1, ColumnCellIndex + 1] = gridView.Columns[i].HeaderText.ToString();
  54.                        ColumnCellIndex++;
  55.                    }

  56.                    //
  57.                    // 先给Range对象一个范围为A2开始,Range对象可以给一个CELL的范围;也可以给例如A1到H10这样的范围
  58.                    // 因为第一行已经写了表头,所以所有数据都应该从A2开始
  59.                    //
  60.                    rangedata = worksheetData.get_Range("A2", miss);
  61.                    Microsoft.Office.Interop.Excel.Range xlRang = null;

  62.                    int iRowCount = gridView.RowCount; //iRowCount为实际行数,最大行
  63.                    int iParstedRow = 0, iCurrSize = 0;

  64.                    //
  65.                    // iEachSize为每次写行的数值,可以自己设置,
  66.                    // 每次写1000行和每次写2000行大家可以自己测试下效率
  67.                    //
  68.                    int iEachSize = 1000;

  69.                    int iColumnAccount = gridView.ColumnCount; //iColumnAccount为实际列数,最大列数

  70.                    //
  71.                    // 在内存中声明一个iEachSize×iColumnAccount的数组,
  72.                    // iEachSize是每次最大存储的行数,iColumnAccount就是存储的实际列数
  73.                    //
  74.                    object[,] objVal = new object[iEachSize, iColumnAccount];
  75.                    try
  76.                    {
  77.                        //
  78.                        // 给进度条赋最大值为实际行数最大值
  79.                        // progressBar1.Maximum = gridView.RowCount;
  80.                        //
  81.                        iCurrSize = iEachSize;
  82.                        int ColumnCellIndex_1 = 0;

  83.                        while (iParstedRow < iRowCount)
  84.                        {
  85.                            if ((iRowCount - iParstedRow) < iEachSize)
  86.                                iCurrSize = iRowCount - iParstedRow;
  87.                            for (int i = 0; i < iCurrSize; i++)
  88.                            {
  89.                                ColumnCellIndex_1 = 0;

  90.                                for (int j = 0; j < iColumnAccount; j++)
  91.                                {
  92.                                    /* 下面四个行的数据不需要导出 */
  93.                                    if ((j == 4) || (j == 8) || (j == 10) || (j == 11))
  94.                                        continue;
  95.                                    objVal[i, ColumnCellIndex_1] = gridView[j, i + iParstedRow].Value.ToString();
  96.                                    ColumnCellIndex_1++;
  97.                                }
  98.                                System.Windows.Forms.Application.DoEvents();
  99.                            }
  100.                            /*
  101.                             * 建议使用设置断点研究下哈
  102.                             * 例如A1到H10的意思是从A到H,第一行到第十行
  103.                             * 下句很关键,要保证获取orkSheet中对应的Range范围
  104.                             * 下句实际上是得到这样的一个代码语句 xlRang = worksheetData.get_Range("A2","H100");
  105.                             * 注意看实现的过程
  106.                             * 'A' + iColumnAccount - 1这儿是获取你的最后列,A的数字码为65,大家可以仔细看下是不是得到最后列的字母
  107.                             * iParstedRow + iCurrSize + 1获取最后行
  108.                             * 若WHILE第一次循环的话这应该是A2,最后列字母+最后行数字
  109.                             * iParstedRow + 2要注意,每次循环这个值不一样,他取决于你每次循环RANGE取了多大,也就是iEachSize设置值的大小
  110.                             */
  111.                            xlRang = worksheetData.get_Range("A" + ((int)(iParstedRow + 2)).ToString(), ((char)('A' + iColumnAccount - 1)).ToString() + ((int)(iParstedRow + iCurrSize + 1)).ToString());

  112.                            xlRang.Value2 = objVal; // 调用Range的Value2属性,把内存中的值赋给Excel
  113.                            iParstedRow = iParstedRow + iCurrSize;
  114.                        }

  115.                        /* 设置A1-H1行头字体的大小 */
  116.                        rangedata = (Microsoft.Office.Interop.Excel.Range)appExcel.get_Range("A1", "H1");
  117.                        rangedata.Font.Size = 20;
  118.                        rangedata.Font.Bold = true;

  119.                        /* 保存工作表 */
  120.                        worksheetData.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
  121.                        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
  122.                        xlRang = null;
  123.                        this.KillSpecialExcel(appExcel);

  124.                        timer1.Stop();
  125.                        /* 数据已经成功导出到 */
  126.                        MessageBox.Show(MessListUs[18] + saveFileDialog.FileName.ToString(), MessListUs[19], MessageBoxButtons.OK, MessageBoxIcon.Information);

  127.                    }
  128.                    catch (Exception ex)
  129.                    {
  130.                        timer1.Stop();
  131.                        return;
  132.                    }

  133.                    /* 别忘了在结束程序之前恢复环境! */
  134.                    System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;
  135.                }
  136.            }

  137.        }

阅读(2262) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~