C#操作excel(多种方法比较)

最新推荐文章于 2024-07-20 16:40:31 发布

SDMrFeng

最新推荐文章于 2024-07-20 16:40:31 发布

阅读量2.3k

点赞数

分类专栏： C# 文章标签： excel C#

C# 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

我们在做excel资料的时候，通常有以下方法。

一.导入导出excel常用方法：

1.用查询表的方式查询并show在数据集控件上。

    public static string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =C:\\08.xls;Extended Properties=Excel 8.0";
    public static DataSet ds;
    protected void Page_Load(object sender, EventArgs e)
    {
        
        OleDbConnection conn = new OleDbConnection(strCon);
        string sql = "select * from [Sheet1$]";
        conn.Open();
        OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, strCon);
        ds = new DataSet();
        myCommand.Fill(ds, "[Sheet1$]");
        conn.Close();
        datagrid1.DataMember = "[Sheet1$]";
        datagrid1.DataSource = ds;
        datagrid1.DataBind();


        //Excel.Application excel = new Excel.Application();
        //excel.Application.Workbooks.Add(true);
        //excel.Visible = true;
    }

2.一个一个单元格的进行插入

        string str = @"Data Source=IT-428E4EA4B0C7\SQLEXPRESS;Initial Catalog=TestBase;Integrated Security=True";
        SqlConnection conn = new SqlConnection(str);
        conn.Open();
        int n = 0;
        for (int i = 1; i < 20; i++)
        {
            if (n > 3)
                break;
            else
                if (msheet.Cells.get_Range("A" + i, Type.Missing).Text.ToString() == "" && n <= 3)
                { n++; }
                else
                {
		     //循环获取excel单元格的值一次一次的插入，excuteSql为执行的存储过程
                     excuteSql(msheet.Cells.get_Range("B" + i, Type.Missing).Text.ToString(),
                     	msheet.Cells.get_Range("B" + (i + 1), Type.Missing).Text.ToString(),
                     	msheet.Cells.get_Range("B" + (i + 2), Type.Missing).Text.ToString(),
                        conn);
                     i = i + 3;

                }
        }

        conn.Close();

二快速导入导出

1.我们都知道当向db里批量插入数据的时候我们会选择SqlBulkCopy

if (dataTable!=null && dataTable.Rows.Count!=0)
{
sqlBulkCopy.WriteToServer(dataTable);
}

这个可以看深山老林新发的一篇SQLServer中批量插入数据方式的性能对比下面是SqlBulkCopy的方法，这个方法有一个弊端就是当excel某一列即有文字，还有日期的时候，会出现null值，我在网上查了一些资料说连接字串加上;HDR=YES;IMEX=1'的时候会都当做字符处理，但是还是会出现一些bug，所以建议最好先把excel数据分析到datatable里然后再用SqlBulkCopy倒入数据库

	// block copy to DB from Excel
        //By xijun, 
        //step 1 create an excel file  C:\Inetpub\wwwroot\test.xls , fill cell(1,1) with "Data",cell(1,2) with "name"
        //step 2 create table named "Data" with 2 column ("data","name") in your DB
        //there the code below:
        DateTime t1 = DateTime.Now;
        Response.Write("<br>start time:" + t1.ToString());
        string ExcelFile = @"C:\\20090916_Hub_Report.xls";
        string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";

        using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
        {

            excelConnection.Open();
            //Getting source data
            //非空讀入數據
            OleDbCommand command = new OleDbCommand("Select [Region],[CustomerPN],[RMA],[Date],[QTY],[Return/Pull] FROM [20090916_Hub_Report$]  ", excelConnection);
            // Initialize SqlBulkCopy object

            using (OleDbDataReader dr = command.ExecuteReader())
            {
                // Copy data to destination
                string sqlConnectionString = @"Data Source=MININT-G87PHNA\SQLEXPRESS;Initial Catalog=GDS_Service;Integrated Security=True";
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                {
                    bulkCopy.DestinationTableName = "GDS_Hub_data";
                    //加入只加入一個列的話,那么就會其他數據庫列都默認為空。
                    bulkCopy.ColumnMappings.Add("Region", "region");
                    bulkCopy.ColumnMappings.Add("CustomerPN", "customer_item_number");
                    bulkCopy.ColumnMappings.Add("RMA", "Rma");
                    bulkCopy.ColumnMappings.Add("Date", "date");
                    bulkCopy.ColumnMappings.Add("QTY", "Qty_1");
                    bulkCopy.ColumnMappings.Add("Return/Pull", "return_pull");
                    //bcp.BatchSize = 100;//每次传输的行数
                    //bcp.NotifyAfter = 100;//进度提示的行数
                    bulkCopy.BatchSize = 100;
                    bulkCopy.NotifyAfter = 100;
                    bulkCopy.WriteToServer((IDataReader)dr);
                }
            }
            //Closing connection
            excelConnection.Close();
        }

        DateTime t2 = DateTime.Now;
        Response.Write("<br>End time:" + t2.ToString());
        Response.Write("<br>use time:" + ((TimeSpan)(t2 - t1)).Milliseconds.ToString() + " Milliseconds");
        Response.Write("<br>inser record count :3307");

