Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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();
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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:

share|improve this answer
    
Thanks Chris, i have read and solved my problem by reading your first link –  user990513 Oct 21 at 10:16
    
@user990513 you are welcome. Glad I could help. –  Chris Oct 21 at 18:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.