none
How to insert text in second sheet of excel ? RRS feed

  • Question

  • Hi all,

    I am able to create the excel sheet using openxml. and able to insert the data in first sheet now i want to insert text in second sheet how to insert it in second sheet?

    thanks


    vicky Bangalore

    Thursday, January 10, 2013 11:37 AM

Answers

  • Hi Vicky,

    Please take a look at this.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using com.mksword.Net.OpenXmlTools;
    using com.mksword.OpenXmlUtil_log4netTool;
    
    namespace ConsoleApplication15
    {
        public class Class1: ExcelTool
        {
            public override void Action()
            {
                try
                {
                    if (_Core.HandOpenSpreadsheetDocumentOnLoacl(
                        PropulateDataToSpecificSheet, true))
                    {
                        SetLog("Missing acomplished!", OXULogType.INFO);
                    }
                    else
                    {
                        SetLog("Missing failed!", OXULogType.ERROR);
                    }
                }
                catch (Exception ex)
                {
                    SetLog(ex.Message, OXULogType.FATAL);
                }
            }
    
            // Key code is in this method, you need not take more care about Action
            // method. It just method to wrap frequently code which will used in 
            // Open Xml program - It is meaningless for your issue.
            private bool PropulateDataToSpecificSheet(SpreadsheetDocument SSD)
            {
                bool result = true;
                string SheetName = "Sheet2";
                WorkbookPart WP = SSD.WorkbookPart;
                Workbook WB = WP.Workbook;
                // Find Sheet then retrieve the WorksheetPart it referenced.
                // This code will fit the situation which your thread title mentioned.
                Sheet S = WB.Descendants<Sheet>().Where(SH => SH.Name == SheetName)
                    .FirstOrDefault();
                WorksheetPart WSP = WP.GetPartById(S.Id) as WorksheetPart;
                if (WSP != null)
                {
                    SheetData SD = WSP.Worksheet.Descendants<SheetData>().First();
                    // To-Do: Please use you code at that place.
                    // For example ExportDataTable1(SD);
                }
                /**
                 * Following comment will fitter the code which your provide.
                 */
                //WorksheetPart newPart = WP.AddNewPart<WorksheetPart>();
                //newPart.Worksheet = new Worksheet(new SheetData());
                //SheetData SD = newPart.Worksheet.Descendants<SheetData>().First();
                //// To-Do: Please use you code at that place.
                //// For example ExportDataTable1(SD);
                return result;
            }
        }
    
    }

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, January 17, 2013 6:53 AM
    Moderator

