none
How to insert data in multiple sheets in the excel using open xml RRS feed

  • Question

  • Hi,

    i have been trying to insert data to multiple sheets.For my excel i have two sheets which is "charts" sheet and "ChartData" sheet, i'm able to update the data to the sheet2 i.e chartdata sheet but i'm unable to insert data to the sheet1.Here is the below code which i have tried to insert data to excel sheets.Here data is coming from database.

        

    double ticks = DateTime.Now.Ticks;
                // MarketAnalysis ms = new MarketAnalysis();
                //ms.Marketanalysis();

                File.Copy(Srcpath, @"E:\Works\OpenXML\DownloadTemplates\ExcelGenerated" + ticks + ".xlsx", true);
                using (SpreadsheetDocument myworkbok = SpreadsheetDocument.Open(@"E:\Works\OpenXML\DownloadTemplates\ExcelGenerated" + ticks + ".xlsx", true))
                {
                    //Acess the main workbook which contain all the references

                    WorkbookPart workbookpart = myworkbok.WorkbookPart;
                    //Get sheet by name
                    Sheet sheet = workbookpart.Workbook.Descendants<Sheet>().Where(s => s.Name == "ChartData").FirstOrDefault();

                    //Worksheet Part by ID
                    WorksheetPart worksheetpart = workbookpart.GetPartById(sheet.Id) as WorksheetPart;

                    //Sheet data contains all the data
                    SheetData sheetdata = worksheetpart.Worksheet.GetFirstChild<SheetData>();
                    DataSet ds = db.Chart1Data();
                    
                     for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        //  if (ds.Tables[0].Rows !=DBNull)
                        //{

                        string Rowlabel = ds.Tables[0].Rows[i][0].ToString();
                        // float? FY13Actuval = Convert.ToInt32(ds.Tables[0].Rows[i][1]);

                        if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i][1].ToString()))
                        {
                            // string s = ds.Tables[0].Rows[i][1].ToString();
                            //FY13Actuval=float.Parse(ds.Tables[0].Rows[i][1].ToString());

                            FY13Actuval = Convert.ToDouble(ds.Tables[0].Rows[i][1].ToString());
                        }
                        else
                        {

                            FY13Actuval = null;
                        }

                        double? Budget = Convert.ToDouble(ds.Tables[0].Rows[i][2].ToString());
                        double? Actuval = Convert.ToDouble(ds.Tables[0].Rows[i][3].ToString());
                        Row contentrow = CreateContentRow(index, Product, Actual, Budget, Forecast);

                        index++;
                        sheetdata.AppendChild(contentrow);

                        // }
                    }
        .......Same code for the other 3 charts
        Sheet sheet1 = workbookpart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Charts").FirstOrDefault();

                    WorksheetPart worksheetpart1 = workbookpart.GetPartById(sheet1.Id) as WorksheetPart;

                    //Sheet data contains all the data
                    SheetData sheetdata1 = worksheetpart1.Worksheet.GetFirstChild<SheetData>();

                    DataSet dsTbl = db.Table();
                    int SCMTblIndex=5;
                    for (int i = 0; i < dsTbl.Tables[0].Rows.Count; i++)
                    {
                        Row tblRow = CreateScorecardMetricTblRow(SCMTblIndex, dsTbl.Tables[0].Rows[i][0].ToString(),
                                                                           dsTbl.Tables[0].Rows[i][1].ToString(),
                                                                           dsTbl.Tables[0].Rows[i][2].ToString());
                        SCMTblIndex++;
                        sheetdata1.AppendChild(tblRow);
                    }

                        myworkbok.WorkbookPart.Workbook.Save();
                }

    and the Methods to create the cells

    public static string[] headerColumns = new string[] { "A", "B", "C", "D", "E", "F", "G", "I", "J" };
            public static string[] header = new string[] { "D", "E", "F" };

    private static Row CreateContentRow(int index, string Product, double? Actual, double? Budget, double? Forecast)
            {

                //Create New ROw
                Row r = new Row();
                r.RowIndex = (UInt32)index;


                //Begin colums
                Cell c0 = new Cell();
                c0.CellReference = headerColumns[0] + index;
                CellValue v0 = new CellValue();
                v0.Text = Product;
                c0.AppendChild(v0);
                r.AppendChild(c0);

                Cell c1 = new Cell();
                c1.CellReference = headerColumns[1] + index;
                CellValue v1 = new CellValue();
                v1.Text = Actual.ToString();
                c1.AppendChild(v1);
                r.AppendChild(c1);

                Cell c2 = new Cell();
                c2.CellReference = headerColumns[2] + index;
                CellValue v2 = new CellValue();
                v2.Text = Budget.ToString();
                c2.AppendChild(v2);
                r.AppendChild(c2);

                Cell c3 = new Cell();
                c3.CellReference = headerColumns[3] + index;
                CellValue v3 = new CellValue();
                v3.Text = Forecast.ToString();
                c3.AppendChild(v3);
                r.AppendChild(c3);


                return r;


            }
    public static Row CreateScorecardMetricTblRow(int index, string Act_Data, string Bud_Data, string VarPr_Data)
            {
                Row r = new Row();
                r.RowIndex = (UInt32)index;
                //Begin Colums

                Cell c0 = new Cell();
                c0.CellReference = header[0] + index;
                CellValue v0 = new CellValue();
                v0.Text = Act_Data;
                c0.AppendChild(v0);
                r.AppendChild(c0);

                Cell c1 = new Cell();
                c1.CellReference = header[1] + index;
                CellValue v1 = new CellValue();
                v1.Text = Bud_Data;
                c1.AppendChild(v1);
                r.AppendChild(c1);

                Cell c2 = new Cell();
                c2.CellReference = header[2] + index;
                CellValue v2 = new CellValue();
                v2.Text = VarPr_Data;
                c2.AppendChild(v2);
                r.AppendChild(c2);

                return r;
            }

    Thursday, March 21, 2013 10:27 AM

All replies