How C # Imports and exports and processes Excel files

OLE DB is Microsoft's strategic low-level application interface to different data sources. Supports a wide variety of databases, and the input and output of DBF files in this client also uses this database API. Use OLE DB to remember to add the namespace:

Using System.Data.OleDb;

The 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 xlsx files varies by requirements and is not addressed in this way. During the input process, the data in the file is converted into the format of the DataTable data type, which is a grid virtual table that temporarily holds the data, which is very convenient to handle.

xlsx file output: xlsx file output process is not very smooth, has been used in three different ways.

The first way is naturally OLE DB, the beginning and the beginning of it. But the real use of the process found that this method not only requires the program to run the machine must install Excel, and the Excel version is very high, if the deletion of the registry is very easy to self-defeating, I changed the registry directly to play bad Excel. In fact, we cannot guarantee that the corresponding version of Excel is installed on the customer's machine, so if you use the OLE DB output file, this client's compatibility will be very poor.

After some communication and struggle decisions or a different approach, decide to switch to a third-party component. Because Java uses POI, the. NET version of the POI project is used directly here, Npoi. A lot of online tutorials, very good implementation, but after testing, this way to large file input and output support is not very good, the processing of the demo file is not a problem, but instead of the real customer provides 100,000 rows or even millions of rows of files, the client directly in the process of the output collapsed.

After querying the information, netizens are advised to use Epplus for large files. The tutorial is also a catch a lot, I did not continue to increase the size of the file test, but the customer gave us a total of 10 files to pass the test, basically are millions below.

On the Code bar:

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);//Create 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 ();//Saving Excel    }}

