i'm using following method create , position excel chart beside table data. below code working fine, chart position(height) creating problem when number of charts increase.
there solution draw chart cell value? or other thing may used me keep chart beside table data.
i want print result of following method page page , therefore want place correctly.
below code:
excel.application xla = new excel.application(); int height=75; string uppercell = "b12"; int cell = 12; if (checkbox1.checked == true) { string table_name, chart_name; table_name = "ration table"; chart_name = "ratio"; string st = ""; con.open(); trying(ws, st, height, uppercell, cell, table_name, chart_name); con.close(); cell = cell + 33; uppercell = "b" + cell; height = 496 + 75;//height + 300; } public void trying(excel.worksheet ws,string st,double height,string uppercell,int cell,string table_name,string chart_name) { mysqldataadapter da = new mysqldataadapter(st, con); system.data.datatable dtmainsqldata = new system.data.datatable(); da.fill(dtmainsqldata); datacolumncollection dccollection = dtmainsqldata.columns; //int cell = system.int32.parse(uppercell.substring(1)); //********************** create chart. ***************************** excel.chartobjects chartobjs = (excel.chartobjects)ws.chartobjects(type.missing); //excel.chartobject chartobj = chartobjs.add(left, top, width, height); excel.chartobject chartobj = chartobjs.add(260, height, 350, 210); excel.chart xlchart = chartobj.chart; //ws.shapes.item("xlchart").top = (float)(double)ws.get_range("e6").top;//this.controls.addchart(this.range["d2", "h12"]"chart1"); int nrows = dtmainsqldata.rows.count; int ncolumns = dtmainsqldata.columns.count; string upperleftcell = uppercell;// "b10"; int endrownumber = system.int32.parse(upperleftcell.substring(1)) + nrows - 1; char endcolumnletter = system.convert.tochar(convert.toint32(upperleftcell[0]) + ncolumns - 1); string upperrightcell = system.string.format("{0}{1}", endcolumnletter, system.int32.parse(upperleftcell.substring(1))); string lowerrightcell = system.string.format("{0}{1}", endcolumnletter, endrownumber); excel.range rg = ws.get_range(upperleftcell, lowerrightcell); (int = cell - 1; < dtmainsqldata.rows.count + cell; i++) { (int j = 2; j < dtmainsqldata.columns.count + 2; j++) { if (i == cell - 1) { xla.cells[i, j] = dccollection[j - 2].tostring(); } else { xla.cells[i, j] = dtmainsqldata.rows[i - cell][j - 2].tostring(); } } } //ws.columns.autofit(); //for (int = 1; <= dtmainsqldata.rows.count; i++) //{ // rg[1, i] = dtmainsqldata.rows[i - 1][0].tostring(); //for adding header text // rg[2, i] = int.parse(dtmainsqldata.rows[i - 1][1].tostring()); //for adding datarow value //} int lcellh = cell - 10; int rcellh = cell - 10; string lcellh1 = "b" + lcellh; string rcellh1 = "l" + rcellh; //-----------------------for company name , address-------------------------// excel.range chartrange3; ws.get_range(lcellh1, rcellh1).merge(false);//----------------------------------upperleft_cell, lowerright_cell chartrange3 = ws.get_range(lcellh1, rcellh1); chartrange3.formular1c1 = "blue bird foods (i) pvt.ltd.";//----------------------------------company name chartrange3.horizontalalignment = 3; chartrange3.verticalalignment = 3; chartrange3.interior.color = system.drawing.colortranslator.toole(system.drawing.color.yellow); chartrange3.font.color = system.drawing.colortranslator.toole(system.drawing.color.palevioletred); chartrange3.font.size = 20; excel.range formatrange3; formatrange3 = ws.get_range(lcellh1, rcellh1);//----------------------------------hrow_border1, hrow_border2 formatrange3.borderaround(excel.xllinestyle.xlcontinuous, excel.xlborderweight.xlmedium, excel.xlcolorindex.xlcolorindexautomatic, excel.xlcolorindex.xlcolorindexautomatic); //------for making header font bold int hcell = cell - 1; string hleftrange = "b" + hcell; string hrightrange = "d" + hcell; excel.range formatrange1; formatrange1 = ws.get_range(hleftrange);//----------------------------------hrow_bold formatrange1.entirerow.font.bold = true; formatrange1.horizontalalignment = 3; formatrange1.verticalalignment = 3; //ws.cells[1, 3] = "bold"; //-------for giving broder header excel.range formatrange2; formatrange2 = ws.get_range(hleftrange, hrightrange);//----------------------------------hrow_border1, hrow_border2 formatrange2.borderaround(excel.xllinestyle.xlcontinuous, excel.xlborderweight.xlmedium, excel.xlcolorindex.xlcolorindexautomatic, excel.xlcolorindex.xlcolorindexautomatic); //-------for giving broder table data excel.range formatrange; formatrange = ws.get_range(upperleftcell, lowerrightcell);//----------------------------------upperleftcell, lowerrightcell formatrange.borderaround(excel.xllinestyle.xlcontinuous, excel.xlborderweight.xlmedium, excel.xlcolorindex.xlcolorindexautomatic, excel.xlcolorindex.xlcolorindexautomatic); formatrange.horizontalalignment = 3; formatrange.verticalalignment = 3; //--------for giving name table int lcell = cell - 3; int rcell = cell - 2; string tleftrange = "b" + lcell; string trightrange = "d" + rcell; excel.range chartrange1; ws.get_range(tleftrange, trightrange).merge(false);//----------------------------------upperleft_cell, lowerright_cell formatrange2 = ws.get_range(tleftrange, trightrange); formatrange2.borderaround(excel.xllinestyle.xlcontinuous, excel.xlborderweight.xlmedium, excel.xlcolorindex.xlcolorindexautomatic, excel.xlcolorindex.xlcolorindexautomatic); chartrange1 = ws.get_range(tleftrange, trightrange); chartrange1.formular1c1 = table_name;//"ratio table";//----------------------------------table_name chartrange1.horizontalalignment = 3; chartrange1.verticalalignment = 3; chartrange1.interior.color = system.drawing.colortranslator.toole(system.drawing.color.yellow); chartrange1.font.color = system.drawing.colortranslator.toole(system.drawing.color.palevioletred); chartrange1.font.size = 20; //--------drawing chart range excel.range chartrange = ws.get_range(upperleftcell, lowerrightcell); xlchart.setsourcedata(chartrange, system.reflection.missing.value); xlchart.charttype = excel.xlcharttype.xl3dpie; // *******************customize axes: *********************** excel.axis xaxis = (excel.axis)xlchart.axes(excel.xlaxistype.xlcategory, excel.xlaxisgroup.xlprimary); //xaxis.hastitle = true; // xaxis.axistitle.text = "x axis"; excel.axis yaxis = (excel.axis)xlchart.axes(excel.xlaxistype.xlseriesaxis, excel.xlaxisgroup.xlprimary); //yaxis.hastitle = true; //yaxis.axistitle.text = "y axis"; excel.axis zaxis = (excel.axis)xlchart.axes(excel.xlaxistype.xlvalue, excel.xlaxisgroup.xlprimary); //zaxis.hastitle = true; //zaxis.axistitle.text = "z axis"; // *********************add title: ******************************* xlchart.hastitle = true; xlchart.charttitle.text = chart_name;// "ratio"; // *****************set legend:*************************** xlchart.haslegend = true; }
Comments
Post a Comment