Export/Import相关操作

　　导言：在项目的开发过程当中，我们会经常面临数据的导入与导出，尤其是在和多方合作的情况下，数据流会在多个系统当中穿梭，那我们怎么把外部数据导入到我们的系统，同时我们又怎样很好地提供数据给外部使用，这是本篇文章所要关注的，同时会记录与分享关于性能方面的思考。

一、Export

　　这种case最为常见，但是实现的方式却有多种，下面将一一加以展示。

1.直接用Microsoft的Excel组件

public static void DataSetToExcel(DataSet dstResource, string fileName, string worksheetName)
{
    if (Gadget.CheckDataSetIsNullOrNot(dstResource))
    {
        Excel.Application oXL;
        Excel.Workbook oWB;
        Excel.Worksheet oSheet;
        Excel.Range oRange;

        // Start Excel and get Application object. 
         oXL = new Excel.Application();

        // Set some properties 
         oXL.Visible = true;
        oXL.DisplayAlerts = false;

        // Get a new workbook. 
         oWB = oXL.Workbooks.Add(Missing.Value);

        // Get the active sheet 
         oSheet = (Excel.Worksheet)oWB.ActiveSheet;
        oSheet.Name = worksheetName;

        int rowCount = 1;
        foreach (DataRow dr in dstResource.Tables[0].Rows)
        {
            rowCount += 1;
            for (int i = 1; i < dstResource.Tables[0].Columns.Count + 1; i++)
            {
                // Add the header the first time through 
                 if (rowCount == 2)
                {
                    oSheet.Cells[1, i] = dstResource.Tables[0].Columns[i - 1].ColumnName;
                }
                oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
            }
        }

        //// Resize the columns 
         //oRange = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[rowCount, dstResource.Tables[0].Columns.Count]);
        //oRange.EntireColumn.AutoFit();

        // Save the sheet and close 
         oSheet = null;
        oRange = null;
        oWB.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            Excel.XlSaveAsAccessMode.xlExclusive,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
        oWB.Close(Missing.Value, Missing.Value, Missing.Value);
        oWB = null;
        oXL.Quit();

        // Clean up 
        // NOTE: When in release mode, this does the trick 
         GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
    }
}

上面的代码都有注释，这里就不再赘述，原理其实非常简单，就是把DataSet里的记录和Excel的Cell一一对应即可。这种方式是我们最常使用的，但是它有一个缺点，就是当数据量大的时候，性能不是很好。

2.先保存成TXT或CSV文件，然后转成Excel

我们知道用Tab分割的TXT文件或者用逗号分隔的CSV文件可以用Excel打开，正是基于此，我们思考是否可以先生成TXT或CSV文件，然后再转化成Excel文件，事实是这样完全可以，而且性能极佳，因为文本文件是没有任何多余的信息，所以当数据量大的时候，我们可以采用此种方式。

public static void DataSetToTXT(DataSet dstResource, string fileName)
{
    StringBuilder sbdContent = new StringBuilder(string.Empty);
    if (Gadget.CheckDataSetIsNullOrNot(dstResource))
    {
        foreach (DataColumn dcnRecource in dstResource.Tables[0].Columns)
        {
            sbdContent.Append(dcnRecource.ColumnName);
            sbdContent.Append(Constant.TextConstant.Tab);
        }
        sbdContent.Append(Constant.TextConstant.NewLine);
        foreach (DataRow drwResource in dstResource.Tables[0].Rows)
        {
            foreach (DataColumn dcnRecource in dstResource.Tables[0].Columns)
            {
                sbdContent.Append(drwResource[dcnRecource.ColumnName].ToString());
                sbdContent.Append(Constant.TextConstant.Tab);
            }
            sbdContent.Append(Constant.TextConstant.NewLine);
        }
    }
    FileHelper.SaveStringToTXT(fileName, sbdContent);
}

在把TXT文件转化成Excel文件时也有两种方式，一种是强制转换（无样式），第二种是借助Excel组件进行有格式的转换，代码如下：

public static void TXTToExcel(string fileName, bool directlyConvert)
{
    File.Move(fileName, fileName.Replace(Constant.FileConstant.FileTypeForTXT, Constant.FileConstant.FileTypeForExcel));
}

