none
EXCEL Calculation. RRS feed

  • Question

  • Please copy and paste below code in MVC Controller Action method and run you will get one .xml file in C \Test folder location. Open that file in excel as i am not able to attach the screenshot here.         

     StringBuilder strb = new StringBuilder();
                strb.Append("<?xml version=\"1.0\"?>");
                strb.Append("<?mso-application progid=\"Excel.Sheet\"?>");
                strb.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
                strb.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
                strb.Append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
                strb.Append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
                strb.Append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
                strb.Append("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
                strb.Append("<Author>Piette, Lisa M.</Author>");
                strb.Append("<LastAuthor>Gaddam, Srikanth A.</LastAuthor>");
                strb.Append("<Created>2017-10-25T16:32:46Z</Created>");
                strb.Append("<LastSaved>2018-01-09T20:58:01Z</LastSaved>");
                strb.Append("<Version>16.00</Version>");
                strb.Append("</DocumentProperties>");
                strb.Append("<OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">");
                strb.Append("<AllowPNG/>");
                strb.Append("</OfficeDocumentSettings>");
                strb.Append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                strb.Append("<WindowHeight>6930</WindowHeight>");
                strb.Append("<WindowWidth>20490</WindowWidth>");
                strb.Append("<WindowTopX>0</WindowTopX>");
                strb.Append("<WindowTopY>0</WindowTopY>");
                strb.Append("<ProtectStructure>False</ProtectStructure>");
                strb.Append("<ProtectWindows>False</ProtectWindows>");
                strb.Append("</ExcelWorkbook>");
                strb.Append("<Styles>");
                strb.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
                strb.Append("<Alignment ss:Vertical=\"Bottom\"/>");
                strb.Append("<Borders/>");
                strb.Append("<Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\"/>");
                strb.Append("<Interior/>");
                strb.Append("<NumberFormat/>");
                strb.Append("<Protection/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"m382016688\">");
                strb.Append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("</Borders>");
                strb.Append("<Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#FF0000\" ss:Bold=\"1\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"m382016708\">");
                strb.Append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("</Borders>");
                strb.Append("<Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#FF0000\" ss:Bold=\"1\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s62\">");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("</Borders>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s63\">");
                strb.Append("<Alignment ss:Horizontal=\"Right\" ss:Vertical=\"Bottom\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("</Borders>");
                strb.Append("<Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#808080\" ss:Bold=\"1\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s64\">");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("</Borders>");
                strb.Append("<Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#808080\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s72\">");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("</Borders>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s73\">");
                strb.Append("<Alignment ss:Horizontal=\"Right\" ss:Vertical=\"Bottom\"/>");
                strb.Append("<Borders/>");
                strb.Append("<Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#808080\" ss:Bold=\"1\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s74\">");
                strb.Append("<Borders/>");
                strb.Append("<Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#808080\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s75\">");
                strb.Append("<Borders/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s76\">");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("</Borders>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s77\">");
                strb.Append("<Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s78\">");
                strb.Append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("</Borders>");
                strb.Append("<Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\" ss:Bold=\"1\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s79\">");
                strb.Append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("</Borders>");
                strb.Append("<Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\" ss:Bold=\"1\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s80\">");
                strb.Append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("</Borders>");
                strb.Append("<Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\" ss:Bold=\"1\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s81\">");
                strb.Append("<Alignment ss:Vertical=\"Center\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s82\">");
                strb.Append("<Alignment ss:Vertical=\"Center\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("</Borders>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s83\">");
                strb.Append("<Alignment ss:Vertical=\"Center\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("</Borders>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s84\">");
                strb.Append("<Alignment ss:Vertical=\"Center\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("</Borders>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s85\">");
                strb.Append("<Alignment ss:Vertical=\"Center\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("</Borders>");
                strb.Append("<NumberFormat ss:Format=\"0.0\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s86\">");
                strb.Append("<Alignment ss:Vertical=\"Center\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("</Borders>");
                strb.Append("<NumberFormat ss:Format=\"0.0\"/>");
                strb.Append("</Style>");
                strb.Append("<Style ss:ID=\"s87\">");
                strb.Append("<Alignment ss:Vertical=\"Center\"/>");
                strb.Append("<Borders>");
                strb.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
                strb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                strb.Append("</Borders>");
                strb.Append("<NumberFormat ss:Format=\"0.0\"/>");
                strb.Append("</Style>");
                strb.Append("</Styles>");
                strb.Append("<Worksheet ss:Name=\"DBO Metrics Summary\">");
                strb.Append("<Names>");
                strb.Append("<NamedRange ss:Name=\"_FilterDatabase\" ss:RefersTo=\"='DBO Metrics Summary'!R3C1:R5C21\" ss:Hidden=\"1\"/>");
                strb.Append("</Names>");
                strb.Append("<Table ss:ExpandedColumnCount=\"22\" ss:ExpandedRowCount=\"5\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultRowHeight=\"15\">");
                strb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"100.5\" ss:Span=\"1\"/>");
                strb.Append("<Column ss:Index=\"3\" ss:Width=\"54\" ss:Span=\"5\"/>");
                strb.Append("<Column ss:Index=\"9\" ss:Width=\"52.5\"/>");
                strb.Append("<Column ss:Index=\"15\" ss:AutoFitWidth=\"0\" ss:Width=\"50.25\"/>");

                strb.Append("<Row ss:AutoFitHeight=\"0\">");
                strb.Append("<Cell ss:StyleID=\"s62\"/>");
                strb.Append("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"String\">Standard weights</Data></Cell>");
                strb.Append("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"Number\">0.25</Data></Cell>");
                strb.Append("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"Number\">0.15</Data></Cell>");
                strb.Append("<Cell ss:StyleID=\"s64\"/>");
                strb.Append("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"Number\">0.15</Data></Cell>");
                strb.Append("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"Number\">0.3</Data></Cell>");
                strb.Append("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"Number\">0.15</Data></Cell>");

                strb.Append("<Cell ss:MergeAcross=\"6\" ss:StyleID=\"m382016688\"><Data ss:Type=\"String\">Standard Results</Data></Cell>");
                strb.Append("<Cell ss:MergeAcross=\"6\" ss:StyleID=\"m382016708\"><Data ss:Type=\"String\">Sales Results</Data></Cell>");

                strb.Append("</Row>");



                strb.Append("<Row ss:AutoFitHeight=\"0\">");
                strb.Append("<Cell ss:StyleID=\"s72\"/>");
                strb.Append("<Cell ss:StyleID=\"s73\"><Data ss:Type=\"String\">Sales weights</Data></Cell>");
                strb.Append("<Cell ss:StyleID=\"s74\"><Data ss:Type=\"Number\">0.1</Data></Cell>");
                strb.Append("<Cell ss:StyleID=\"s74\"/>");
                strb.Append("<Cell ss:StyleID=\"s74\"><Data ss:Type=\"Number\">0.2</Data></Cell>");
                strb.Append("<Cell ss:StyleID=\"s74\"><Data ss:Type=\"Number\">0.05</Data></Cell>");
                strb.Append("<Cell ss:StyleID=\"s74\"><Data ss:Type=\"Number\">0.3</Data></Cell>");
                strb.Append("<Cell ss:StyleID=\"s74\"><Data ss:Type=\"Number\">0.35</Data></Cell>");
                strb.Append("<Cell ss:StyleID=\"s72\"/>");
                strb.Append("<Cell ss:StyleID=\"s75\"/>");
                strb.Append("<Cell ss:StyleID=\"s75\"/>");
                strb.Append("<Cell ss:StyleID=\"s75\"/>");
                strb.Append("<Cell ss:StyleID=\"s75\"/>");
                strb.Append("<Cell ss:StyleID=\"s75\"/>");
                strb.Append("<Cell ss:StyleID=\"s76\"/>");
                strb.Append("<Cell ss:StyleID=\"s72\"/>");
                strb.Append("<Cell ss:StyleID=\"s75\"/>");
                strb.Append("<Cell ss:StyleID=\"s75\"/>");
                strb.Append("<Cell ss:StyleID=\"s75\"/>");
                strb.Append("<Cell ss:StyleID=\"s75\"/>");
                strb.Append("<Cell ss:StyleID=\"s75\"/>");
                strb.Append("<Cell ss:StyleID=\"s76\"/>");
                strb.Append("</Row>");
                strb.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"28.6875\" ss:StyleID=\"s77\">");
                strb.Append("<Cell ss:StyleID=\"s78\"><Data ss:Type=\"String\">Platform</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Business Unit</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">DP Margin</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Functionals</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Gross to Net</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">JVM</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Market Share</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s80\"><Data ss:Type=\"String\">Net Sales</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s78\"><Data ss:Type=\"String\">DP Margin</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Functionals</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Gross to Net</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">JVM</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Market Share</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Net Sales</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s80\"><Data ss:Type=\"String\">Total</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s78\"><Data ss:Type=\"String\">DP Margin</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Functionals</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Gross to Net</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">JVM</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Market Share</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s79\"><Data ss:Type=\"String\">Net Sales</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s80\"><Data ss:Type=\"String\">Total</Data></Cell>");
                strb.Append("</Row>");
                strb.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"30\" ss:Hidden=\"1\" ss:StyleID=\"s81\">");
                strb.Append("<Cell ss:StyleID=\"s82\"><Data ss:Type=\"String\">Comp Use Only</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"String\"></Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s84\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s85\" ss:Formula=\"=ROUND(RC[-6]*R1C3,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-6]*R1C4,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-6]*R1C5,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-6]*R1C6,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-6]*R1C7,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-6]*R1C8,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s87\" ss:Formula=\"=SUBTOTAL(9,RC[-6]:RC[-1])\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s85\" ss:Formula=\"=ROUND(RC[-13]*R2C3,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>);");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-13]*R2C4,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-13]*R2C5,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-13]*R2C6,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-13]*R2C7,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-13]*R2C8,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s87\" ss:Formula=\"=SUM(RC[-6]:RC[-1])\"><Data ss:Type=\"Number\">0</Data></Cell>");
                strb.Append("</Row>");
                strb.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"30\" ss:StyleID=\"s81\">");
                strb.Append("<Cell ss:StyleID=\"s82\"><Data ss:Type=\"String\">Global Commercial</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"String\">Global AC</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s83\"><Data ss:Type=\"Number\">113</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s84\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s85\" ss:Formula=\"=ROUND(RC[-6]*R1C3,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-6]*R1C4,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-6]*R1C5,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-6]*R1C6,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-6]*R1C7,1)\"><Data ss:Type=\"Number\">33.9</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-6]*R1C8,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s87\" ss:Formula=\"=SUBTOTAL(9,RC[-6]:RC[-1])\"><Data ss:Type=\"Number\">33.9</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s85\" ss:Formula=\"=ROUND(RC[-13]*R2C3,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-13]*R2C4,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-13]*R2C5,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-13]*R2C6,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-13]*R2C7,1)\"><Data ss:Type=\"Number\">33.9</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s86\" ss:Formula=\"=ROUND(RC[-13]*R2C8,1)\"><Data ss:Type=\"Number\">0</Data><NamedCell ss:Name=\"_FilterDatabase\"/></Cell>");
                strb.Append("<Cell ss:StyleID=\"s87\" ss:Formula=\"=SUM(RC[-6]:RC[-1])\"><Data ss:Type=\"Number\">33.9</Data></Cell>");
                strb.Append("</Row>");
                strb.Append("</Table>");
                strb.Append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                strb.Append("<Unsynced/>");
                strb.Append("<Print>");
                strb.Append("<ValidPrinterInfo/>");
                strb.Append("<HorizontalResolution>600</HorizontalResolution>");
                strb.Append("<VerticalResolution>600</VerticalResolution>");
                strb.Append("</Print>");
                strb.Append("<Selected/>");
                strb.Append("<Panes>");
                strb.Append("<Pane>");
                strb.Append("<Number>3</Number>");
                strb.Append("<ActiveRow>10</ActiveRow>");
                strb.Append("<ActiveCol>5</ActiveCol>");
                strb.Append("</Pane>");
                strb.Append("</Panes>");
                strb.Append("<ProtectObjects>False</ProtectObjects>");
                strb.Append("<ProtectScenarios>False</ProtectScenarios>");
                strb.Append("</WorksheetOptions>");
                strb.Append("</Worksheet>");
                strb.Append("</Workbook>");

                XmlDocument doc = new XmlDocument();
                doc.LoadXml(strb.ToString());
                doc.Save(@"C:\Test\x.xml");

                return View();
    Some one please help me on this Complex Logic: Attached is the screenshot of the excel file. Please find the requirement as below:

     1) i have list of objects (Max 8/)  like Standard weights, sales weights,..etc . In the screenshot i have shown only two objects.ie, Standard Results  and Sales results.

    Note: These objects are different for each Fiscal Year.

    2)From database i am getting the DPMargin, Functional, Grossnet, JVM, MarketShare, NetSales values.

    If Standard result object (Row 1 in Screenshot) is available, then i need to Multiply the standardResults row values with Database values and need to display the result in Separate "Standard Result" block with all Values (DPMargin, Functional, Grossnet, JVM, MarketShare, NetSales)

    If Sales result object is available (Row 2 in Screenshot), then i need to Multiply the Sales Results row values with Database values and need to display the result in Separate "Standard Result" block with all Values (DPMargin, Functional, Grossnet, JVM, MarketShare, NetSales)

    But if we have only "sales Result" object is available then we need to display only SalesResult block.

    Means how many number of objects are available that many blocks we need to display.

    File should be .xml only. I have opened the xml file in EXCEL to explain it better.

    I trried some code which i shared here but it is working for only one row only. If i have multiple rows are available platform section, these rows are not getting calculated (Multiply with Object values).

    I am almost dying to resolve this logic. Please help me some one.

    My Gmail is: srikanthnet@outlook.com , if you need any other information, please send an email to me. 










    Friday, July 27, 2018 9:18 PM

All replies

  • NOTE: This does not directly address your current task but instead offers a cleaner approach to working with Excel, VB.NET and XML.

    That's a lot of "stuff" to go through, I would suggest creating the Excel file as it should look, save it to XML format then work with it in code as I have a code sample which does pretty much what you are w/o any string concatenation which in turn should make it easier to figure out the current task.

    Screenshot from link above.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, July 27, 2018 9:27 PM
    Moderator