none
New vs Existing worksheets RRS feed

  • Question

  • I wanted to use the code at this link to add strings to multiple "existing cells" on an "existing sheet". I want to get away from creating new sheets every time I use this code to export a table of strings from my database into my excel report sheet template. Is there a way to modify the code to do overwrites and write many cells at once? I tried for instance naming the sheet without the id number added but that just creates a sheet with the same name that excel then identifies as material to be recovered and it changes the sheet name to something to prevent duplicates. It feels like the code blocks defining the new worksheet and cell locations would have to be changed a lot so I am not sure if this kind of functionality is possible. I would appreciate any pointers to the right solution.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    //first "Add Reference" "Assemblies" named "DocumentFormat.OpenXml" and "WindowsBase"
    //needed for using the open xml sdk to insert cells into spreadsheet
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    //added this to allow the process statement that opens excel
    using System.Diagnostics;
    
    namespace Inserting_cells_into_spreadsheet
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                GeneratedClass gc = new GeneratedClass();
                gc.InsertText(@"C:\Users\Jordan Walker\Documents\DATA sheets\MAR.xlsx", "Hello New World");
                
                //This opens the excel file from the current directory
                Process.Start(@"C:\Users\Jordan Walker\Documents\DATA sheets\MAR.xlsx");
            }
        }
    }
    namespace Inserting_cells_into_spreadsheet
    {
        public class GeneratedClass
        {
            // Given a document name and text,
            // inserts a new worksheet and writes the text to cell "A1" of the new worksheet.
            public void InsertText(string docName, string text)
            {
                // Open the document for editing.
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                {
                    // Get the SharedStringTablePart. If it does not exist, create a new one.
                    SharedStringTablePart shareStringPart;
                    if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    {
                        shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    }
                    else
                    {
                        shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    }
    
                    // Insert the text into the SharedStringTablePart.
                    int index = InsertSharedStringItem(text, shareStringPart);
                    
                    // Insert a new worksheet.
                    WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
                    
                    // 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);
    
                    // Save the new worksheet.
                    worksheetPart.Worksheet.Save();
    
                }
            }
    
            // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
            // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
            private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
            {
                // If the part does not contain a SharedStringTable, create one.
                if (shareStringPart.SharedStringTable == null)
                {
                    shareStringPart.SharedStringTable = new SharedStringTable();
                }
    
                int i = 0;
    
                // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
                foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
                {
                    if (item.InnerText == text)
                    {
                        return i;
                    }
    
                    i++;
                }
    
                // The text does not exist in the part. Create the SharedStringItem and return its index.
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
                shareStringPart.SharedStringTable.Save();
    
                return i;
            }
    
            // Given a WorkbookPart, inserts a new worksheet.
            private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
            {
                // Add a new worksheet part to the workbook.
                WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();
    
                Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
    
                // Get a unique ID for the new sheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
    
                string sheetName = "Sheet" + sheetId;
    
                // Append the new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append(sheet);
                workbookPart.Workbook.Save();
    
                return newWorksheetPart;
            }
    
            // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
            // If the cell already exists, returns it. 
            private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
            {
                Worksheet worksheet = worksheetPart.Worksheet;
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                string cellReference = columnName + rowIndex;
    
                // If the worksheet does not contain a row with the specified row index, insert one.
                Row row;
                if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
                {
                    row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
                }
                else
                {
                    row = new Row() { RowIndex = rowIndex };
                    sheetData.Append(row);
                }
    
                // If there is not a cell with the specified column name, insert one.  
                if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
                {
                    return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
                }
                else
                {
                    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                    Cell refCell = null;
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                        {
                            refCell = cell;
                            break;
                        }
                    }
    
                    Cell newCell = new Cell() { CellReference = cellReference };
                    row.InsertBefore(newCell, refCell);
    
                    worksheet.Save();
                    return newCell;
                }
            }
        }    
    }

    Monday, March 24, 2014 12:44 AM

