如何在C＃中导入excel 2013-2016？ [英] How about import excel 2013-2016 in C# ?

问题描述

导入excel 2013-2016怎么样？



我尝试过：



导入excel 2013-2016怎么样？

How about import excel 2013-2016 ?

What I have tried:

How about import excel 2013-2016 ?

推荐答案

做自己的研究怎么样。



请参阅使用MS Excel（xls） / xlsx）使用MDAC和Oledb [ ^ ]。

How about doing your own research.

See Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^].

您有两个选项，使用Microsoft Office Interop

You have two options, use the Microsoft Office Interop

private void OpenExcelFile(string filePath)
       {
           Excel.Application xlApp ;
           Excel.Workbook xlWorkBook ;
           Excel.Worksheet xlWorkSheet ;
           object misValue = System.Reflection.Missing.Value;

           xlApp = new Excel.Application();
           xlWorkBook = xlApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
           xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

           MessageBox.Show(xlWorkSheet.get_Range("A1","A1").Value2.ToString());

           xlWorkBook.Close(true, misValue, misValue);
           xlApp.Quit();

           releaseObject(xlWorkSheet);
           releaseObject(xlWorkBook);
           releaseObject(xlApp);
       }

       private void releaseObject(object obj)
       {
           try
           {
               System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
               obj = null;
           }
           catch (Exception ex)
           {
               obj = null;
               MessageBox.Show("Unable to release the Object " + ex.ToString());
           }
           finally
           {
               GC.Collect();
           }
       }

或者您可以使用Open解析excel文件作为打开的xml文档XML SDK：



使用Open XML SDK 2.0生成Excel 2010工作簿 [ ^ ]

下面的代码仅在您在机器上安装了MS Office或者您需要时才有效在您的计算机上安装AccessDatabaseEngine_x64驱动程序工具。 AccessDatabaseEngine_x64安装文件很容易获得且免费。





试试以下代码我认为这对你有用





aspx代码



< form id =form1runat =server>

< DIV>

< div style =float：left; width：100％; text-align：right>

< asp：Button ID =BtnDownloadrunat =serverText =DownloadOnClick =BtnDownload_Click/> < / div>

< div style =float：left; width：100％>

< asp：FileUpload ID =FileUpload1runat =server/> & nbsp;& nbsp;

< asp：Button ID =BtnUploadrunat =serverText =UploadOnClick =BtnUpload_Click/>

< p>& nbsp;< / p>< p>& nbsp;< / p>< p>& nbsp;< / p>

下载示例文件< a href =SampleFile.xlsx>点击此处< / a>

< p>& nbsp;< / p>



< / div>

< div style =float：left; width：100％>

< asp：Label ID =lblStatusrunat =serverText =ForeColor =Green>< / asp：Label>

< asp：Label ID =lblErrorMsgrunat =serverText =ForeColor =Red>< / asp：Label>

< ; / div>

< / div>



< / form>









.cs文件代码



protected void BtnUpload_Click（object sender，EventArgs e）

{



if（FileUpload1.HasFile ）

{

试试

{

lblStatus.Text =;

lblErrorMsg.Text =;

string filename = Path.GetFileName（FileUpload1.FileName）;

FileUpload1.SaveAs（Server.MapPath（〜/ MyFolder /）+ filename）;

lblStatus.Text =文件已更新：+ DateTime.Now;

BtnUpload.Enabled = false;

ReadExcelToTable（filename）; < br $>


}

catch（例外情况）

{

lblErrorMsg.Text = 无法上传文件。 + ex.Message;

lblStatus.Text =;

BtnUpload.Enabled = true;

}

}

其他

{

lblErrorMsg.Text =请选择文件。;

lblStatus.Text =;

BtnUpload.Enabled = true;

}



}













private void ReadExcelToTable（string strFileName）

{



string connstring;



string strPath = Server.MapPath（〜/ MyFolder /）; // System.Configuration.ConfigurationManager.AppSettings.Get（File_Path）。ToString（）;



if（！strFileName.Contains（。xlsx））

{

connstring =Provider = Microsoft.JET.OLEDB.4.0; Data Source = + strPath + strFileName +; Exten ded Properties ='Excel 8.0; HDR = YES; IMEX = 1';; //此连接字符串适用于Office 2007和旧版本。我们可以根据Office版本或我们的程序选择最合适的连接字符串。

}

else

{

// connstring =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ strPath + strFileName +; Extended Properties ='Excel 8.0; HDR = NO; IMEX = 1';; // Office 2007和上一版本中无法显示额外的空白区域。我们需要注意分号。

connstring =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ strPath + strFileName +; Extended Properties ='Excel 12.0; HDR =是; IMEX = 1';

}

使用（OleDbConnection conn = new OleDbConnection（connstring））

{

conn.Open（）;

DataTable sheetsName = conn.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，new object [] {null，null，null，Table}）; //获取所有表格名称

string firstSheetName = sheetsName.Select（TABLE_NAME ='Sheet1

Below code only works when you have MS office installed on the machine or you need to install AccessDatabaseEngine_x64 drivers tool on your machine. AccessDatabaseEngine_x64 setup file is easily available & it is free.


Try out below code i think this is useful to you


aspx code

<form id="form1" runat="server">
<div>
<div style="float:left;width:100%;text-align:right">
<asp:Button ID="BtnDownload" runat="server" Text="Download" OnClick="BtnDownload_Click" /> </div>
<div style="float:left;width:100%">
<asp:FileUpload ID="FileUpload1" runat="server" /> &nbsp;&nbsp;
<asp:Button ID="BtnUpload" runat="server" Text="Upload" OnClick="BtnUpload_Click" />
<p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p>
Download sample file <a href="SampleFile.xlsx">Click Here</a>
<p>&nbsp;</p>

</div>
<div style="float:left;width:100%">
<asp:Label ID="lblStatus" runat="server" Text="" ForeColor="Green"></asp:Label>
<asp:Label ID="lblErrorMsg" runat="server" Text="" ForeColor="Red"></asp:Label>
</div>
</div>

</form>




.cs file code

protected void BtnUpload_Click(object sender, EventArgs e)
{

if (FileUpload1.HasFile)
{
try
{
lblStatus.Text = "";
lblErrorMsg.Text = "";
string filename = Path.GetFileName(FileUpload1.FileName);
FileUpload1.SaveAs(Server.MapPath("~/MyFolder/") + filename);
lblStatus.Text = "File Updated:" + DateTime.Now;
BtnUpload.Enabled = false;
ReadExcelToTable(filename);

}
catch (Exception ex)
{
lblErrorMsg.Text = "The file could not be uploaded." + ex.Message;
lblStatus.Text = "";
BtnUpload.Enabled = true;
}
}
else
{
lblErrorMsg.Text = "Please select file.";
lblStatus.Text = "";
BtnUpload.Enabled = true;
}

}






private void ReadExcelToTable(string strFileName)
{

string connstring;

string strPath = Server.MapPath("~/MyFolder/");//System.Configuration.ConfigurationManager.AppSettings.Get("File_Path").ToString();

if (!strFileName.Contains(".xlsx"))
{
connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + strPath + strFileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; //This connection string is appropriate for Office 2007 and the older version. We can select the most suitable connection string according to Office version or our program.
}
else
{
//connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + strFileName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Extra blank space cannot appear in Office 2007 and the last version. And we need to pay attention on semicolon.
connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + strFileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
}
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //Get All Sheets Name
string firstSheetName = sheetsName.Select("TABLE_NAME='Sheet1

这篇关于如何在C＃中导入excel 2013-2016？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！