How to import, export and process Excel files in C#

OleDB is Microsoft's strategic low-level application programming interface to different data sources. It supports many kinds of databases. The input and output of DBF files in this client also use this database API. When using OleDB, remember to add the namespace:

using System.Data.OleDb;

Input of the xlsx file:

private void inputXlsx()
{
    string strConn2;
    string filePath = inPath;
    FileInfo fileInfo = new FileInfo(filePath);
    string directory = fileInfo.DirectoryName;

    strConn2 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;';";
    string strConnection = string.Format(strConn2, inPath);
    OleDbConnection conn = new OleDbConnection(strConnection);
    try
    {
        conn.Open();
        String tableName = null;
        DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        tableName = dt.Rows[0][2].ToString().Trim();
        OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + tableName + "]", strConnection);
        oada.Fill(dtOld);
        conn.Close();   
    }
    catch (Exception ex)
    {
        conn.Close();
    }
}

The intermediate processing of the xlsx file varies depending on the needs, and the processing method will not be described here. During the input process, the data in the file is converted into the Datatable data type format. It is a grid virtual table that temporarily saves data, and is very convenient to process.

Export of xlsx file: The output process of xlsx file is not very smooth, and three methods are used.

The first way is naturally OleDB, it has a beginning and an end. However, during actual use, I found that this method not only requires Excel to be installed on the computer to run the program, but also has very high requirements for the Excel version. If you delete the registry, it will be self-defeating. If I change the registry, I will directly destroy Excel. In fact, we cannot guarantee that the corresponding version of Excel must be installed on the customer's machine, so if you use OleDB to output files, the compatibility of this client will be very poor.

After some communication and struggle, I decided to change the method and decided to use third-party components. Since the JAVA side uses POI, the .NET version of the POI project, NPOI, is used directly here. There are many online tutorials and it is easy to implement. However, after testing, this method does not support the input and output of large files very well. There is no problem in processing DEMO files, but it is replaced by 100,000 or even million lines provided by real customers. level files, the client crashes directly during the output process.

After searching for information, netizens recommended using EPPlus for large files. There are also a lot of tutorials. I did not continue to increase the file size for testing. However, the dozen or so files provided by the customer all passed the test, and they were basically below one million.

Go to the code:

private void outputXlsx()
{
    FileInfo newFile = new FileInfo(dtNew.TableName + ".xlsx");
    if (newFile.Exists)
    {
        newFile.Delete();
        newFile = new FileInfo(dtNew.TableName + ".xlsx");
    }
    using (ExcelPackage package = new ExcelPackage(newFile))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(dtNew.TableName);//创建worksheet

        for (int m = 0; m < dtNew.Columns.Count; m++)
        {
            worksheet.Cells[1, m + 1].Value = dtNew.Columns[m];
        }
        int i = 1;
        foreach (DataRow dr in dtNew.Rows)
        {
            for (int j = 0; j < dtNew.Columns.Count; j++)
            {
                worksheet.Cells[i + 1, j + 1].Value = dr[j];
            }
            i++;
        }
        package.Save();//保存excel
    }
}