Answers

  • Hi,

    You could use the code below to find an existing worksheet with sheet name so that it would not insert a new sheet every time.

    For example, the code below would find the sheet named “Sheet2”.

    WorksheetPart worksheetPart = FindWorkSheet(spreadSheet.WorkbookPart, "Sheet2");
    private static WorksheetPart FindWorkSheet(WorkbookPart workbookPart, string condition)
                {
                    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == condition);
    
                    if (sheet == null) throw new Exception(string.Format("Could not find sheet with name {0}", condition));
                    else return workbookPart.GetPartById(sheet.Id) as WorksheetPart;
                }

    You could improve the code so that if there is no such sheet, we need to insert a new sheet with the fix name.

    >>Is there a way to modify the code to do overwrites and write many cells at once?<<

    I would recommend you to use OLEDB to connect to an Excel file and use SQL statement to insert data into the Excel file. It would be easy through C#.

    In addition, a better way to batch insert data into Excel through OpenXML SDK is that you can pass row index and column index into the function.

    In this case, the point is to convert column index to column name(e.g. 5 to "E").

    What I thought is that you can create an array from "A" to "Z". Then you need to create a function to convert column index to column name.

    You can use the code below to get quotient and residue since the count of the array is 24.

    int quotient = columnindex / 24;
    int residue = columnindex % 24;

    After that you can compare residue and quotient with the array to get column name.

    Hope this helps.


    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 25, 2014 9:54 AM
    Moderator
  • Hi,

    I'm glad to know that you have improved the code to insert a new worksheet if there is no such sheet existing. 

    >>Could you point to some place to learn how to use basics of OLEDB<<

    Here is a sample from MSDN for your reference:

    C# reading from Excel to Text using OleDb - limit at 255 columns

    You could use SQL insert statement to batch insert data into Excel workbooks. Also you could dynamically create SQL statement based on the data source.

    For example, you get a dataset object after reading data from SQL Server. You could loop all data from this object and overlay the SQL statement to execute it one time.

    For more question about OLEDB using C#, I would suggest you asking from ADO.NET Managed Providers forum.


    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.

    • Proposed as answer by George HuaModerator Monday, March 31, 2014 10:27 AM
    • Marked as answer by reigh7 Tuesday, April 1, 2014 1:51 AM
    Monday, March 31, 2014 3:45 AM
    Moderator

All replies

  • Hi,

    You could use the code below to find an existing worksheet with sheet name so that it would not insert a new sheet every time.

    For example, the code below would find the sheet named “Sheet2”.

    WorksheetPart worksheetPart = FindWorkSheet(spreadSheet.WorkbookPart, "Sheet2");
    private static WorksheetPart FindWorkSheet(WorkbookPart workbookPart, string condition)
                {
                    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == condition);
    
                    if (sheet == null) throw new Exception(string.Format("Could not find sheet with name {0}", condition));
                    else return workbookPart.GetPartById(sheet.Id) as WorksheetPart;
                }

    You could improve the code so that if there is no such sheet, we need to insert a new sheet with the fix name.

    >>Is there a way to modify the code to do overwrites and write many cells at once?<<

    I would recommend you to use OLEDB to connect to an Excel file and use SQL statement to insert data into the Excel file. It would be easy through C#.

    In addition, a better way to batch insert data into Excel through OpenXML SDK is that you can pass row index and column index into the function.

    In this case, the point is to convert column index to column name(e.g. 5 to "E").

    What I thought is that you can create an array from "A" to "Z". Then you need to create a function to convert column index to column name.

    You can use the code below to get quotient and residue since the count of the array is 24.

    int quotient = columnindex / 24;
    int residue = columnindex % 24;

    After that you can compare residue and quotient with the array to get column name.

    Hope this helps.


    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 25, 2014 9:54 AM
    Moderator

  • >>Is there a way to modify the code to do overwrites and write many cells at once?<<

    I would recommend you to use OLEDB to connect to an Excel file and use SQL statement to insert data into the Excel file. It would be easy through C#.

    Could you point to some place to learn how to use basics of OLEDB right now I am using Lightswitch as a database and am pretty green at coding.

    "You could improve the code so that if there is no such sheet, we need to insert a new sheet with the fix name."

    So I could write the statement

                if (sheet == null) 

    // Add a new worksheet part to the workbook. WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new sheet. uint sheetId = 1; if (sheets.Elements<Sheet>().Count() > 0) { sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1; } string sheetName = "Sheet" + sheetId; // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); workbookPart.Workbook.Save();

    • Edited by reigh7 Monday, March 31, 2014 12:51 AM
    Monday, March 31, 2014 12:48 AM
  • Hi,

    I'm glad to know that you have improved the code to insert a new worksheet if there is no such sheet existing. 

    >>Could you point to some place to learn how to use basics of OLEDB<<

    Here is a sample from MSDN for your reference:

    C# reading from Excel to Text using OleDb - limit at 255 columns

    You could use SQL insert statement to batch insert data into Excel workbooks. Also you could dynamically create SQL statement based on the data source.

    For example, you get a dataset object after reading data from SQL Server. You could loop all data from this object and overlay the SQL statement to execute it one time.

    For more question about OLEDB using C#, I would suggest you asking from ADO.NET Managed Providers forum.


    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.

    • Proposed as answer by George HuaModerator Monday, March 31, 2014 10:27 AM
    • Marked as answer by reigh7 Tuesday, April 1, 2014 1:51 AM
    Monday, March 31, 2014 3:45 AM
    Moderator