如何使用C#获取上传的excel文件的sheetname？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17358801/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get sheetname of the uploaded excel file using C#?
提问by prabu R
I would like to get the sheet name of the uploaded excel file using C#code. The file may be in .xlsor .xlsxformat. The Code I have used is as follows:
我想使用C#代码获取上传的 excel 文件的工作表名称。该文件可能是.xls或.xlsx格式。我使用的代码如下：
protected void btnGenerateCSV_Click(object sender, EventArgs e)
{
string sourceFile = ExcelFileUpload.PostedFile.FileName;
string worksheetName = ?? //(How to get the first sheetname of the uploaded file)
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
if (sourceFile.Contains(".xlsx"))
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sourceFile + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
try
{
conn = new OleDbConnection(strConn);
conn.Open();
cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);
cmd.CommandType = CommandType.Text;
wrtr = new StreamWriter(targetFile);
da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int x = 0; x < dt.Rows.Count; x++)
{
string rowString = "";
for (int y = 0; y < dt.Columns.Count; y++)
{
rowString += "\"" + dt.Rows[x][y].ToString() + "\",";
}
wrtr.WriteLine(rowString);
}
}
catch (Exception exp)
{
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
cmd.Dispose();
da.Dispose();
wrtr.Close();
wrtr.Dispose();
}
}
- (How to get the first
sheetnameof the uploaded file)string worksheetName = ??
- （如何获取第一个
sheetname上传的文件）string worksheetName = ??
采纳答案by Karthik
I use this to get sheet names from a .xlsxfile and loop through all the names to read sheets one by one.
我使用它从.xlsx文件中获取工作表名称并遍历所有名称以一张一张地读取工作表。
OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0 xml;HDR=YES;'");
connection.Open();
DataTable Sheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach(DataRow dr in Sheets.Rows)
{
string sht = dr[2].ToString().Replace("'", "");
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from [" + sht + "]", connection);
}
回答by bombaatdev
DataTable Sheets = oleConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for(int i=0;i<Sheets.Rows.Count;i++)
{
string worksheets= Sheets.Rows[i]["TABLE_NAME"].ToString();
string sqlQuery = String.Format("SELECT * FROM [{0}]", worksheets);
}
回答by Zhangcong
If the Excel is too big, This code will waste a lot of time in(conn.open()). Use Openxml will be better(use less time),but if the Excel is Open---Using openxml to read will have the exception but oldbhelper wile have no exception. My english is pool , sorry.-----Chinese boy
如果Excel太大，这段代码会在(conn.open())中浪费很多时间。使用Openxml会更好（使用更少的时间），但如果Excel是Open的---使用openxml读取会有异常但oldbhelper wile没有异常。我的英语是游泳池，对不起。-----中国男孩
回答by Daya Paari
I use Microsoft excel library Microsoft.Office.Interop.Excel. Then you can use index to get the worksheet name as following.
我使用 Microsoft excel 库 Microsoft.Office.Interop.Excel。然后您可以使用索引来获取工作表名称，如下所示。
string path = @"C\Desktop\MyExcel.xlsx" //Path for excel
using Excel = Microsoft.Office.Interop.Excel;
xlAPP = new Excel.Application();
xlAPP.Visible = false;
xlWbk = xlAPP.Workbooks.Open(path);
string worksheetName = xlWbk.Worksheets.get_Item(1).Name //pass Index here. Reemember that index starts from 1.
xlAPP.Quit();
releaseObject(xlWbk);
releaseObject(xlAPP);
//Always handle unmanaged code.
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();
}
}
