none
pb for writing data in both Z and AA columns RRS feed

  • Question

  • Hello,

    I manage to generate an Excel file with OpenXml but I have a blocking issue in one case.
    Indeed, I can write data either : only between columns A and Z or between AA and above.
    If I create a very simple sheet with a value in Z1 and a value in AA1, then my Excel file refuses to open and I have the error like there is unreadable content.

    But, when I look at the content of the 2 generated files (one that works, the other not), I don't see any structure difference so I am lost...
    For information, in order to look into details the content of my xlsx file, I move the extension from xlsx to zip, unzip the file, and I then have access to all the architecture of xml files.

    So any idea why and how to workaround please?

    Thursday, February 19, 2015 2:29 PM

Answers

  • Hi,

    There was a bug in the samples actually. In order to go around it, I replaced in the method CreateCell "if (string.Compare(cell.CellReference.Value, address, true) > 0)" by :

    if (fromBase26(Regex.Replace(cell.CellReference.Value, @"[\d-]", string.Empty)) > fromBase26(Regex.Replace(address, @"[\d-]", string.Empty)))

    and define:

    //Hexavigesimal (Excel Column Name to Number) - Bijective
            private int fromBase26(string colName)
            {
                colName = colName.ToUpper();
                int decimalValue = 0;
                for (int i = 0; i < colName.Length; i++)
                {
                    decimalValue *= 26;
                    decimalValue += (colName[i] - 64);
                }
                return decimalValue;
            }

    Tuesday, March 17, 2015 9:51 AM

