EXCEL导入导出！

浅浅的花香味﹌ 2021-11-05 01:24 570阅读 0赞

操作EXCEL一般分两种，其一就是用类似连接数据库的方式，将EXCEL文件当作小型数据库操作，这种方式我想大多数程序员都很喜欢；另一种就是用Microsoft.Office.Interop.Excel组件，这种方式操作的时候比较细致。

比较：

第一种：适合读取EXCEL文件，很方便，直接就可将sheet的内容转为DATATABLE;当然它也可以将DATATABLE进行导出，生成EXCEL文件。这种导出是利用

'select .....into \[excel连接的文件\] from 数据库'的形式，一看便知道其实很局限，必须连接数据库，从数据库中查询出一个DATATABLE才能将整表生成EXCEL文件。

第二种：显然在读取文件的方面不如第一种；但是在将DATATABLE导出生成为EXCEL文件这一步，很强大，效果也很好。

以下是自己总结的几个方法：

1.读取EXCEL文件

//返回所有sheet名
           public string[] ImportExcel(string filePath)
           {
               string strConn = FatherForm.xml._excConn1 + filePath + FatherForm.xml._excConn2;
               OleDbConnection conn = new OleDbConnection(strConn);
               conn.Open();
    
               DataTable dt=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"table"});
               int len=dt.Rows.Count;
               string[] sheetNames = new string[len];
               int start;
               for (start = 0; start < len; start++)
               {
                   string allName=dt.Rows[start]["TABLE_NAME"].ToString();
                   sheetNames[start] = allName.Replace("$","");//remove $ sysmbol
               }          
               conn.Close();
               return sheetNames;
           }
           //以datatable的形式返回sheet的内容
           public DataTable ShowSheet(string filePath, string sheetName)
           {
               string strConn = FatherForm.xml._excConn1 + filePath + FatherForm.xml._excConn2;
               OleDbConnection conn = new OleDbConnection(strConn);
               conn.Open();
               string sql = "select * from [" + sheetName + "$]";
               OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
               DataTable dt = new DataTable();
               da.Fill(dt);
               conn.Close();
               return dt;
           }

2.将EXCEL文件读取后存储到数据库

这个必须得说一下，在这个步骤里方法很多，最常用的就是InsertCommand,SqlDataAdapter.Update,SqlBulkCopy，这三种里面我实现了后两种，然后只用了第二种。  
    2.1Adapter.Update

//这个办法可以将datatable存储至数据库，格式不一样也没事，要么补齐（这里采用补齐），要么就在数据库可空字段显示为null
           public bool ImportToDataBase(DataTable dt, string sql, string destinationTatbleName,string whichOperator)
           {
               if (dt == null || dt.Rows.Count == 0)
               {
                   return false;
               }
               else
               {
                   string strConn = FatherForm.xml._dbConn;
                   SqlConnection conn = new SqlConnection(strConn);
                   conn.Open();
                  
                   SqlDataAdapter da = new SqlDataAdapter(sql,conn);
                   SqlCommandBuilder comm = new SqlCommandBuilder(da);
                   DataTable table = new DataTable();
                   da.Fill(table);
    
                   int start;
                   for (start = 0; start < dt.Rows.Count; start++)
                   { //KHMC,YHDM,GSMC,NSRSBH,ZCDZ,LXDH,SSBM,KHHMC,YHZH,REMARK,POSTADDRESS
                       DataRow dr = table.NewRow();
                       dr["SEARIL"] =Guid.NewGuid().ToString("N").ToUpper();
                       dr["OPERATOR"] =whichOperator;
                       for (int i = 0; i < dt.Columns.Count; i++)
                       {
                           dr[i+1]=dt.Rows[start][i].ToString();
                       }
                           table.Rows.Add(dr.ItemArray);
                   }
                   da.Update(table);
                   return true;
               }
           }

很明显，这种方式批量插入时需要先sql一下原始数据库用以获得dataset(或者datareader、datatable，总之就是数据源啦),然后将新得到的datatable(比如从EXCEL文件的sheet1中得到的)嫁接到之前的数据源上，更新即可。效果一般，我数据不大，也感觉不出来。  
  
  
    2.2SqlBulkCopy

