操作Execl的三种方法

来源：互联网发布：问财神营销软件编辑：程序博客网时间：2024/05/17 23:12

http://blog.csdn.net/hfzsjz/article/details/3521427

http://blog.csdn.net/hfzsjz/article/details/3521331

http://blog.csdn.net/hfzsjz/article/details/3521482

方法1：通过oledb，使用sql语句操作数据表

create table会创建worksheet，insert语句会插入一行。用StringBuilder性能会好一些。

public void DealExcelOut(){    String strconn = "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" + path2 + "; Extended Properties = Excel 8.0";    OleDbConnection cn = new OleDbConnection(strconn);    OleDbCommand cmd = new OleDbCommand();    String cmdstr;    //根据ListView创建VPN表    cn.Open();    cmd.Connection = cn;    cmdstr = "Create Table VPN (";    foreach (ColumnHeader ch in listView_Main.Columns)    {         cmdstr += ch.Text + " TEXT,";    }    cmdstr = cmdstr.Remove(cmdstr.Length - 1);    cmdstr += ")";    cmd.CommandText = cmdstr;    cmd.ExecuteNonQuery();                    //导出数据     long cols = listView_Main.Columns.Count;    foreach (ListViewItem lvi in listView_Main.Items)    {        cmdstr = "Insert Into VPN Values (";        for (long i = 0; i < cols; i++)             cmdstr += "'" + lvi.SubItems[(Int32)i].Text + "',";        cmdstr = cmdstr.Remove(cmdstr.Length - 1);        cmdstr += ")";        cmd.CommandText = cmdstr;        cmd.ExecuteNonQuery();     }     cn.Close();}

方法2：使用office组件

如果电脑上没有安装office，可能会出问题。

<span style="font-family:Arial;color:#333333;">/// <summary>/// 将ListView的内容写入Excel表中/// </summary>/// <param name="LView">ListView控件</param>/// <param name="strFilter">内容的标题</param>public void UWriteListViewToExcel(ListView LView, string strTitle){      Microsoft.Office.Interop.Excel.Application ExcelApp =new Microsoft.Office.Interop.Excel.Application();      object m_objOpt = System.Reflection.Missing.Value;                      Microsoft.Office.Interop.Excel.Workbooks ExcelBooks = (Microsoft.Office.Interop.Excel.Workbooks)ExcelApp.Workbooks;      Microsoft.Office.Interop.Excel._Workbook ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)(ExcelBooks.Add(m_objOpt));      Microsoft.Office.Interop.Excel._Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;                      //设置标题      ExcelApp.Caption = strTitle;      ExcelSheet.Cells[1, 1] = strTitle;      //写入列名      for (int i = 1; i <= LView.Columns.Count; i++)      {            ExcelSheet.Cells[2, i] = LView.Columns[i-1].Text;      }      //写入内容      for (int i = 3; i < LView.Items.Count + 3; i++)      {            ExcelSheet.Cells[i, 1] = LView.Items[i - 3].Text;            for (int j = 2; j <= LView.Columns.Count; j++)            {                 ExcelSheet.Cells[i, j] = LView.Items[i-3].SubItems[j-1].Text;            }      }      //显示Excel      ExcelApp.Visible = true;            }</span>

方法3：导出为逗号分隔的文本

本质上是csv文件，如果文本里有逗号、换行等等控制字符，需要仔细处理。因此不推荐。

<span style="font-family:Arial;color:#333333;">public static bool ExportForListView(ListView listView, string fileName, bool isShowExcle) {      FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write);      StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);      string excel = "";      //用于存放要写入的一行文本。      for (int i = 0; i < listView.Columns.Count; i++)      {           excel = excel + listView.Columns[i].Text.ToString().Trim() + Convert.ToChar(9);      }      sw.WriteLine(excel);    //写入DataGridView的标题行。      excel = "";      for (int i = 0; i < listView.Items.Count; i++)      {           for (int j = 0; j < listView.Columns.Count; j++)           {                if (listView.Items[i].SubItems[j].Text.ToString() == null)                     excel = excel + "" + Convert.ToChar(9);    //循环写入每一行                else                     excel = excel + listView.Items[i].SubItems[j].Text.ToString() + Convert.ToChar(9);            }            sw.WriteLine(excel);            excel = "";      }      sw.Close();      fs.Close();      if (isShowExcle)      {            System.Diagnostics.Process.Start(fileName);      }      return true;  }</span>

0 0