ASP. NET (C #) reads Excel files

. Xls Format Office2003 and earlier versions
. Xlsx format Office2007 and later versions
. Csv format string text separated by commas (, you can save the preceding two file types as this format)
Two different methods are used to read the first two formats and the last one.

See the program below:
Page front-end:

Copy codeThe Code is as follows: <div> <% -- the File Upload control is used to upload the file to be read and obtain the file information through this control -- %>
<Asp: FileUpload ID = "fileSelect" runat = "server"/>
<% -- Click this button to execute the read Method -- %>
<Asp: Button ID = "btnRead" runat = "server" Text = "ReadStart"/>
</Div>

Background code:

Copy codeThe Code is as follows: // declare a variable (attribute)
String currFilePath = string. Empty; // full path of the file to be read
String currFileExtension = string. Empty; // File Extension

// Page_Load event registration button click event
Protected void Page_Load (object sender, EventArgs e)
{
This. btnRead. Click + = new EventHandler (btnRead_Click );
}

// Click the event button. // The three methods in the event are shown below.
Protected void btnRead_Click (object sender, EventArgs e)
{
Upload (); // File Upload Method
If (this. currFileExtension = ". xlsx" | this. currFileExtension = ". xls ")
{
DataTable dt = ReadExcelToTable (currFilePath); // read the excelfile (.xlsand .xlsx format)
}
Else if (this. currFileExtension = ". csv ")
{
DataTable dt = ReadExcelWidthStream (currFilePath); // read. CSV format file
}
}

The following lists the three methods in the button-click event.

Copy codeThe Code is as follows: // <summary>
/// Upload the file to the temporary directory
/// </Ummary>
Private void Upload ()
{
HttpPostedFile file = this. fileSelect. PostedFile;
String fileName = file. FileName;
String tempPath = System. IO. Path. GetTempPath (); // obtain the temporary file Path of the System.
FileName = System. IO. Path. GetFileName (fileName); // get the file name (without Path)
This. currFileExtension = System. IO. Path. GetExtension (fileName); // get the file extension
This. currFilePath = tempPath + fileName; // obtain the uploaded file path record to the previously declared global variable
File. SaveAs (this. currFilePath); // upload
}

/// <Summary>
/// How to read Excel files in xls \ xlsx format
/// </Ummary>
/// <Param name = "path"> full path of the Excel file to be read </param>
/// <Returns> </returns>
Private DataTable ReadExcelToTable (string path)
{
// Connection string
String connstring = "Provider = Microsoft. ACE. OLEDB.12.0; Data Source = "+ path +"; Extended Properties = 'excel 8.0; HDR = NO; IMEX = 1 ';"; // no extra spaces are allowed for Office 07 and later versions, and note the semicolon
// String connstring = Provider = Microsoft. JET. OLEDB.4.0; Data Source = "+ path +"; Extended Properties = 'excel 8.0; HDR = NO; IMEX = 1 ';"; // for versions earlier than Office 07, the connection string is not used because I use Office2010. You can select the connection string or program based on your own situation to determine which connection string to use.
Using (OleDbConnection conn = new OleDbConnection (connstring ))
{
Conn. Open ();
DataTable sheetsName = conn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, new object [] {null, "Table"}); // obtain the names of all sheets
String firstSheetName = sheetsName. Rows [0] [2]. ToString (); // obtain the name of the first sheet.
String SQL = string. Format ("SELECT * FROM [{0}], firstSheetName); // query a string
OleDbDataAdapter ada = new OleDbDataAdapter (SQL, connstring );
DataSet set = new DataSet ();
Ada. Fill (set );
Return set. Tables [0];

}
}

/// <Summary>
/// How to read an Excel file in csv format
/// </Ummary>
/// <Param name = "path"> full path of the Excel file to be read </param>
/// <Returns> </returns>
Private DataTable ReadExcelWithStream (string path)
{
DataTable dt = new DataTable ();
Bool isDtHasColumn = false; // mark whether the DataTable has generated a column
StreamReader reader = new StreamReader (path, System. Text. Encoding. Default); // data stream
While (! Reader. EndOfStream)
{
String meaage = reader. ReadLine ();
String [] splitResult = message. Split (new char [] {','}, StringSplitOption. None); // read a row separated by commas and saved to the array
DataRow row = dt. NewRow ();
For (int I = 0; I <splitResult. Length; I ++)
{
If (! IsDtHasColumn) // if no column is generated
{
Dt. Columns. Add ("column" + I, typeof (string ));
}
Row [I] = splitResult [I];
}
Dt. Rows. Add (row); // Add a row
IsDtHasColumn = true; // when the first row is read, the column is no longer generated when the row is marked as an existing column and then read.
}
Return dt;
}