//这个办法可以将datatable存储至数据库，但是导入的datatable的结构必须与数据库表的结构一致，比较严格，但效率好，暂时没用到
           public bool ImportToDataBase(DataTable  dt,string destinationTatbleName)
           {
               if (dt == null || dt.Rows.Count==0)
               {
                   return false;
               }
               else
               {
                   string strConn = FatherForm.xml._dbConn;
                   SqlBulkCopy bkc = new SqlBulkCopy(strConn, SqlBulkCopyOptions.UseInternalTransaction);
                   bkc.DestinationTableName = destinationTatbleName;
                   bkc.WriteToServer(dt);           
                   return true;
               }
           }

这种方式很牛逼，若有两个地方的数据库表结构一致，并且希望数据从一个地方COPY到另一个地方的的话用这个办法最好。但是没仔细研究到底能不能结构不一样的进行writetoserver，实在没时间，如果可以那这确实是很优秀的方式。

3.导出生成EXCEL文件

方法1.

//该方法可已经将datatable导出成excel，但是在导出完成的时候会弹出保存框，效果不很好
           public bool ExportExcel(DataTable dtSource,string filePath)
           {
               if (dtSource.Rows.Count == 0)
               {
                   return false;
               }
               else
               {
                   Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                   excel.Application.Workbooks.Add(true);
                   excel.Visible = true;
                   int row, colu;
                   for (row = 0; row < dtSource.Rows.Count; row++)
                   {
                       for (colu = 0; colu < dtSource.Columns.Count; colu++)
                       {
                           excel.Cells[row + 1, colu + 1] = dtSource.Rows[row][colu].ToString();
                       }
                   }
                   excel.DisplayAlerts = false;
                   excel.AlertBeforeOverwriting = false;
                   excel.Application.Workbooks.Add(true).Save();
                   excel.Save(filePath);
                   excel.Quit();
                   excel = null;
                   return true;
               }

方法2.

//导出按钮
            private void btnExport_Click(object sender, EventArgs e)
            {
                SaveFileDialog saveFile = new SaveFileDialog();
                saveFile.Filter = ("Excel2003文件(*.xls)|*.xls|Excel2007文件(*.xlsx)|*.xlsx");
                if (saveFile.ShowDialog() == DialogResult.OK)
                {
                    string filePath = saveFile.FileName;
                    if (System.IO.File.Exists(filePath))
                    {
                        System.IO.File.Delete(filePath);
                    }
                    bool status = exc.ExportExcel(this.dataGridViewX1, filePath);
                    if (!status)
                        MessageBox.Show("空文件无法导出！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    else
                        MessageBox.Show("已成功导出！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
    //该方法是目前测试用的最好的方法，保存时可以传递存储路径，不会弹出保存框
           public bool ExportExcel(DataGridViewX dtSource, string saveFileName)
           {
               DataTable dt = (DataTable)dtSource.DataSource;
               if (dt.Rows.Count == 0||dt==null)
               {
                   return false;
               }
               else
               {
                   Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                   Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                   Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//创建sheet
                   Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//写死，只操作sheet1
                   //添加列标题
                   for (int i = 0; i < dt.Columns.Count; i++)
                   {
                       worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                   }
                   //添加内容
                   for (int r = 0; r < dt.Rows.Count; r++)
                   {
                       for (int i = 0; i < dt.Columns.Count; i++)
                       {
                           worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
                       }
                       System.Windows.Forms.Application.DoEvents();
                   }
                   worksheet.Columns.EntireColumn.AutoFit();//自适应列宽，很实用
                   Microsoft.Office.Interop.Excel.Range rg = worksheet.Range[worksheet.Cells[2, 2], worksheet.Cells[dt.Rows.Count + 1, 2]];
                   rg.NumberFormat = "00000000";
                   if (saveFileName != "")
                   {
                       try
                       {
                           workbook.Saved = true;
                           workbook.SaveCopyAs(saveFileName);
                       }
                       catch (Exception ex)
                       {
                           throw new Exception(ex.Message);
                       }
                   }
                   xlApp.Quit();
                   GC.Collect();
                   return true;
               }
           }

肯定还有其他办法，再次mark。

**补充：**

**1.报错“未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序。**

**解决办法：下载office2007驱动程序[http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe][http_download.microsoft.com_download_7_0_3_703ffbcb-dc0c-4e19-b0da-1463960fdcdb_AccessDatabaseEngine.exe]**

**2.VS自带的组件为MS EXCEL 12.O OBJECT LIBARAY，2007基本兼容所有2003操作，所以主要2007搞定了，低版本肯定没问题。**

[http_download.microsoft.com_download_7_0_3_703ffbcb-dc0c-4e19-b0da-1463960fdcdb_AccessDatabaseEngine.exe]: http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe