Export/Import相关操作
导言:在项目的开发过程当中,我们会经常面临数据的导入与导出,尤其是在和多方合作的情况下,数据流会在多个系统当中穿梭,那我们怎么把外部数据导入到我们的系统,同时我们又怎样很好地提供数据给外部使用,这是本篇文章所要关注的,同时会记录与分享关于性能方面的思考。
一、Export
这种case最为常见,但是实现的方式却有多种,下面将一一加以展示。
1.直接用Microsoft的Excel组件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public static void DataSetToExcel(DataSet dstResource, string fileName, string worksheetName)
2 {
3 if (Gadget.CheckDataSetIsNullOrNot(dstResource))
4 {
5 Excel.Application oXL;
6 Excel.Workbook oWB;
7 Excel.Worksheet oSheet;
8 Excel.Range oRange;
9
10 // Start Excel and get Application object.
11 oXL = new Excel.Application();
12
13 // Set some properties
14 oXL.Visible = true;
15 oXL.DisplayAlerts = false;
16
17 // Get a new workbook.
18 oWB = oXL.Workbooks.Add(Missing.Value);
19
20 // Get the active sheet
21 oSheet = (Excel.Worksheet)oWB.ActiveSheet;
22 oSheet.Name = worksheetName;
23
24 int rowCount = 1;
25 foreach (DataRow dr in dstResource.Tables[0].Rows)
26 {
27 rowCount += 1;
28 for (int i = 1; i < dstResource.Tables[0].Columns.Count + 1; i++)
29 {
30 // Add the header the first time through
31 if (rowCount == 2)
32 {
33 oSheet.Cells[1, i] = dstResource.Tables[0].Columns[i - 1].ColumnName;
34 }
35 oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
36 }
37 }
38
39 //// Resize the columns
40 //oRange = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[rowCount, dstResource.Tables[0].Columns.Count]);
41 //oRange.EntireColumn.AutoFit();
42
43 // Save the sheet and close
44 oSheet = null;
45 oRange = null;
46 oWB.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal,
47 Missing.Value, Missing.Value, Missing.Value, Missing.Value,
48 Excel.XlSaveAsAccessMode.xlExclusive,
49 Missing.Value, Missing.Value, Missing.Value,
50 Missing.Value, Missing.Value);
51 oWB.Close(Missing.Value, Missing.Value, Missing.Value);
52 oWB = null;
53 oXL.Quit();
54
55 // Clean up
56 // NOTE: When in release mode, this does the trick
57 GC.WaitForPendingFinalizers();
58 GC.Collect();
59 GC.WaitForPendingFinalizers();
60 GC.Collect();
61 }
62 }
上面的代码都有注释,这里就不再赘述,原理其实非常简单,就是把DataSet里的记录和Excel的Cell一一对应即可。这种方式是我们最常使用的,但是它有一个缺点,就是当数据量大的时候,性能不是很好。
2.先保存成TXT或CSV文件,然后转成Excel
我们知道用Tab分割的TXT文件或者用逗号分隔的CSV文件可以用Excel打开,正是基于此,我们思考是否可以先生成TXT或CSV文件,然后再转化成Excel文件,事实是这样完全可以,而且性能极佳,因为文本文件是没有任何多余的信息,所以当数据量大的时候,我们可以采用此种方式。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public static void DataSetToTXT(DataSet dstResource, string fileName)
2 {
3 StringBuilder sbdContent = new StringBuilder(string.Empty);
4 if (Gadget.CheckDataSetIsNullOrNot(dstResource))
5 {
6 foreach (DataColumn dcnRecource in dstResource.Tables[0].Columns)
7 {
8 sbdContent.Append(dcnRecource.ColumnName);
9 sbdContent.Append(Constant.TextConstant.Tab);
10 }
11 sbdContent.Append(Constant.TextConstant.NewLine);
12 foreach (DataRow drwResource in dstResource.Tables[0].Rows)
13 {
14 foreach (DataColumn dcnRecource in dstResource.Tables[0].Columns)
15 {
16 sbdContent.Append(drwResource[dcnRecource.ColumnName].ToString());
17 sbdContent.Append(Constant.TextConstant.Tab);
18 }
19 sbdContent.Append(Constant.TextConstant.NewLine);
20 }
21 }
22 FileHelper.SaveStringToTXT(fileName, sbdContent);
23 }
在把TXT文件转化成Excel文件时也有两种方式,一种是强制转换(无样式),第二种是借助Excel组件进行有格式的转换,代码如下:
1 public static void TXTToExcel(string fileName, bool directlyConvert)
2 {
3 File.Move(fileName, fileName.Replace(Constant.FileConstant.FileTypeForTXT, Constant.FileConstant.FileTypeForExcel));
4 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public static void TXTToExcel(string fileName)
2 {
3 Excel.Application m_objExcel = null;
4 Excel.Workbooks m_objBooks = null;
5 Excel._Workbook m_objBook = null;
6 object m_objOpt = Missing.Value;
7 m_objExcel = new Excel.Application();
8 m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
9
10 m_objBooks.OpenText(fileName,
11 Excel.XlPlatform.xlWindows,
12 1,
13 Excel.XlTextParsingType.xlDelimited,
14 Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
15 false, true, false, false, false, false,
16 m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt
17 );
18
19 m_objBook = m_objExcel.ActiveWorkbook;
20
21 m_objBook.SaveAs(fileName.Replace(Constant.FileConstant.FileTypeForTXT, Constant.FileConstant.FileTypeForExcel),
22 Excel.XlFileFormat.xlWorkbookNormal,
23 m_objOpt, m_objOpt, m_objOpt, m_objOpt,
24 Excel.XlSaveAsAccessMode.xlNoChange,
25 m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt
26 );
27
28 m_objBook.Close(false, m_objOpt, m_objOpt);
29 m_objExcel.Quit();
30 }
3.借用OLEDB去插入记录
如果是插入/更新/查找某些记录,可以使用这种方式,但是如果是大数据量的时候不推荐此种方式,因为好像这里的Insert语句只支持一条一条的往里面插入,多个Insert语句会直接报错,目前还不知道为什么,有待深入研究。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public static void DataSetToExcel(DataSet dstResource,string fileName)
2 {
3 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
4 using (OleDbConnection conn = new OleDbConnection(strConn))
5 {
6 conn.Open();
7 OleDbCommand myCommand = new OleDbCommand();
8 myCommand.Connection = conn;
9 foreach (DataRow dr in dstResource.Tables[0].Rows)
10 {
11 myCommand.CommandText = "INSERT INTO [Sheet1$] VALUES ('" + dr["Field1"].ToString() + "','" + dr["Field2"].ToString() + "')";
12 myCommand.ExecuteNonQuery();
13 }
14 }
15 }
二、Import
导入的关键是数据的读取,下面将读取的方法展示如下
直接使用OLEDB从Excel文件读取数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public static DataSet ExcelToDataSet(string fileName, string sheetName)
2 {
3 DataSet ds = new DataSet();
4 string strExcel = "SELECT * FROM [" + sheetName + "$]";
5 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
6 using (OleDbConnection conn = new OleDbConnection(strConn))
7 {
8 OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
9 myCommand.Fill(ds, sheetName);
10 }
11 return ds;
12 }
用Excel组件读取数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public static void OpenExcel(string fileName)
2 {
3 Excel.Application excel = new Excel.Application();
4 excel.Visible = false;
5 excel.UserControl = true;
6
7 // Open excel file with readonly model
8 Excel.Workbook wb = excel.Application.Workbooks.Open(fileName, Missing.Value, true, Missing.Value,
9 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true,
10 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
11
12 //Get the first worksheet
13 Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1);
14
15 //Get the number of rows (include the header)
16 int rowsCount = ws.UsedRange.Cells.Rows.Count;
17
18 //Get the data range (don't include header)
19 Excel.Range range = ws.Cells.get_Range("B1", "B" + rowsCount);
20 object[,] arryItem = (object[,])range.Value2;
21
22 //Assign the value to array
23 string[,] arry = new string[rowsCount - 1, 2];
24 for (int i = 1; i <= rowsCount - 1; i++)
25 {
26 arry[i - 1, 0] = arryItem[i, 1].ToString();
27 }
28
29 excel.Quit();
30 excel = null;
31 GC.Collect();
32 }
如果导入的数据和DB中表的数据是完全对应的,亦即Excel中的字段顺序和DB表中字段的顺序是完全一致的,为了提高性能,我们可以使用SqlBulkCopy
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public void ImportExcelWithSqlBulkCopy(string excelFile, string sheetName, string connectionString)
2 {
3 DataSet ds = new DataSet();
4 try
5 {
6 //Get all data from Excel and save them into DataSet
7 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
8 using (OleDbConnection conn = new OleDbConnection(strConn))
9 {
10 string strExcel = string.Format("select * from [{0}$]", sheetName);
11 OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
12 myCommand.Fill(ds, sheetName);
13 }
14
15 //Create table if it's not existed in DB
16 string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
17 foreach (DataColumn c in ds.Tables[0].Columns)
18 {
19 strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
20 }
21 strSql = strSql.Trim(',') + ")";
22
23 using (SqlConnection sqlconn = new SqlConnection(connectionString))
24 {
25 sqlconn.Open();
26 SqlCommand command = sqlconn.CreateCommand();
27 command.CommandText = strSql;
28 command.ExecuteNonQuery();
29 sqlconn.Close();
30 }
31
32 //Import data with BCP
33 using (SqlBulkCopy bcp = new SqlBulkCopy(connectionString))
34 {
35 bcp.BatchSize = 100;
36 bcp.DestinationTableName = sheetName;
37 bcp.WriteToServer(ds.Tables[0]);
38 }
39 }
40 catch (Exception ex)
41 {
42
43 }
44 }