如何在C#中导入excel 2013-2016? [英] How about import excel 2013-2016 in C# ?
本文介绍了如何在C#中导入excel 2013-2016?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
导入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" />
<asp:Button ID="BtnUpload" runat="server" Text="Upload" OnClick="BtnUpload_Click" />
<p> </p><p> </p><p> </p>
Download sample file <a href="SampleFile.xlsx">Click Here</a>
<p> </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屋!
查看全文