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";
    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 create an excel file  C:\Inetpub\wwwroot\test.xls , fill cell(1,1) with "Data",cell(1,2) with "name"
         //step create table named "Data" with column ("data","name") in your DB
         //there the code below:
         DateTime t1 = DateTime.Now;
         Response.Write("<br>start time:" + t1.ToString());
         string ExcelFile = @"C:\\_Hub_Report.xls";
         string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile + ";Extended Properties='Excel.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 [_Hub_Report$]  ", excelConnection);
             // Initialize SqlBulkCopy object

             using (OleDbDataReader dr = command.ExecuteReader())
             {
                 // Copy data to destination
                 string sqlConnectionString = @"Data Source=MININT-GPHNA\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 =;//每次传输的行数
                     //bcp.NotifyAfter =;//进度提示的行数
                     bulkCopy.BatchSize =;
                     bulkCopy.NotifyAfter =;
                     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 :");

 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有一定的用处。

以上所述是小编给大家介绍的C#多种操作excel的方法比较,希望对大家有所帮助。在此也非常感谢大家对我们网站的支持!

(0)

相关推荐

  • C# 基于NPOI操作Excel

    目录 1 单元格下拉框 2 添加批注 3 读取数据 结语 常见问题解决 NPOI 导出添加批注功能 POI导出Excel时下拉列表值超过255的问题 日期格式导入混乱 注意 1 单元格下拉框 在开发中我们会遇到为单元格设置下拉框.一般可以编写如下: var cellRanges = new CellRangeAddressList(firstRow, lastRow, firstCol, latsCol); DVConstraint constraint = DVConstraint.Creat

  • C# Winform实现导入和导出Excel文件

    本文实例为大家分享了Winform实现导入导出Excel文件的具体代码,供大家参考,具体内容如下 /// <summary> /// 导出Excel文件 /// </summary> /// /// <param name="dataSet"></param> /// <param name="dataTable">数据集</param> /// <param name="isS

  • c#读取excel数据的两种方法实现

    方法一:OleDb: 用这种方法读取Excel速度还是非常的快的,但这种方式读取数据的时候不太灵活,不过可以在 DataTable 中对数据进行一些删减修改. 优点:读取方式简单.读取速度快 缺点:除了读取过程不太灵活之外,这种读取方式还有个弊端就是,当Excel数据量很大时.会非常占用内存,当内存不够时会抛出内存溢出的异常. 不过一般情况下还是非常不错的. (代码比原文相较有所修改) DataTable GetDataFromExcelByConn(bool hasTitle = false)

  • C# 从Excel读取数据向SQL server写入

    第一次写C#与sql的东西,主要任务是从Excel读取数据,再存到SQL server中. 先上读取Excel文件的code如下. public bool GetFiles(string equipName) { //choose all sheet? or all data in sheet? string strExcel = "select * from [Sheet1$]"; //初始化system.IO的配置(路径) DirectoryInfo directoryInfo1

  • C#操作Excel相关方法总结

    0.导入命名空间: using Microsoft.Office.Core; using Microsoft.Office.Interop.Excel; using System.IO; using System.Reflection; 1.如何打开已有excel文档,或者创建一个新的excel文档 Application app = new Application(); Workbooks wbks = app.Workbooks; _Workbook _wbk = wbks.Add(xxx)

  • 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"; public static DataSet ds; protected void Page_Load(obj

  • python操作excel的方法(xlsxwriter包的使用)

    本文介绍python操作excel的方法(xlsxwriter包的使用),具体内容如下 xlsxwriter包的安装 pip install xlsxwriter Workbook类 创建一个excel文件 filename = "test.xlsx" # Workbook代表整个电子表格文件,并且存储在磁盘上.filename为字符串类型,表示创建的excel文件存储路径: wb = xlsxwriter.Workbook(filename) 创建一个sheet表:add_works

  • java操作excel的方法

    本文实例讲述了java操作excel的方法.分享给大家供大家参考.具体如下: WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\\output.xls")); WritableSheet sheet = workbook.createSheet("项目简报", 0); //样式 WritableFont sonti18font = new WritableFont(WritableFon

  • Python使用xlwt模块操作Excel的方法详解

    本文实例讲述了Python使用xlwt模块操作Excel的方法.分享给大家供大家参考,具体如下: 部分摘自官网文档. 该模块安装很简单 $ pip install xlwt 先来个简单的例子: #!/usr/bin/python #coding=utf-8 # ============================================================================== # # Filename: demo.py # Description: exc

  • python操作excel的方法

    摘要: Openpyxl是一个常用的python库,用于对Excel的常用格式及其模板进行数据读写等操作. 简介与安装openpyxl库 Openpyxl is a Python library for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files. 安装 pip install openpyxl pillow:在文件中需要使用images (jpeg, png, bmp,...)时,需要安装pillow库. 注意:写操作时请关

  • Python基于xlrd模块操作Excel的方法示例

    本文实例讲述了Python基于xlrd模块操作Excel的方法.分享给大家供大家参考,具体如下: 一.使用xlrd读取excel 1.xlrd的安装: pip install xlrd==0.9.4 2.基本操作示例: #coding: utf-8 import xlrd #导入xlrd模块 xlsfile=r"D:\workspace\host.xls" #获得excel的book对象 book = xlrd.open_workbook(filename=None, file_con

  • python使用openpyxl操作excel的方法步骤

    一 前言 知识追寻者又要放大招了,学完这篇openpyxl第三方库,读者将会懂得如何灵活的读取excel数据,如何创建excel工作表:更新工作表,删除工作表:是不是感觉很强大,留下赞赞吧!! 二 openpyxl常用属性函数 常用函数或者属性 说明 openpyxl.load_workbook() 加载excel工作本 Workbook.active 获得默认sheet Workbook.create_sheet() 创建sheet Workbook.get_sheet_names() 已过时

  • Golang操作excel的方法

    关键术语介绍 为了方便开源库的快速上手,我们先来了解 excel 中的几个关键术语,如下图所示,①为sheet,也就是表格中的页签:②为row,代表 excel 中的一行:③为cell,代表 excel 中的一个单元格. 正常情况下,创建一个表格的基本流程是打开 wps 点击新建,这时会默认创建一个 sheet,然后在该 sheet 中的第一行填写表头,接下来根据表头逐行填充内容,最后将文件另存为到硬盘的某个位置.这与 Golang 开源库创建 excel 的流程基本相同,下面演示一个极简表格的

  • 分享11个Python自动化操作Excel的方法

    目录 一.openpyxl是什么 二.openpyxl安装 三.openpyxl操作指南 1.创建工作簿 2.写工作簿 3.插入图片 4.删除行和列 5.将工作表转换为数据框 6.2D区域图 7.雷达图 8.使用公式 9.给单元格设定字体颜色 10.设定字体和大小 11.设定单元格的边框.字体.颜色.大小和边框背景色 前言: 今天我教大家如何利用Python自动化操作Excel,包括:介绍操作Excel的工具包.安装方法及操作Excel具体方法.对于每天有大量重复性工作的同学来说,这款工具绝对是

  • VBS操作Excel常见方法

    dim oExcel,oWb,oSheet Set oExcel= CreateObject("Excel.Application") Set oWb = oExcel.Workbooks.Open("E:\其他\新装电话表.xls") Set oSheet = oWb.Sheets("Sheet1") MsgBox oSheet.Range("B2").Value '#提取单元格B2内容 '..... 3.如果是XP系统,可

随机推荐