altandogan altandogan - 1 month ago 5
C# Question

Shared String Table return null when reading created excel (Open xml)

I am creating excel file using open xml sdk. And I want to read it using open xml. When i want to read it i am getting error at this line

SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();it return null


if I open excel file and save again.It creates shared string table and it run.

Reading from excel

#region OpenFile
public void OpenFile(string directory)
{
try
{
fs = new FileStream(directory, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
doc = SpreadsheetDocument.Open(fs, false);
}
catch (FileNotFoundException ex)
{
string exception = string.Format("Doya bulunamadı{0}", directory);
throw new ApplicationException(exception);
}
}
#endregion

#region GetWorkSheet
public void AssignWorkSheet(int sheetID)
{
if (fs == null || doc == null)
throw new ApplicationException("Dosya açılamadı");

WorkbookPart workbookPart = doc.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
sst = sstpart.SharedStringTable;

var workbook = workbookPart.Workbook;
var sheets = workbook.Descendants<Sheet>();
var sheetINVOICE = sheets.ElementAt(sheetID);
var worksheetPartINVOICE = (WorksheetPart)workbookPart.GetPartById(sheetINVOICE.Id);
WorkSheet = worksheetPartINVOICE.Worksheet;
//return sheetInvoice;
}
#endregion


It creates an write some text and numeric value with below code

var fileName = string.Format(@"C:\Sonuc\{0}\{1}.xlsx", systemType.ToString(), systemTypeEnum.ToString() + "_" + fileType.ToString());
SpreadsheetDocument xl = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();

WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();

FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();

SheetData sd = new SheetData();

//int rowCount = 1;
DocumentFormat.OpenXml.UInt32Value rowCount = 1;



foreach (var item in resultList)
{
rowCount = rowCount + 1;
Row r3 = new Row() { RowIndex = rowCount };
Cell c6 = new Cell();
c6.DataType = CellValues.String;
c6.CellValue = new CellValue(item.BusinessPartner);
r3.Append(c6);

Cell c7 = new Cell();
c7.DataType = CellValues.Number;
c7.CellValue = new CellValue(item.VKN);
r3.Append(c7);

Cell c8 = new Cell();
c8.DataType = CellValues.Number;
c8.CellValue = new CellValue(item.InvoiceCount.ToString());
r3.Append(c8);

Cell c9 = new Cell();
c9.DataType = CellValues.Number;
c9.CellValue = new CellValue(item.TaxTotalAmount.ToString());
r3.Append(c9);
sd.Append(r3);

}

ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();

Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Sheet1";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);

xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();

Answer

By default, MS EXCEL saves String values using the SharedStringTablePart. It's why your code seems working when you open and save the file with MS EXCEL.

But here, when you create the file, you define the Cell.Datatype to CellValues.String (instead of CellValues.SharedString)

c6.DataType = CellValues.String;

When Cell.Datatype is CellValues.String, you must read the value by reading the Cell.CellValue property.


To save the String values using SharedStringPart, please refer to online documentation: