how to get 1st column value in spreadsheet Document in mvc

Kalyani Shevale

7y3.5kLearn iOS Programming

I have created an Open XML document file

in Existing Spreadsheet document open then read the 1st column value in this file.

SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(srcFile, true);
WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild();
string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart);
uint sheetId = 1;
SheetData sheetData = newWorksheetPart.Worksheet.GetFirstChild();
IEnumerable worksheetPart = workbookPart.WorksheetParts;
int rocount=0;
Sheet sheet = workbookPart.Workbook.Descendants()
.Where(s => s.Name == "Survey Details").FirstOrDefault();
foreach (WorksheetPart WSP in worksheetPart)
{
//find sheet data
IEnumerable sheetData1 = WSP.Worksheet.Elements();
// Iterate through every sheet inside Excel sheet
foreach (SheetData SD in sheetData1)
{
IEnumerable row = SD.Elements(); // Get the row IEnumerator
IEnumerable column = SD.Elements();
var d = column.Count();
rocount=(row.Count()); // Will give you the count of rows
}
}

how to read column value in c#

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo7y ago
You can get from the sheet of the bellow code
Microsoft.Office.Interop.Excel.Application xlApp;
           Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
           Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
           Microsoft.Office.Interop.Excel.Range range;

           string str;
           int rCnt = 0;
           int cCnt = 0;
           string excelpath = fname;
           xlApp = new Microsoft.Office.Interop.Excel.Application();
           xlWorkBook = xlApp.Workbooks.Open(@excelpath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
           xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Kalyani Shevale7y ago
https://docs.microsoft.com/en-us/office/open-xml/how-to-retrieve-the-values-of-cells-in-a-spreadsheet#getcellvalue-method

this link used but same issue
Sreekanth Reddy7y ago
Read the below URL :

https://docs.microsoft.com/en-us/office/open-xml/how-to-retrieve-the-values-of-cells-in-a-spreadsheet#getcellvalue-method
Kalyani Shevale7y ago
Use InnerText property and check once please.
Instead of
text = c.CellValue.Text;
Use
text = c.CellValue.InnerText;

this code is not useful
Sreekanth Reddy7y ago
Hi Kalyani,

Use InnerText property and check once please.
Instead of
text = c.CellValue.Text;
Use
text = c.CellValue.InnerText;
Accept answer if it helps. Else come up with your query.
Thanks.
Kalyani Shevale7y ago
hi sanwar

ur code through only get cell value I want Actual value .
Sreekanth Reddy7y ago
Hi Kalyani,

Imports System
Imports System.Linq
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements().First();
string text;
foreach (Row r in sheetData.Elements())
{
foreach (Cell c in r.Elements())
{
text = c.CellValue.Text;
lstbox1.Items.Add(text);
}
}
Found in the following URL :
https://docs.microsoft.com/en-us/office/open-xml/how-to-parse-and-read-a-large-spreadsheet
If it helps mark as answer. Else come up with your query.
Thank you.

Kalyani Shevale7y ago

this data in Excel file.this file open SpreadsheetDocument in c#.

Conducted Survey Id	Survey Name
19	FIRESTROM
21	FIRESTROM
24	FIRESTROM
30	FIRESTROM
32	FIRESTROM
36	FIRESTROM
38	FIRESTROM
39	FIRESTROM
40	FIRESTROM
42	FIRESTROM
45	FIRESTROM
46	FIRESTROM
48	FIRESTROM
51	FIRESTROM
52	FIRESTROM
53	FIRESTROM
54	FIRESTROM
56	FIRESTROM
57	FIRESTROM
58	FIRESTROM
63	FIRESTROM
64	FIRESTROM
65	FIRESTROM
68	FIRESTROM
69	FIRESTROM
70	FIRESTROM
71	FIRESTROM
72	FIRESTROM
73	FIRESTROM
74	FIRESTROM
75	FIRESTROM
76	FIRESTROM
77	FIRESTROM
78	FIRESTROM
79	FIRESTROM
81	FIRESTROM
82	FIRESTROM
88	FIRESTROM
89	FIRESTROM
90	FIRESTROM
91	FIRESTROM
93	FIRESTROM
94	FIRESTROM
95	FIRESTROM
97	FIRESTROM
99	FIRESTROM
100	FIRESTROM
102	FIRESTROM
103	FIRESTROM
105	FIRESTROM

I want this Actual data in one List in c#

Sanwar Ranwa7y ago
how to get Actual data in Spreadsheet document ?. can you explain in details
Kalyani Shevale7y ago
@sanwar
you have provded link through only cell value get...how to get Actual data in Spreadsheet document.
Kalyani Shevale7y ago
we cant use Excel .only spreadsheet or workbookpart used
Sanwar Ranwa7y ago
Hello

Please check this

https://documentation.devexpress.com/OfficeFileAPI/12086/Spreadsheet-Document-API/Examples/Rows-and-Columns/How-to-Access-a-Row-or-Column

https://help.syncfusion.com/file-formats/xlsio/worksheet-rows-and-columns-manipulation