public static void TXTToExcel(string fileName)
{
    Excel.Application m_objExcel = null;
    Excel.Workbooks m_objBooks = null;
    Excel._Workbook m_objBook = null;
    object m_objOpt = Missing.Value;
    m_objExcel = new Excel.Application();
    m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

    m_objBooks.OpenText(fileName,
        Excel.XlPlatform.xlWindows,
        1,
        Excel.XlTextParsingType.xlDelimited,
        Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
        false, true, false, false, false, false,
        m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt
    );

    m_objBook = m_objExcel.ActiveWorkbook;

    m_objBook.SaveAs(fileName.Replace(Constant.FileConstant.FileTypeForTXT, Constant.FileConstant.FileTypeForExcel),
        Excel.XlFileFormat.xlWorkbookNormal,
        m_objOpt, m_objOpt, m_objOpt, m_objOpt,
        Excel.XlSaveAsAccessMode.xlNoChange,
        m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt
    );

    m_objBook.Close(false, m_objOpt, m_objOpt);
    m_objExcel.Quit();
}

以上两种转换方式的明显区别是当你用第一种方式打开生成的Excel文件并修改里面的样式或内容时会提示你部分不兼容，而第二种则不会。

3.借用OLEDB去插入记录

如果是插入/更新/查找某些记录，可以使用这种方式，但是如果是大数据量的时候不推荐此种方式，因为好像这里的Insert语句只支持一条一条的往里面插入，多个Insert语句会直接报错，目前还不知道为什么，有待深入研究。

public static void DataSetToExcel(DataSet dstResource,string fileName)
{
    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
    using (OleDbConnection conn = new OleDbConnection(strConn))
    {
        conn.Open();
        OleDbCommand myCommand = new OleDbCommand();
        myCommand.Connection = conn;
        foreach (DataRow dr in dstResource.Tables[0].Rows)
        {
            myCommand.CommandText = "INSERT INTO [Sheet1$] VALUES ('" + dr["Field1"].ToString() + "','" + dr["Field2"].ToString() + "')";
            myCommand.ExecuteNonQuery();
        }
    }
}

二、Import

导入的关键是数据的读取，下面将读取的方法展示如下

直接使用OLEDB从Excel文件读取数据

public static DataSet ExcelToDataSet(string fileName, string sheetName)
{
    DataSet ds = new DataSet();
    string strExcel = "SELECT * FROM [" + sheetName + "$]";
    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
    using (OleDbConnection conn = new OleDbConnection(strConn))
    {
        OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
        myCommand.Fill(ds, sheetName);
    }
    return ds;
}

用Excel组件读取数据

public static void OpenExcel(string fileName)
{
    Excel.Application excel = new Excel.Application();
    excel.Visible = false;
    excel.UserControl = true;

    // Open excel file with readonly model  
     Excel.Workbook wb = excel.Application.Workbooks.Open(fileName, Missing.Value, true, Missing.Value,
        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true,
        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

    //Get the first worksheet  
     Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1);

    //Get the number of rows (include the header)  
    int rowsCount = ws.UsedRange.Cells.Rows.Count;

    //Get the data range  (don't include header)    
    Excel.Range range = ws.Cells.get_Range("B1", "B" + rowsCount);
    object[,] arryItem = (object[,])range.Value2;

    //Assign the value to array  
    string[,] arry = new string[rowsCount - 1, 2];
    for (int i = 1; i <= rowsCount - 1; i++)
    {
        arry[i - 1, 0] = arryItem[i, 1].ToString();
    }

    excel.Quit();
    excel = null;
    GC.Collect();
}

如果导入的数据和DB中表的数据是完全对应的，亦即Excel中的字段顺序和DB表中字段的顺序是完全一致的，为了提高性能，我们可以使用SqlBulkCopy

public void ImportExcelWithSqlBulkCopy(string excelFile, string sheetName, string connectionString)
{
    DataSet ds = new DataSet();
    try
    {
        //Get all data from Excel and save them into DataSet   
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
            string strExcel = string.Format("select * from [{0}$]", sheetName);
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
            myCommand.Fill(ds, sheetName);
        }

        //Create table if it's not existed in DB   
        string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
        foreach (DataColumn c in ds.Tables[0].Columns)
        {
            strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
        }
        strSql = strSql.Trim(',') + ")";

        using (SqlConnection sqlconn = new SqlConnection(connectionString))
        {
            sqlconn.Open();
            SqlCommand command = sqlconn.CreateCommand();
            command.CommandText = strSql;
            command.ExecuteNonQuery();
            sqlconn.Close();
        }

        //Import data with BCP   
        using (SqlBulkCopy bcp = new SqlBulkCopy(connectionString))
        {
            bcp.BatchSize = 100;
            bcp.DestinationTableName = sheetName;
            bcp.WriteToServer(ds.Tables[0]);
        }
    }
    catch (Exception ex)
    {

    }
}