2.快速导出db的数据到excel

这种方法就是利用

Excel.QueryTables
Excel.QueryTable

Querytable把数据快速导入excel里。我们在做复杂报表的时候，这个用的是比较多了，但是单单会这个没有用，它只是快速的把db里的数据放放到excel里，

在做大量数据而且需要设定excel样式的时候我们会选择先用这种方法把数据导入excel一个临时sheet，再利sheet复制，sheet移动，和一些excel样式设定，以及

excel一个强大的自动填充的功能，那么这些就可以让我们快速的做出花样多试的excel报表，当然这个要求我们比较熟练office的操作，包括宏的操作。

	public string query_table_getdata(string sourpath)
        {
            string str_path = sourpath.Substring(0, sourpath.Length - 22);
            str_path = str_path + "basic.xls";

            Excel.QueryTables m_objQryTables = null;
            Excel.QueryTable m_objQryTable = null;
            Excel.Application m_objExcel = null;
            Excel.Workbooks m_objBooks = null;
            Excel.Workbook m_objBook = null;
            Excel.Sheets m_objSheets = null;
            Excel.Worksheet m_objSheet = null;
            Excel.Range m_objRange = null;
            m_objExcel = new Excel.Application();

            //try
            //{
                m_objBooks = m_objExcel.Workbooks;
                m_objBooks.Open(sourpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                m_objBook = (Excel.Workbook)m_objBooks.get_Item(1);


                m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
                m_objSheet = (Excel.Worksheet)m_objSheets.get_Item(1);
                m_objRange = m_objSheet.get_Range("A2", Type.Missing);
                m_objQryTables = m_objSheet.QueryTables;
                string sqlstr = "SELECT [day01],[day02],[day03],[day04],[day05],[day06],[day07],[day08],[day09],[day10],[day11],[day12],[day13],[day14]";
                sqlstr += ",[week01] ,[week02],[week03],[week04],[week05],[week06],[week07],[week08],[week09],[week10],[week11],[week12],[week13],[week14]";
                sqlstr += ",[week15],[week16],[week17],[week18],[week19],[week20],[week21],[week22],[week23],[week24]";
                sqlstr += " FROM [GDS_Service].[dbo].[GDS_Service_Report_Base] order by groupID ,id";
                             //可以放在配置文件里
                string conn = @"Provider=SQLOLEDB.1;Data Source=MININT-G87PHNA\SQLEXPRESS;uid=xijun_ke;Password=12345678;                   Initial Catalog=GDS_Service;Persist Security Info=False;";

                m_objQryTable = (Excel.QueryTable)m_objQryTables.Add("OLEDB;" + conn, m_objRange, sqlstr);

                m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;

                m_objQryTable.Refresh(false);
                m_objBook.SaveAs(str_path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                m_objBook.Close(false, Type.Missing, Type.Missing);
            //}
            //catch (Exception ee)
            //{
            //    mp.WriteLog(ee.ToString());
            //}
            //finally
            //{
                m_objExcel.Quit();
                GC.Collect();
            //}
            return str_path;
        }

com操作excel的一些特性操作：

range.NumberFormatLocal = "@";     //设置单元格格式为文本 
range = (Range)worksheet.get_Range("A1", "E1");     //获取Excel多个单元格区域：本例做为Excel表头
range.Merge(0);     //单元格合并动作
worksheet.Cells[1, 1] = "Excel单元格赋值";     //Excel单元格赋值
range.Font.Size = 15;     //设置字体大小
range.Font.Underline=true;     //设置字体是否有下划线
range.Font.Name="黑体";     设置字体的种类
range.HorizontalAlignment=XlHAlign.xlHAlignCenter;     //设置字体在单元格内的对其方式
range.ColumnWidth=15;     //设置单元格的宽度
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();     //设置单元格的背景色
range.Borders.LineStyle=1;     //设置单元格边框的粗细
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());     //给单元格加边框
range.EntireColumn.AutoFit();     //自动调整列宽
Range.HorizontalAlignment= xlCenter;     // 文本水平居中方式
Range.VerticalAlignment= xlCenter     //文本垂直居中方式
Range.WrapText=true;     //文本自动换行
Range.Interior.ColorIndex=39;     //填充颜色为淡紫色
Range.Font.Color=clBlue;     //字体颜色
xlsApp.DisplayAlerts=false;     //保存Excel的时候，不弹出是否保存的窗口直接进行保存
workbook.SaveCopyAs(temp);/**填入完信息之后另存到路径及文件名字

excel宏操作，sheet和单元格操作：

    /// <summary>
    /// 讀取excel數據和插入公式
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button2_Click(object sender, EventArgs e)
    {
        DateTime t1 = DateTime.Now;
        Response.Write("<br>start time:" + t1.ToString());
        Excel.Application excelkk = new Excel.Application();
        
        excelkk.Workbooks.Add(true);
        int row = 2;
        DataTable myTable = ds.Tables["[Sheet1$]"];
        for (int i = 0; i < myTable.Columns.Count; i++)
        {
            excelkk.Cells[1, 1 + i] = myTable.Columns[i].ColumnName.ToString();
        }
        for (int i = 0; i < myTable.Rows.Count; i++)
        {
            for (int j = 0; j < myTable.Columns.Count; j++)
            {
                excelkk.Cells[row, j + 1] = myTable.Rows[i][j].ToString();
            }
            
            row++;
        }
      
       //取得特定單元格的值
        excelkk.Visible = true;
        this.TextBoxChange.Text = excelkk.get_Range("A2", Type.Missing).Text.ToString();
        //表的單元格合并
        Excel.Range range1 = excelkk.get_Range("A2", "D4");
        range1.Merge(Type.Missing);
        
        //想表格中插入求和的值
        Excel.Range range2 = excelkk.get_Range("B25", Type.Missing);
        range2.Formula = "=SUM(B2:B24)";
        range2.Calculate();

        //進行宏的循環應用與單元格的刪除和添加，多個單元格默認宏自動操作。
        Excel.Range range3 = excelkk.get_Range("B25","E25");
        range2.AutoFill(range3,Excel.XlAutoFillType.xlFillDefault);
        //刪除表的指定行數操作
        Excel.Range range7 = null;
        range7 = excelkk.get_Range(excelkk.Cells[2, 2], excelkk.Cells[4, 4]);
        range7.Select();
        range7.EntireRow.Delete(Excel.XlDirection.xlUp);

        //獲取最大用過的行數
        Excel.Worksheet wsheet1 = (Excel.Worksheet)excelkk.Worksheets.get_Item(1);
        int n =wsheet1.UsedRange.Cells.Columns.Count;
        Response.Write(n.ToString() + "<br>");
        //MessageBox.Show(n.ToString());
        n = wsheet1.UsedRange.Cells.Rows.Count;
        Response.Write(n.ToString() + "<br>");
        //MessageBox.Show(n.ToString());
        //數據的複製
        Excel.Range range4 = excelkk.get_Range("A2", "B25");
        Excel.Range range5 = excelkk.get_Range("E3", "F25");
        //range4.get_Offset(1,4).Select();
        range4.Copy(range5);
        //停用警告信息
        excelkk.DisplayAlerts = false;
        GC.Collect();     
    }


單個sheet里求和：
	Excel.Range range2 = excelkk.get_Range("B25", Type.Missing);
        range2.Formula = "=SUM(B2:B24)";
        range2.Calculate();

跨sheet求和：
	 Excel.Worksheet wsheet1 = (Excel.Worksheet)excelSql.Worksheets.get_Item(1);
        Excel.Range range3 =wsheet1.get_Range("A23", Type.Missing);
        range3.Formula = "=Sheet3!B8+Sheet3!B12";
        range3.Calculate();

虽然我们拥有强大的NPOI，不过我还是写出来，希望对大家理解office有一定的用处。有错误的地方欢迎大家拍砖,希望交流和共享。

From：http://www.cnblogs.com/MR_ke/archive/2010/03/02/1676210.html

SDMrFeng

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
C#操作excel(多种方法比较)

我们在做excel资料的时候，通常有以下方法。一.导入导出excel常用方法：1.用查询表的方式查询并show在数据集控件上。 public static string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =C:\\08.xls;Extended Properties=Excel 8.0"
复制链接

扫一扫

专栏目录