All replies

  • As an additional information, here is the code I use to create my excel:

    string filename = string.Format("{0}BulkResult_{1}{2:00}{3:00}{4:00}{5:00}{6:00}{7:000}.xlsx", System.IO.Path.GetTempPath(), DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, DateTime.UtcNow.Hour, DateTime.UtcNow.Minute, DateTime.UtcNow.Second, DateTime.UtcNow.Millisecond);
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();
    workbookpart.Workbook.Save();
    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart1 = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart1.Worksheet = new Worksheet(new SheetData());
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    string worksheetDivision = "Division";
    Sheet sheet1 = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart1), SheetId = 1, Name = worksheetDivision };
    sheets.Append(sheet1); 
    workbookpart.Workbook.Save();
    //Handling SharedStringTable
    WorkbookPart wbPart = spreadsheetDocument.WorkbookPart;
    var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
    if (stringTablePart == null)
           stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
    stringTablePart.SharedStringTable = new SharedStringTable();
    //Handling Styles
    // Stylesheet
    WorkbookStylesPart workbookStylesPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet = createStyleSheet(); 
    workbookStylesPart.Stylesheet.Save();
    //INSERT MY DATA HERE
    wbPart = spreadsheetDocument.WorkbookPart;
    UInt32Value normalFontIndex = 0; 
    UInt32Value boldFontIndex = 7;
    UpdateValue(wbPart, worksheetDivision, "Z1", "toto", boldFontIndex, true);
    UpdateValue(wbPart, worksheetDivision, "AA1", "titi", normalFontIndex, true);
    spreadsheetDocument.Close();
    System.Diagnostics.Process.Start(filename);
    
    private Stylesheet createStyleSheet()
            {             
                Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
    
                DocumentFormat.OpenXml.Spreadsheet.Fonts fonts1 = new DocumentFormat.OpenXml.Spreadsheet.Fonts() { Count = (UInt32Value)1U, KnownFonts = true };
    
                //Normal Font
                DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.Font();
                DocumentFormat.OpenXml.Spreadsheet.FontSize fontSize1 = new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11D };
                DocumentFormat.OpenXml.Spreadsheet.Color color1 = new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = (UInt32Value)1U };
                FontName fontName1 = new FontName() { Val = "Calibri" };
                FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
                FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
    
                font1.Append(fontSize1);
                font1.Append(color1);
                font1.Append(fontName1);
                font1.Append(fontFamilyNumbering1);
                font1.Append(fontScheme1);
                fonts1.Append(font1);
    
                //Bold Font
                DocumentFormat.OpenXml.Spreadsheet.Font bFont = new DocumentFormat.OpenXml.Spreadsheet.Font();                     
                DocumentFormat.OpenXml.Spreadsheet.FontSize bfontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11D };
                DocumentFormat.OpenXml.Spreadsheet.Color bcolor = new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = (UInt32Value)1U };
                FontName bfontName = new FontName() { Val = "Calibri" };
                FontFamilyNumbering bfontFamilyNumbering = new FontFamilyNumbering() { Val = 2 };
                FontScheme bfontScheme = new FontScheme() { Val = FontSchemeValues.Minor };
                Bold bFontBold = new Bold();
    
                bFont.Append(bfontSize);
                bFont.Append(bcolor);
                bFont.Append(bfontName);    
                bFont.Append(bfontFamilyNumbering);
                bFont.Append(bfontScheme);
                bFont.Append(bFontBold);
    
                fonts1.Append(bFont);
    
                Fills fills1 = new Fills() { Count = (UInt32Value)5U };
    
                // FillId = 0
                Fill fill1 = new Fill();
                PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
                fill1.Append(patternFill1);
    
                // FillId = 1
                Fill fill2 = new Fill();
                PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };             
                fill2.Append(patternFill2);
    
                // FillId = 2,RED             
                Fill fill3 = new Fill();             
                PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };             
                ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "FFB6C1" };             
                BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };             
                patternFill3.Append(foregroundColor1);             
                patternFill3.Append(backgroundColor1);             
                fill3.Append(patternFill3);
    
                // FillId = 3,GREEN             
                Fill fill4 = new Fill();             
                PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };             
                ForegroundColor foregroundColor2 = new ForegroundColor() { Rgb = "90EE90" };             
                BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };             
                patternFill4.Append(foregroundColor2);             
                patternFill4.Append(backgroundColor2);             
                fill4.Append(patternFill4);
    
                // FillId = 4,YELLO             
                Fill fill5 = new Fill();             
                PatternFill patternFill5 = new PatternFill()  { PatternType = PatternValues.Solid };             
                ForegroundColor foregroundColor3 = new ForegroundColor() { Rgb = "FFFF00" };             
                BackgroundColor backgroundColor3 = new BackgroundColor() { Indexed = (UInt32Value)64U };             
                patternFill5.Append(foregroundColor3);             
                patternFill5.Append(backgroundColor3);             
                fill5.Append(patternFill5);
    
                // FillId = 5,RED and BOLD Text             
                Fill fill6 = new Fill();             
                PatternFill patternFill6 = new PatternFill() { PatternType = PatternValues.Solid };             
                ForegroundColor foregroundColor4 = new ForegroundColor() { Rgb = "FFB6C1" };             
                BackgroundColor backgroundColor4 = new BackgroundColor() { Indexed = (UInt32Value)64U };             
                Bold bold1 = new Bold();             
                patternFill6.Append(foregroundColor4);             
                patternFill6.Append(backgroundColor4);             
                fill6.Append(patternFill6);
    
                fills1.Append(fill1);             
                fills1.Append(fill2);             
                fills1.Append(fill3);             
                fills1.Append(fill4);             
                fills1.Append(fill5);
                fills1.Append(fill6);
    
                Borders borders1 = new Borders() { Count = (UInt32Value)1U };
    
                Border border1 = new Border();             
                LeftBorder leftBorder1 = new LeftBorder();             
                RightBorder rightBorder1 = new RightBorder();            
                TopBorder topBorder1 = new TopBorder();             
                BottomBorder bottomBorder1 = new BottomBorder();             
                DiagonalBorder diagonalBorder1 = new DiagonalBorder();
    
                border1.Append(leftBorder1);             
                border1.Append(rightBorder1);             
                border1.Append(topBorder1);             
                border1.Append(bottomBorder1);             
                border1.Append(diagonalBorder1);
    
                borders1.Append(border1);
    
                CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };             
                CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
    
                cellStyleFormats1.Append(cellFormat1);
    
                CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };             
                CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };             
                CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };             
                CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };             
                CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };             
                CellFormat cellFormat6 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };             
                CellFormat cellFormat7 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };             
                CellFormat cellFormat8 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
                CellFormat cellFormat9 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
    
                cellFormats1.Append(cellFormat2);             
                cellFormats1.Append(cellFormat3);             
                cellFormats1.Append(cellFormat4);             
                cellFormats1.Append(cellFormat5);             
                cellFormats1.Append(cellFormat6);             
                cellFormats1.Append(cellFormat7);
                cellFormats1.Append(cellFormat8);
                cellFormats1.Append(cellFormat9);
    
                CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };             
                CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
    
                cellStyles1.Append(cellStyle1);             
                DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };             
                TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
    
                stylesheet1.Append(fonts1);             
                stylesheet1.Append(fills1);             
                stylesheet1.Append(borders1);             
                stylesheet1.Append(cellStyleFormats1);             
                stylesheet1.Append(cellFormats1);             
                stylesheet1.Append(cellStyles1);             
                stylesheet1.Append(differentialFormats1);             
                stylesheet1.Append(tableStyles1);
    
                return stylesheet1;         
            }
    
            // Given a Worksheet and an address (like "AZ254"), either return a cell reference, or 
            // create the cell reference and return it.
            private Cell InsertCellInWorksheet(Worksheet ws, string addressName)
            {
                SheetData sheetData = ws.GetFirstChild<SheetData>();
                Cell cell = null;
    
                UInt32 rowNumber = GetRowIndex(addressName);
                Row row = GetRow(sheetData, rowNumber);
    
                // If the cell you need already exists, return it.
                // If there is not a cell with the specified column name, insert one.  
                Cell refCell = row.Elements<Cell>().
                    Where(c => c.CellReference.Value == addressName).FirstOrDefault();
                if (refCell != null)
                {
                    cell = refCell;
                }
                else
                {
                    cell = CreateCell(row, addressName);
                }
                return cell;
            }
    
            private Cell CreateCell(Row row, String address)
            {
                Cell cellResult;
                Cell refCell = null;
    
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, address, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }
    
                cellResult = new Cell();
                cellResult.CellReference = address;
    
                row.InsertBefore(cellResult, refCell);
                return cellResult;
            }
    
            private Row GetRow(SheetData wsData, UInt32 rowIndex)
            {
                var row = wsData.Elements<Row>().
                Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
                if (row == null)
                {
                    row = new Row();
                    row.RowIndex = rowIndex;
                    wsData.Append(row);
                }
                return row;
            }
    
            private UInt32 GetRowIndex(string address)
            {
                string rowPart;
                UInt32 l;
                UInt32 result = 0;
    
                for (int i = 0; i < address.Length; i++)
                {
                    if (UInt32.TryParse(address.Substring(i, 1), out l))
                    {
                        rowPart = address.Substring(i, address.Length - i);
                        if (UInt32.TryParse(rowPart, out l))
                        {
                            result = l;
                            break;
                        }
                    }
                }
                return result;
            }
    
            public bool UpdateValue(WorkbookPart wbPart, string sheetName, string addressName, string value, UInt32Value styleIndex, bool isString)
            {
                value = value != null ? value : "";
                // Assume failure.
                bool updated = false;
    
                Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();
    
                if (sheet != null)
                {
                    Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
                    Cell cell = InsertCellInWorksheet(ws, addressName);
    
                    if (isString)
                    {
                        // Either retrieve the index of an existing string,
                        // or insert the string into the shared string table
                        // and get the index of the new item.
                        int stringIndex = InsertSharedStringItem(wbPart, value);
    
                        cell.CellValue = new CellValue(stringIndex.ToString());
                        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                    }
                    else
                    {
                        cell.CellValue = new CellValue(value);
                        cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                    }
    
                    if (styleIndex > 0)
                        cell.StyleIndex = styleIndex;
    
                    // Save the worksheet.
                    ws.Save();
                    updated = true;
                }
    
                return updated;
            }
    
            // Given the main workbook part, and a text value, insert the text into the shared
            // string table. Create the table if necessary. If the value already exists, return
            // its index. If it doesn't exist, insert it and return its new index.
            private int InsertSharedStringItem(WorkbookPart wbPart, string value)
            {
                value = value != null ? value : "";
                int index = 0;
                bool found = false;
                var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
    
                // If the shared string table is missing, something's wrong.
                // Just return the index that you found in the cell.
                // Otherwise, look up the correct text in the table.
                if (stringTablePart == null)
                {
                    // Create it.
                    stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
                }
    
                var stringTable = stringTablePart.SharedStringTable;
                if (stringTable == null)
                {
                    stringTable = new SharedStringTable();
                }
    
                // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
                foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
                {
                    if (item.InnerText == value)
                    {
                        found = true;
                        break;
                    }
                    index += 1;
                }
    
                if (!found)
                {
                    stringTable.AppendChild(new SharedStringItem(new Text(value)));
                    stringTable.Save();
                }
    
                return index;
            }
    
            // Used to force a recalc of cells containing formulas. The
            // CellValue has a cached value of the evaluated formula. This
            // will prevent Excel from recalculating the cell even if 
            // calculation is set to automatic.
            private bool RemoveCellValue(WorkbookPart wbPart, string sheetName, string addressName)
            {
                bool returnValue = false;
    
                Sheet sheet = wbPart.Workbook.Descendants<Sheet>().
                    Where(s => s.Name == sheetName).FirstOrDefault();
                if (sheet != null)
                {
                    Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
                    Cell cell = InsertCellInWorksheet(ws, addressName);
    
                    // If there is a cell value, remove it to force a recalc
                    // on this cell.
                    if (cell.CellValue != null)
                    {
                        cell.CellValue.Remove();
                    }
    
                    // Save the worksheet.
                    ws.Save();
                    returnValue = true;
                }
    
                return returnValue;
            }

    Thursday, February 19, 2015 4:16 PM
  • Hi jmclej

    My recommendation would be for you to open Excel and create a test workbook/worksheet. Just put some sample data in those adjacent columns, save, and close.

    Then download the Open XML SDK and use the Productivity Tool to open this workbook (do NOT rename to .zip). This can show you code that will successfully generate the workbook and should give you some clues. You can also use the Tool to compare Open XML of the workbook your code generates with the one you created as an end-user - it will highlight the differences.


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, February 19, 2015 7:00 PM
    Moderator
  • Hi jmclej,

    The suggestion provieded by Cindy is a good way for learning developing with Open XML.

    Also here is an anrticle that insert text into a cell based on the column name and row index:
    How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 20, 2015 2:47 AM
    Moderator
  • Hello,

    Thank you for your answers.
    The given example, as with a lot that I have found does not create a file, it opens an existing one and then do all sort of things. My issue here is to understand all the needed steps to create from scratch a document. Do you know of any link that provides such a basic need please? I had to build with several pieces of examples found on internet in order to achieve it but there must still be something wrong. Eventhough, the only issue I have is when I put text in both columns having one letter and on columns above (AQP for instance).
    I have also used the compare tool in the sdk and the only difference that I see (but you can actually look into the generated files here : http://www.developpez.net/forums/d1500582/dotnet/langages/csharp/probleme-openxml/ (I have posted the same issue on a french forum, but I could upload my excel files there)), it is in /xl/worksheets/sheet.xml where in OK.xlsx, I have first the index 0 (for V1) and then the index 1 (for W1). In NOTOK.xlsx, I have first the index 1 (for AA1) and then the index 0 (for Z1). I have tried to manually modify the order but I still get the same error.
    I have also tried to get rid of the creation of my stylesheet and to put a style index of 0 (the normal value by default), but I still have the same issue.
    Could someone try to actaully run the code I have put in my reply above and tell me if they encounter the same issue.
    Thank you very much in advance.
    Julien

    Friday, February 20, 2015 10:58 AM
  • Hi Julien,

    Seems that we need to register and login to that forum to download the file, you could upload files to OneDrive so that we can download it.

    By the way, have you tried Cindy's suggestion? As you know, it's a little bit complex when we use Open XML SDK to do a small change to the Excel workbook. The productivity tool provided by the SDK is a good tool to create a spread sheet. In order to know the details for how to create and modify Excel workbook from scratch, you need to firstly know the Office Open XML format and the OpenXML SDK object model, I recommend that you read the documents in MSDN if you want:

    Generating Excel 2010 Workbooks by using the Open XML SDK 2.0


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 23, 2015 10:14 AM
    Moderator
  • Hello,

    Here is a OneDrive link to the files : https://onedrive.live.com/redir?resid=F788D9B50CC0B52E!4056&authkey=!ADcFRD007T0ipmA&ithint=folder%2cxlsx . I have added as well a VS Project with my code trying 2 different ways to create my file. It uses as well the code I have pasted at the beginning of this thread.

    I have look at Cindty's suggestion yes, I went throw the code, but I need a generic way to generate my file with any data.

    Thanks for telling me if you see what is going wrong in my code so that there is an issue only when having data in both 1-letter size columns and above.

    Regards,

    Julien


    • Edited by jmclej Monday, March 2, 2015 10:32 AM
    Monday, March 2, 2015 10:21 AM
  • Hi Julien,

    I tryied the code you provied above. There is no error when we update a value in a worksheet, however if we update two values the issue could be reproduced.

    After the further investigation, I found the cell element on the sheet is in wrong order like figure below:

    I suggest that you compare the code sample provided by MSDN and above to see which line cause this issue. You may also consider use the sample provided by MSDN as a workaround.

    >>The given example, as with a lot that I have found does not create a file, it opens an existing one and then do all sort of things. My issue here is to understand all the needed steps to create from scratch a document. Do you know of any link that provides such a basic need please?<<

    If you want to create a spread sheet, you can get the sample code from link below:
    How to: Create a spreadsheet document by providing a file name (Open XML SDK)

    Also you can get more code samples about handling spreadsheets using Open XML from link below:
    Spreadsheets (Open XML SDK)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 3, 2015 8:23 AM
    Moderator
  • Hello,

    Indeed, I had already seen and told about this "disorder" thing in a previous comment. I have just no clue what in my code can handle this order. But as I said above is that even if, with notepad, I change this order to the expected one, I still have the same mistake.

    Regarding the sample codes you provide, I have tried them as well already but I get a nullpointer exception for sheets in InsertWorksheet method (it is in the project I have submitted above, in the commented part). I don't know if I have spent too much time on this and can't see the obvious but I am really stuck. Could someone try to put together the codes of the samples in order to handle the creation of an Excel file and directly insert values in it (in columns Z1 and AA1) to see if you manage to make it work ? Thanks you very much for your efforts. Hopefully the final working code will provide a more complete study case for OpenXML

    Regards,

    Julien

    Monday, March 9, 2015 12:52 PM
  • Even when I take exactly the same code as shown in How to: Insert text into a cell in a spreadsheet document (OpenXML SDK), but only want to add a second value in the problematic area (Z1 and AA1), then the document won't open without an error. I just changed this code inside InsertText method :

    // Insert the text into the SharedStringTablePart.
                int index1 = Utils.InsertSharedStringItem("toto1", shareStringPart);

    // Insert cell A1 into the new worksheet. Cell cell = InsertCellInWorksheet("A", 1, worksheetPart); // Set the value of cell A1. cell.CellValue = new CellValue(index.ToString()); cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

    to this code :

    // Insert the text into the SharedStringTablePart.
                int index1 = Utils.InsertSharedStringItem("toto1", shareStringPart);

    // Insert cell Z1 into the new worksheet. Cell cell1 = Utils.InsertCellInWorksheet("Z", 1, worksheetPart); // Set the value of cell Z1. cell1.CellValue = new CellValue(index1.ToString()); cell1.DataType = new EnumValue<CellValues>(CellValues.SharedString); // Insert the text into the SharedStringTablePart. int index2 = Utils.InsertSharedStringItem("toto2", shareStringPart); // Insert cell AA1 into the new worksheet. Cell cell2 = Utils.InsertCellInWorksheet("AA", 1, worksheetPart); // Set the value of cell AA1. cell2.CellValue = new CellValue(index2.ToString()); cell2.DataType = new EnumValue<CellValues>(CellValues.SharedString);





    • Edited by jmclej Monday, March 9, 2015 2:37 PM
    Monday, March 9, 2015 2:34 PM
  • Hello,

    I have refactored my sample to make it clearer, it is in this zip : TestWriteWithOpenXML.zip

    There are 3 projects in it : CopyCodeFromProductivityTool, which creates correctly but non-dynamically my Excel file. FirstAttempt and SecondAttempt are 2 tries to generate dynamically my Excel. They are console applications so you can choose to generate the file with or without the error which I am stuck with for a while now. The difference is only to write text in both Z and AA columns... In the 2 applications, I also tried to create/close and reopen my excel file or to create all in once. That was an attempt to discover how to do it right, but the error appears in both cases.

    Also, when I check the validation of my file via the Open XML SDK 2.5 Productivity Tool, it says there is no error. It only happens when I actually try to open it with Excel.

    Thank you again for trying to help me. I wrote FirstAttempt and SecondAttempt by following the only 2 samples I have found to create an excel file from scratch and write in it, which obviously don't work completely.



    • Edited by jmclej Monday, March 16, 2015 12:17 PM
    Monday, March 16, 2015 10:42 AM
  • Hi,

    There was a bug in the samples actually. In order to go around it, I replaced in the method CreateCell "if (string.Compare(cell.CellReference.Value, address, true) > 0)" by :

    if (fromBase26(Regex.Replace(cell.CellReference.Value, @"[\d-]", string.Empty)) > fromBase26(Regex.Replace(address, @"[\d-]", string.Empty)))

    and define:

    //Hexavigesimal (Excel Column Name to Number) - Bijective
            private int fromBase26(string colName)
            {
                colName = colName.ToUpper();
                int decimalValue = 0;
                for (int i = 0; i < colName.Length; i++)
                {
                    decimalValue *= 26;
                    decimalValue += (colName[i] - 64);
                }
                return decimalValue;
            }

    Tuesday, March 17, 2015 9:51 AM
  • It took me 2 hours to search for damaged data in my data table that caused the not-working xlsx. After a while I realized that it was row # 27 always failed.

    How many letters does the alphabet have? 26 Right :-D

    jmclej You Made My Day!

    Friday, December 6, 2019 9:54 AM