locked
Add data to existing OpenXML spreadsheets RRS feed

  • Question

  • User-381880841 posted

    Hi,

    I am created Multiple sheets of spreadsheet using open xml sdk. My problem is, I want insert some default data in Those multiple sheets of spreadsheet.

    and how to open that existing spreadsheet using open xml sdk. if anyone knows please help me..........

    Thanks in advance......

    Thursday, May 1, 2014 8:11 AM

Answers

  • User1918509225 posted

    Hi kumar402,

    For your post ,please you  try the code below:

    Excel.Application app = new Excel.Application();
    app.Visiable=false; Excel.Workbook workbook = app.Workbooks.Open("YourFile.xls", 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);
    //if you want to get the second worksheet of the excel ,just like below:
    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[2]; worksheet.Activate();
    //set value to the cell of the worksheet.
    worksheet.Cells[rownum,colnum]="xx";
    workbook.Save();

    Hope it can help you.

    Best Regards,

    Kevin Shen.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 5, 2014 5:00 AM

All replies

  • User1508394307 posted

    So, you mean 

    1) you know how to create a spreadsheet using open xml sdk
    2) but you do not know how to open it? 

    Share your code, please.

    Thursday, May 1, 2014 12:09 PM
  • User-381880841 posted

    Yes, I am created spreadsheet with 3 sheets. Now I'm trying to add some data for that. Here is my code:

            protected void Page_Load(object sender, EventArgs e)
            {
                string filepath = "~/MyFiles/iQBoxSampleExportEmpty.xlsx";
              
                CreateSpreadsheetWorkbook(filepath);
             
            }

             public static void CreateSpreadsheetWorkbook(string filepath)
            {
                // Create a spreadsheet document by supplying the filepath.
                // By default, AutoSave = true, Editable = true, and Type = xlsx.
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, false);

                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());


                // Add Sheets to the Workbook.
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

                // Append a new worksheet and associate it with the workbook.
                Sheet s1 = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "TSC" };
                Sheet s2 = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "TMC" };
                Sheet s3 = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "TDD" };
                sheets.Append(s1);
                sheets.Append(s2);
                sheets.Append(s3);

                // Get the sheetData cell table.
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                // Add a row to the cell table.
                Row row;
                row = new Row() { RowIndex = 3, StyleIndex = 1 };
                sheetData.Append(row);

                // In the new row, find the column location to insert a cell in A1.  
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, "A3", true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                // Add the cell to the cell table at A1.
                Cell newCell = new Cell() { CellReference = "C3" };
                Cell newCell1 = new Cell() { CellReference = "D3" };
                row.InsertBefore(newCell, refCell);
                row.InsertBefore(newCell1, refCell);

                // Set the cell value to be a numeric value of 100.
                newCell.CellValue = new CellValue("#GUIDeXactLCMS#");
                newCell1.CellValue = new CellValue("EN");
                newCell.DataType = new EnumValue<CellValues>(CellValues.Number);

                // Close the document.
                spreadsheetDocument.Close();
            }

    Friday, May 2, 2014 4:10 AM
  • User1918509225 posted

    Hi kumar402,

    For your post ,please you  try the code below:

    Excel.Application app = new Excel.Application();
    app.Visiable=false; Excel.Workbook workbook = app.Workbooks.Open("YourFile.xls", 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);
    //if you want to get the second worksheet of the excel ,just like below:
    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[2]; worksheet.Activate();
    //set value to the cell of the worksheet.
    worksheet.Cells[rownum,colnum]="xx";
    workbook.Save();

    Hope it can help you.

    Best Regards,

    Kevin Shen.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 5, 2014 5:00 AM