All replies

  • Take a look at this...

    http://zsvipullo.blogspot.it/2011/08/excel-helper-leggere-un-file-xlsx.html


    Regards,
    Bubu
    http://zsvipullo.blogspot.it

    Please mark my answer if it helped you, I would greatly appreciate it.

    Thursday, January 10, 2013 12:25 PM
  • Hi sir,

    i checked this blog it is in some different language i am not able to understand.

    thanks


    vicky Bangalore

    Friday, January 11, 2013 8:31 AM
  • Hi Vicky,

    Thanks for posting in the MSDN Forum.

    Every worksheet will reference a Worksheetpart. You need query the rID form workbook via search the sheet and use that rID to get specific WorksheetPart for you goal.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 14, 2013 6:28 AM
    Moderator
  • Hi Tom,

    i am able to write text in first sheet and when i am trying to write text in second sheet so it's not showing anything in sheet2 which data i am inserting.

    could you check once my code?

    my code is below:

    protected void btndownload_Click(object sender, System.EventArgs e)
        {
           
           
            BuildWorkbook(@"C:\Users\VIKASH\Desktop\Test.xlsx");
            ClientScript.RegisterStartupScript(this.GetType(), "", "alert('File Created');", true);

      }

    private  void BuildWorkbook(string fileName)
        {
            try
            {
                using (SpreadsheetDocument s = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
                {
                    WorkbookPart workbookPart = s.AddWorkbookPart();
                    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    string relId = workbookPart.GetIdOfPart(worksheetPart);
                    Workbook workbook = new Workbook();
                    FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
                    Worksheet worksheet = new Worksheet();
                    SheetData sheetData = new SheetData();
                    SheetData sheetData1 = new SheetData();
                    object misvalue = System.Reflection.Missing.Value;
                    worksheet.Append(sheetData);
                    worksheetPart.Worksheet = worksheet;
                    worksheetPart.Worksheet.Save();
                    Sheets sheets = new Sheets();
                    Sheet sheet = new Sheet { Name = "EmployeeForm", SheetId = 1, Id = relId };
                    //Sheet sheet1 = new Sheet { Name = "ReferenceSheet", SheetId = 2, Id = relId };
                    //Sheet sheet2 = new Sheet { Name = "Sheet3", SheetId = 3, Id = relId };

                    //sheets.Append(sheet);
                    workbook.Append(fileVersion);
                   //ExportDataTable(sheetData);
                    ExportDataTable(sheetData);
                   sheets.Append(sheet);
                  
                    //ExportDataTable1(sheetData1);
                    //sheets.Append(sheet1);
                    
                    workbook.Append(sheets);
                    s.WorkbookPart.Workbook = workbook;
                    s.WorkbookPart.Workbook.Save();
                    s.Close();
                   InsertWorksheet(fileName);
                   
                   
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                Console.ReadLine();
            }
        }

     public void ExportDataTable(SheetData sheetData)
        {
            //add column names to the first row
            DataTable dt = new DataTable();
            SheetData st = new SheetData();
            string qry = "Select [Emp_ID],[Emp_FirstName],[Emp_LastName] ,[Card_ID],[Comp_ID],[Cat_Code],[DOB],[DOJ],[Dept_Code],[Desig_Code],[Address],[Phone],[IsManager],[Managerid],[Emp_Email],[Shift_Code],[Mobile],[Status],[gender],[emp_cat] from employee";
            dt = objDataTier.ReturnDataTable(qry);
            Row header = new Row();
            header.RowIndex = (UInt32)1;
            SheetData sheetData2 = new SheetData();
            foreach (DataColumn column in dt.Columns)
            {
                Cell headerCell = createTextCell(dt.Columns.IndexOf(column) + 1, Convert.ToInt32(header.RowIndex.Value), column.ColumnName);
                header.AppendChild(headerCell);
            }

            sheetData.AppendChild(header);
            DataRow contentRow;
            int startRow = 2;
            DataTable dt1 = new DataTable();
            dt1.Clear();
            dt1.Columns.Add(new DataColumn("Emp_Id", typeof(string)));
            dt1.Columns.Add(new DataColumn("fname", typeof(string)));
            dt1.Columns.Add(new DataColumn("lname", typeof(string)));
            dt1.Columns.Add(new DataColumn("eid", typeof(string)));
            dt1.Columns.Add(new DataColumn("cid", typeof(string)));
            dt1.Columns.Add(new DataColumn("bid", typeof(string)));
            dt1.Columns.Add(new DataColumn("date", typeof(string)));
            dt1.Columns.Add(new DataColumn("date1", typeof(string)));
            dt1.Columns.Add(new DataColumn("depcode", typeof(string)));
            dt1.Columns.Add(new DataColumn("desicode", typeof(string)));
            dt1.Columns.Add(new DataColumn("add", typeof(string)));
            dt1.Columns.Add(new DataColumn("ph", typeof(string)));
            dt1.Columns.Add(new DataColumn("ism", typeof(string)));
            dt1.Columns.Add(new DataColumn("ifnotism", typeof(string)));
            dt1.Columns.Add(new DataColumn("email", typeof(string)));
            dt1.Columns.Add(new DataColumn("shiftcode", typeof(string)));
            dt1.Columns.Add(new DataColumn("mno", typeof(string)));
            dt1.Columns.Add(new DataColumn("enter1", typeof(string)));
            dt1.Columns.Add(new DataColumn("male", typeof(string)));
            dt1.Columns.Add(new DataColumn("ecat", typeof(string)));
            //DataColumn dc = new DataColumn("Emp_Id(8 Alphanumeric ex:ABC00012)", typeof(string));
            //DataColumn dc1 = new DataColumn("Emp_Id(8 Alphanumeric ex:ABC00012)", typeof(string));
            //dt1.Columns.Add(dc);
            contentRow = dt1.NewRow();
            contentRow["Emp_Id"] = "Emp_Id(8 Alphanumeric ex:ABC00012)";
            contentRow["fname"] = "Firstname(ex:Harish)";
            contentRow["lname"] = "Lastname (ex:Kumar)";
            contentRow["eid"] = "Erollmentid(only 6digits ex:000089)";
            contentRow["cid"] = " Comp_Code (3 Alphanumeric ex:C01)";
            contentRow["bid"] = "Branch_Code(3 Alphanumeric ex:C01)";
            contentRow["date"] = "Date";
            contentRow["date1"] = "Date";
            contentRow["depcode"] = "Dept_Code(3 Alphanumeric ex:D01)";
            contentRow["desicode"] = "Desig_Code(3 Alphanumeric ex:D01)";
            contentRow["add"] = "Address";
            contentRow["ph"] = "12digits Ph-No:0802369854";
            contentRow["ism"] = "Ismanager enter 1 else 0";
            contentRow["ifnotism"] = "If is manager leave empty else enter managerid(Emp_id)";
            contentRow["email"] = "Email 'aaa@gmail.com'";
            contentRow["shiftcode"] = "Shift_Code(3 Alphanumeric ex:S01)";
            contentRow["mno"] = "12digits Mob-No:9999999999";
            contentRow["enter1"] = "Enter 1";
            contentRow["male"] = "Male/Female";
            contentRow["ecat"] = "Ecat_Code (3 Alphanumeric ex:E01)";
            dt1.Rows.Add(contentRow);
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                contentRow = dt1.Rows[i];
                sheetData.AppendChild(createContentRow(contentRow, i + startRow));
            }

        }

    public void ExportDataTable1(SheetData sheetData2)
        {
            //add column names to the first row
            DataSet ds = new DataSet();
            DataTable dt2 = new DataTable();
            dt2.Columns.Add(new DataColumn("Comp_ID", typeof(string)));
            dt2.Columns.Add(new DataColumn("Comp_Name", typeof(string)));
            dt2.Columns.Add(new DataColumn("Dept_Code", typeof(string)));
            dt2.Columns.Add(new DataColumn("Dept_Name", typeof(string)));
            dt2.Columns.Add(new DataColumn("Desig_Code", typeof(string)));
            dt2.Columns.Add(new DataColumn("Desig_Name", typeof(string)));
            dt2.Columns.Add(new DataColumn("Cat_Code", typeof(string)));
            dt2.Columns.Add(new DataColumn("Cat_Name", typeof(string)));
            dt2.Columns.Add(new DataColumn("ECat_Code", typeof(string)));
            dt2.Columns.Add(new DataColumn("ECat_Name", typeof(string)));
            Row header1 = new Row();
            header1.RowIndex = (UInt32)1;
            foreach (DataColumn col1 in dt2.Columns)
            {
                string col = col1.ColumnName;
                Cell headerCell = createTextCell(dt2.Columns.IndexOf(col1) + 1, Convert.ToInt32(header1.RowIndex.Value), col1.ColumnName);
                header1.AppendChild(headerCell);
            }
            sheetData2.AppendChild(header1);

            //loop through each data row  
            DataRow contentRow;
            int startRow = 2;
            DataTable dt1 = new DataTable();
            dt1.Clear();
            dt1.Columns.Add(new DataColumn("Cid", typeof(string)));
            dt1.Columns.Add(new DataColumn("cname", typeof(string)));
            dt1.Columns.Add(new DataColumn("dcode", typeof(string)));
            dt1.Columns.Add(new DataColumn("dname", typeof(string)));
            dt1.Columns.Add(new DataColumn("decode", typeof(string)));
            dt1.Columns.Add(new DataColumn("dename", typeof(string)));
            dt1.Columns.Add(new DataColumn("bcode", typeof(string)));
            dt1.Columns.Add(new DataColumn("bname", typeof(string)));
            dt1.Columns.Add(new DataColumn("scode", typeof(string)));
            dt1.Columns.Add(new DataColumn("sname", typeof(string)));
            dt1.Columns.Add(new DataColumn("ecatcode", typeof(string)));
            dt1.Columns.Add(new DataColumn("ecatname", typeof(string)));
            contentRow = dt1.NewRow();
            contentRow["Cid"] = "Comp_ID";
            contentRow["cname"] = "Comp_Name";
            contentRow["dcode"] = "Dept_Code";
            contentRow["dname"] = "Dept_Name";
            contentRow["decode"] = "Desig_Code";
            contentRow["dename"] = "Desig_Name";
            contentRow["bcode"] = "Branch_Code";
            contentRow["bname"] = "Branch_Name";
            contentRow["scode"] = "Shift_Code";
            contentRow["sname"] = "Shift_Name";
            contentRow["ecatcode"] = "Emp_Cat Code";
            contentRow["ecatname"] = "Emp_Cat Name";
            dt1.Rows.Add(contentRow);
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                contentRow = dt1.Rows[i];
                sheetData2.AppendChild(createContentRow(contentRow, i + startRow));
            }

        }

     private static Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
        {
            Cell cell = new Cell();

            cell.DataType = CellValues.InlineString;
            cell.CellReference = getColumnName(columnIndex) + rowIndex;

            InlineString inlineString = new InlineString();
            Text t = new Text();

            t.Text = cellValue.ToString();
            inlineString.AppendChild(t);
            cell.AppendChild(inlineString);

            return cell;
        }

        private static Row createContentRow(DataRow dataRow, int rowIndex)
        {

            Row row = new Row
            {
                RowIndex = (UInt32)rowIndex
            };

            for (int i = 0; i < dataRow.Table.Columns.Count; i++)
            {
                Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
                row.AppendChild(dataCell);
            }

            return row;
        }
        private static string getColumnName(int columnIndex)
        {
            int dividend = columnIndex;
            string columnName = String.Empty;
            int modifier;

            while (dividend > 0)
            {
                modifier = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modifier).ToString() + columnName;
                dividend = (int)((dividend - modifier) / 26);
            }

            return columnName;
        }

     public  void InsertWorksheet(string docName)
        {
            // Open the document for editing.
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
            {
                // Add a blank WorksheetPart.
                WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());

                Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart);

                // Get a unique ID for the new worksheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
                SheetData sheetData = new SheetData();
                // Give the new worksheet a name.
                string sheetName = "RefrenceSheet";

                // Append the new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append(sheet);
                ExportDataTable1(sheetData);
                spreadSheet.WorkbookPart.Workbook.Save();
                spreadSheet.Close();
            }
        }

    when text is inserting in first sheet of excel use of this method ExportDataTable().

    and afterthat i am calling one more method where it's opening the excel file and insert text in second sheet

    for that method is :InsertWorksheet

    in this method i am running one more method Exportdatatable1() in that it's inserting text in second sheet.

    but when i am opening. i am getting data in first sheet not in second sheet.

    thanks


    vicky Bangalore

    Tuesday, January 15, 2013 4:47 AM
  • Hi Vicky,

    I noticed that:

    WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<worksheetpart>();</worksheetpart>

    So you need not query the Worksheet part, please use newWorksheetPart.Worksheet.Descendants<SheetData>().First() to the ExportDataTable1 method.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 15, 2013 6:29 AM
    Moderator
  • Hi sir,

    i am not getting where this code is need to set could u tell me the exact location?

    thanks


    vicky Bangalore

    Tuesday, January 15, 2013 7:16 AM
  • Hi Vicky,

    I would provide your a sample tomorrow.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 16, 2013 6:27 AM
    Moderator
  • Hi Tom,

    Thanks for your response.

    i'll wait for you reply.

    thanks


    vicky Bangalore

    Wednesday, January 16, 2013 8:38 AM
  • Hi Vicky,

    Please take a look at this.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using com.mksword.Net.OpenXmlTools;
    using com.mksword.OpenXmlUtil_log4netTool;
    
    namespace ConsoleApplication15
    {
        public class Class1: ExcelTool
        {
            public override void Action()
            {
                try
                {
                    if (_Core.HandOpenSpreadsheetDocumentOnLoacl(
                        PropulateDataToSpecificSheet, true))
                    {
                        SetLog("Missing acomplished!", OXULogType.INFO);
                    }
                    else
                    {
                        SetLog("Missing failed!", OXULogType.ERROR);
                    }
                }
                catch (Exception ex)
                {
                    SetLog(ex.Message, OXULogType.FATAL);
                }
            }
    
            // Key code is in this method, you need not take more care about Action
            // method. It just method to wrap frequently code which will used in 
            // Open Xml program - It is meaningless for your issue.
            private bool PropulateDataToSpecificSheet(SpreadsheetDocument SSD)
            {
                bool result = true;
                string SheetName = "Sheet2";
                WorkbookPart WP = SSD.WorkbookPart;
                Workbook WB = WP.Workbook;
                // Find Sheet then retrieve the WorksheetPart it referenced.
                // This code will fit the situation which your thread title mentioned.
                Sheet S = WB.Descendants<Sheet>().Where(SH => SH.Name == SheetName)
                    .FirstOrDefault();
                WorksheetPart WSP = WP.GetPartById(S.Id) as WorksheetPart;
                if (WSP != null)
                {
                    SheetData SD = WSP.Worksheet.Descendants<SheetData>().First();
                    // To-Do: Please use you code at that place.
                    // For example ExportDataTable1(SD);
                }
                /**
                 * Following comment will fitter the code which your provide.
                 */
                //WorksheetPart newPart = WP.AddNewPart<WorksheetPart>();
                //newPart.Worksheet = new Worksheet(new SheetData());
                //SheetData SD = newPart.Worksheet.Descendants<SheetData>().First();
                //// To-Do: Please use you code at that place.
                //// For example ExportDataTable1(SD);
                return result;
            }
        }
    
    }

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, January 17, 2013 6:53 AM
    Moderator