c# – 如何导入HTML格式的Excel

我已经使用HttpContext从数据库导出数据,格式为table,tr和td.我想读同一个文件并转换成datatable.

<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='HTML Import;HDR={1};IMEX=1'" />
 
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'" />
 
    private DataTable GetTableFromExcel()
    {
        DataTable dt = new DataTable();
 
        try
        {
            if (exclFileUpload.HasFile)
            {
                string FileName = Path.GetFileName(exclFileUpload.PostedFile.FileName);
                string Extension = Path.GetExtension(exclFileUpload.PostedFile.FileName);
                string FolderPath = Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
                //string NewFileName = string.Format("{0}_{1}",DateTime.Now.ToString().Replace("/","").Replace(" ","").Replace(":",""),FileName);
                string FilePath = Path.Combine(string.Format("{0}/{1}",FolderPath,FileName));
                exclFileUpload.SaveAs(FilePath);
                string conStr = "";
                switch (Extension)
                {
                    case ".xls": //Excel 97-03
                        conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                        break;
                    case ".xlsx": //Excel 07
                        conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                        break;
                }
                conStr = String.Format(conStr,FilePath,true);
                OleDbConnection connExcel = new OleDbConnection(conStr);
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oda = new OleDbDataAdapter();
 
                cmdExcel.Connection = connExcel;
 
                connExcel.Open();
                DataTable dtExcelSchema;
                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
                string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                connExcel.Close();
 
                connExcel.Open();
                cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
                oda.SelectCommand = cmdExcel;
                oda.Fill(dt);
                connExcel.Close();
                File.Delete(FilePath);
 
            }
        }
        catch (Exception ex)
        {
 
        }
        return dt;
    }

当使用第二个连接字符串时,我收到错误“外部表不是预期格式的connection.Open()”.但是当使用第一个时,我在阅读工作表名称时会收到错误.

请告诉我如何从Excel中读取表格或直接从Excel中读取数据.

解决方法

我认为这个 Third party dll-(ExcellDataReader)可能有助于解决你的问题.

FileStream stream = File.Open(filePath,FileMode.Open,FileAccess.Read);
 
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
 
//5. Data Reader methods
while (excelReader.Read())
{
    //excelReader.GetInt32(0);
}
 
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

c# – 如何导入HTML格式的Excel

解决方法

猜你在找的C#相关文章