Databases
StackOverflow2013
Postsdbo
POHow to retrieve Tab names from excel sheet using OpenXML
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to retrieve Tab names from excel sheet using OpenXML
    text
    copied!<p>I have a spreadsheet document that has 182 columns in it. I need to place the spreadsheet data into a data table, tab by tab, but i need to find out as I'm adding data from each tab, what is the tab name, and add the tab name to a column in the data table. </p> <p>This is how I set up the data table. </p> <p>I then loop in the workbook and drill down to the <code>sheetData</code> object and walk through each row and column, getting cell data. </p> <pre><code>DataTable dt = new DataTable(); for (int i = 0; i &lt;= col.GetUpperBound(0); i++) { try { dt.Columns.Add(new DataColumn(col[i].ToString(), typeof(string))); } catch (Exception e) { MessageBox.Show("Uploader Error" + e.ToString()); return null; } } dt.Columns.Add(new DataColumn("SheetName", typeof(string))); </code></pre> <p>However at the end of the string array that I use for the Data Table, I need to add the tab name. How can I find out the tab name as I'm looping in the sheet in Open XML? </p> <p>Here is my code so far: </p> <pre><code>using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(Destination, false)) { WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart; Workbook workbook = spreadSheetDocument.WorkbookPart.Workbook; Sheets sheets = spreadSheetDocument .WorkbookPart .Workbook .GetFirstChild&lt;DocumentFormat.OpenXml.Spreadsheet.Sheets&gt;(); OpenXmlElementList list = sheets.ChildElements; foreach (WorksheetPart worksheetpart in workbook.WorkbookPart.WorksheetParts) { Worksheet worksheet = worksheetpart.Worksheet; foreach (SheetData sheetData in worksheet.Elements&lt;SheetData&gt;()) { foreach (Row row in sheetData.Elements()) { string[] thisarr = new string[183]; int index = 0; foreach (Cell cell in row.Elements()) { thisarr[(index)] = GetCellValue(spreadSheetDocument, cell); index++; } thisarr[182] = ""; //need to add tabname here if (thisarr[0].ToString() != "") { dt.Rows.Add(thisarr); } } } } } return dt; </code></pre> <p>Just a note: I did previously get the tab names from the InnerXML property of "list" in </p> <pre><code>OpenXmlElementList list = sheets.ChildElements; </code></pre> <p>however I noticed as I'm looping in the spreadsheet it does not get the tab names in the right order. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload