none
Appending a row to an Excel spread sheet/workbook. RRS feed

  • Question

  • I have an existing Excel workbook (.xlsx) and I want to append a row to a sheet called 'Log' that already exists. I wasn't able to readily see the solution from the documentation or samples so if someone would be so kind as to help a beginner? My first problem is finding the sheet by name. The second would be adding the row to the sheet. I believe the construction of the row would be something like:

    			Row row = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:7" }, DyDescent = 0.25D };
    
    			Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
    			CellValue cellValue1 = new CellValue();
    			cellValue1.Text = partner;
    
    			cell1.Append(cellValue1);
    
    			Cell cell2 = new Cell() { CellReference = "B1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
    			CellValue cellValue2 = new CellValue();
    			cellValue2.Text = baseCatalog;
    
    			cell2.Append(cellValue2);
    
    			Cell cell3 = new Cell() { CellReference = "C1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
    			CellValue cellValue3 = new CellValue();
    			cellValue3.Text = productId;
    
    			cell3.Append(cellValue3);
    
    			Cell cell4 = new Cell() { CellReference = "D1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
    			CellValue cellValue4 = new CellValue();
    			cellValue4.Text = variantId;
    
    			cell4.Append(cellValue4);
    
    			Cell cell5 = new Cell() { CellReference = "E1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
    			CellValue cellValue5 = new CellValue();
    			cellValue5.Text = message;
    
    			cell5.Append(cellValue5);
    
    			Cell cell6 = new Cell() { CellReference = "F1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
    			CellValue cellValue6 = new CellValue();
    			cellValue6.Text = detail;
    
    			cell6.Append(cellValue6);
    
    			Cell cell7 = new Cell() { CellReference = "G1", StyleIndex = (UInt32Value)4U, DataType = CellValues.SharedString };
    			CellValue cellValue7 = new CellValue();
    			cellValue7.Text = time.ToLongTimeString();
    
    			cell7.Append(cellValue7);
    
    			row.Append(cell1);
    			row.Append(cell2);
    			row.Append(cell3);
    			row.Append(cell4);
    			row.Append(cell5);
    			row.Append(cell6);
    			row.Append(cell7);
    
    

    Of course the Cell Reference will need to change for each Cell if this code is even close. Is there a way I can reference the cells by column name rather than A, B, C, ...? Then I would need to replace the '1' as a designator for the row above to the 'last' row.

    Thank you.


    Kevin Burton
    Tuesday, February 1, 2011 5:41 PM

Answers

  • Hi KevinBurton,

    >> What is the proper way to insert a time (from DateTime) into a cell?

    Thank you for providing the code snippet, I modified your code and it works well, below is the code:

     private static void AppendRow(SheetData sheetData,
                        uint index,
                                      string partner,
                                      string baseCatalog,
                                      string productId,
                                      string variantId,
                                      string message,
                                      string detail,
                                      DateTime time)
            {
                Row row = new Row() { RowIndex = (UInt32Value)index, Spans = new ListValue<StringValue>() { InnerText = "1:7" }, DyDescent = 0.25D };

                Cell cell1 = new Cell() { CellReference = "A" + index, DataType = CellValues.String };
                CellValue cellValue1 = new CellValue();
                cellValue1.Text = partner;

                cell1.Append(cellValue1);

                Cell cell2 = new Cell() { CellReference = "B" + index, DataType = CellValues.String };
                CellValue cellValue2 = new CellValue();
                cellValue2.Text = baseCatalog;

                cell2.Append(cellValue2);

                Cell cell3 = new Cell() { CellReference = "C" + index, DataType = CellValues.String };
                CellValue cellValue3 = new CellValue();
                cellValue3.Text = productId;

                cell3.Append(cellValue3);

                Cell cell4 = new Cell() { CellReference = "D" + index, DataType = CellValues.String };
                CellValue cellValue4 = new CellValue();
                cellValue4.Text = variantId;

                cell4.Append(cellValue4);

                Cell cell5 = new Cell() { CellReference = "E" + index, DataType = CellValues.String };
                CellValue cellValue5 = new CellValue();
                cellValue5.Text = message;

                cell5.Append(cellValue5);

                Cell cell6 = new Cell() { CellReference = "F" + index, DataType = CellValues.String };
                CellValue cellValue6 = new CellValue();
                cellValue6.Text = detail;
                cell6.Append(cellValue6);

                //Cell cell7 = new Cell() { CellReference = "G" + index,StyleIndex = (UInt32Value)1U};
                Cell cell7 = new Cell() { CellReference = "G" + index, DataType = CellValues.String };
                CellValue cellValue7 = new CellValue();
                cellValue7.Text = time.ToShortDateString();
                cell7.Append(cellValue7);

                row.Append(cell1);
                row.Append(cell2);
                row.Append(cell3);
                row.Append(cell4);
                row.Append(cell5);
                row.Append(cell6);
                row.Append(cell7);
                sheetData.Append(row);
            }

    I think the issue should be related with the DataType, you should choose proper DataType.

    I hope it can help you and feel free to follow up after you tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by KevinBurton Wednesday, February 9, 2011 2:17 PM
    Thursday, February 3, 2011 6:58 AM

All replies

  • Hi KevinBurton,

    Thank you for posting and we are glad to help with you.

    >> My first problem is finding the sheet by name.

    I have two method to get sheet via name:

                // get sheet by name
                ws = wb.Sheets["Sheet1"as Excel.Worksheet;
                // another way to get sheet by name 
                ws = wb.Worksheets.get_Item("Sheet1"as Excel.Worksheet;

    >>The second would be adding the row to the sheet.

    It is not neccessary to add the row to the sheet, there already exist the rows in the sheet, you just insert values into it, such like:

                // filled data in: A1, B1, C1,....G1
                int columnNum = 7;
                for (int i = 1; i < columnNum; i++)
                {
                    Excel.Range eachCell = ws.Cells[1, i];
                    // apply style to Excel cells
                    eachCell.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent6;
                    eachCell.Value2 = "Happy New Year " + i.ToString();
                }

    Bellow is my entire code snippet for you to reference:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Excel = Microsoft.Office.Interop.Excel;

    namespace GetSheetByNameAndAppendRows
    {
        class Program
        {
            static void Main(string[] args)
            {
                Excel.Application excelApp = new Excel.Application();
                excelApp.Visible = true;
                Excel.Workbook wb = null;
                Excel.Worksheet ws = null;
                wb = excelApp.Workbooks.Open(@"E:\MyWorkingDocuments\ExcelCases\GetSheetByNameAndAppendRows\GetSheetByNameAndAppendRows\bin\Debug\Test.xlsx",
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);

                // get sheet by name
                ws = wb.Sheets["Sheet1"as Excel.Worksheet;
                // another way to get sheet by name 
                ws = wb.Worksheets.get_Item("Sheet1"as Excel.Worksheet;

                // filled data in: A1, B1, C1,....G1, not via column name, just via column number
                int columnNum = 7;
                for (int i = 1; i < columnNum; i++)
                {
                    Excel.Range eachCell = ws.Cells[1, i];
                    // apply style to Excel cells
                    eachCell.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent6;
                    eachCell.Value2 = "Happy New Year " + i.ToString();
                }
                wb.Close();
                excelApp.Quit();
            }
        }
    }

    I hope it an help you and feel free to follow up after you tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 2, 2011 3:11 AM
  • Hi KevinBurton,

    I am sorry that I didn't noticed you post in Open XML forum, the previous post is about using Excel PIA. I will do further research about your problem via Open XML. And I will reply you after I succeed in resolving it. 

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Edited by Bruce Song Wednesday, February 2, 2011 3:18 AM typo
    Wednesday, February 2, 2011 3:16 AM
  • I have come up with the following code:

    		public void Log(string partner,
    						string baseCatalog,
    						string productId,
    						string variantId,
    						string message,
    						string detail,
    						DateTime time)
    		{
    			using (SpreadsheetDocument document = SpreadsheetDocument.Open(Path, true))
    			{
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Log");
            if (sheets.Count() == 0)
            {
              // The specified worksheet does not exist.
              return;
            }
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
            Worksheet worksheet = worksheetPart.Worksheet;
            uint maxRowIndex = worksheet.Descendants<Row>().Max(r => r.RowIndex.Value);
            maxRowIndex = maxRowIndex > 1 ? maxRowIndex + 1 : 2;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            AppendRow(sheetData,
                 maxRowIndex,
                 partner,
                 baseCatalog,
                 productId,
                 variantId,
                 message,
                 detail,
                 time);
            worksheet.Save();
          }
    		}
    
        private void AppendRow(SheetData sheetData,
                    uint index,
    							  string partner,
    							  string baseCatalog,
    							  string productId,
    							  string variantId,
    							  string message,
    							  string detail,
    							  DateTime time)
    		{
          Row row = new Row() { RowIndex = (UInt32Value)index, Spans = new ListValue<StringValue>() { InnerText = "1:6" }, DyDescent = 0.25D };
    
    			Cell cell1 = new Cell() { CellReference = "A" + index, StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
    			CellValue cellValue = new CellValue();
    			cellValue.Text = partner;
    			cell1.Append(cellValue);
    
          Cell cell2 = new Cell() { CellReference = "B" + index, StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
    			cellValue = new CellValue();
    			cellValue.Text = baseCatalog;
    			cell2.Append(cellValue);
    
          Cell cell3 = new Cell() { CellReference = "C" + index, StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
          cellValue = new CellValue();
          cellValue.Text = productId;
          cell3.Append(cellValue);
    
          Cell cell4 = new Cell() { CellReference = "D" + index, StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
          cellValue = new CellValue();
          cellValue.Text = variantId;
          cell4.Append(cellValue);
    
          Cell cell5 = new Cell() { CellReference = "E" + index, StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
          cellValue = new CellValue();
          cellValue.Text = message;
          cell5.Append(cellValue);
    
          Cell cell6 = new Cell() { CellReference = "F" + index, StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
          cellValue = new CellValue();
          cellValue.Text = detail;
          cell6.Append(cellValue);
    
          //Cell cell7 = new Cell() { CellReference = "G" + index, StyleIndex = (UInt32Value)4U, DataType = CellValues.SharedString };
          //cellValue = new CellValue();
          //cellValue.Text = time.ToLongTimeString();
          //cell7.Append(cellValue);
    
    			row.Append(cell1);
    			row.Append(cell2);
          row.Append(cell3);
          row.Append(cell4);
          row.Append(cell5);
          row.Append(cell6);
    			//row.Append(cell7);
    
    			sheetData.Append(row);
    		}
    
    

    But the spreadsheet that this produces when open with Excel gives me an error "Excel found unreadable content in 'log.xlsx'. Do you want to recover the contents of the workbook? I you trust the source of this workbook, click Yes." If I click yes then I get:

    <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
    - <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
     <logFileName>error021920_01.xml</logFileName> 
     <summary>Errors were detected in file 'C:\TEMP\log.xlsx'</summary> 
    - <repairedRecords summary="Following is a list of repairs:">
     <repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet.xml part</repairedRecord> 
     </repairedRecords>
     </recoveryLog
    

    So I am not there yet. My code has a flaw somewhere.


    Kevin Burton
    Wednesday, February 2, 2011 4:01 PM
  • This error goes away if I comment out the date and make all the other cell types strings. So, I have

          Cell cell7 = new Cell() { CellReference = "G" + index, StyleIndex = (UInt32Value)4U, DataType = CellValues.Date };
          cellValue = new CellValue();
          cellValue.Text = time.ToOADate().ToString();
          cell7.Append(cellValue);
    
    

    What is the proper way to insert a time (from DateTime) into a cell? Unless the code above has serious flaws then inserting the date/time into a cell is the last of the problems. Any ideas?

     


    Kevin Burton
    Thursday, February 3, 2011 12:06 AM
  • Hi KevinBurton,

    >> What is the proper way to insert a time (from DateTime) into a cell?

    Thank you for providing the code snippet, I modified your code and it works well, below is the code:

     private static void AppendRow(SheetData sheetData,
                        uint index,
                                      string partner,
                                      string baseCatalog,
                                      string productId,
                                      string variantId,
                                      string message,
                                      string detail,
                                      DateTime time)
            {
                Row row = new Row() { RowIndex = (UInt32Value)index, Spans = new ListValue<StringValue>() { InnerText = "1:7" }, DyDescent = 0.25D };

                Cell cell1 = new Cell() { CellReference = "A" + index, DataType = CellValues.String };
                CellValue cellValue1 = new CellValue();
                cellValue1.Text = partner;

                cell1.Append(cellValue1);

                Cell cell2 = new Cell() { CellReference = "B" + index, DataType = CellValues.String };
                CellValue cellValue2 = new CellValue();
                cellValue2.Text = baseCatalog;

                cell2.Append(cellValue2);

                Cell cell3 = new Cell() { CellReference = "C" + index, DataType = CellValues.String };
                CellValue cellValue3 = new CellValue();
                cellValue3.Text = productId;

                cell3.Append(cellValue3);

                Cell cell4 = new Cell() { CellReference = "D" + index, DataType = CellValues.String };
                CellValue cellValue4 = new CellValue();
                cellValue4.Text = variantId;

                cell4.Append(cellValue4);

                Cell cell5 = new Cell() { CellReference = "E" + index, DataType = CellValues.String };
                CellValue cellValue5 = new CellValue();
                cellValue5.Text = message;

                cell5.Append(cellValue5);

                Cell cell6 = new Cell() { CellReference = "F" + index, DataType = CellValues.String };
                CellValue cellValue6 = new CellValue();
                cellValue6.Text = detail;
                cell6.Append(cellValue6);

                //Cell cell7 = new Cell() { CellReference = "G" + index,StyleIndex = (UInt32Value)1U};
                Cell cell7 = new Cell() { CellReference = "G" + index, DataType = CellValues.String };
                CellValue cellValue7 = new CellValue();
                cellValue7.Text = time.ToShortDateString();
                cell7.Append(cellValue7);

                row.Append(cell1);
                row.Append(cell2);
                row.Append(cell3);
                row.Append(cell4);
                row.Append(cell5);
                row.Append(cell6);
                row.Append(cell7);
                sheetData.Append(row);
            }

    I think the issue should be related with the DataType, you should choose proper DataType.

    I hope it can help you and feel free to follow up after you tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by KevinBurton Wednesday, February 9, 2011 2:17 PM
    Thursday, February 3, 2011 6:58 AM