Hi,
i am creating excel using c# code.
here is my code.
Excel.Application XL =new Microsoft.Office.Interop.Excel.Application();
Excel._Workbook WB;
Excel._Worksheet Sheet;
Excel.Range oRng;
Object objSaveAsFile = (Object)SaveAsFile(StrDocName,"Xls");
object objMissing = System.Reflection.Missing.Value;
Excel.ChartObjects chartObjects = null;
XL = new Excel.Application();
XL.Visible = true;
//Get a new workbook.
WB = (Excel._Workbook)(XL.Workbooks.Add(Missing.Value));
//Excel.Chart xlChart = (Excel.Chart)(XL.Charts.Add(Type.Missing, Type.Missing, Type.Missing,
Type.Missing));
WB.SaveAs(objSaveAsFile,
Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Sheet = (Excel._Worksheet)WB.ActiveSheet;
chartObjects = (Excel.ChartObjects)(Sheet.ChartObjects(Type.Missing));
oRng = Sheet.get_Range("A1", "D1");
oRng.Interior.Color = System.Drawing.Color.Teal.ToArgb();
oRng = Sheet.get_Range("B1", "B65536");
oRng.EntireColumn.ColumnWidth = 90;
oRng = Sheet.get_Range("D1", "D65536");
oRng.EntireColumn.ColumnWidth = 40;
Sheet.Cells[1, 1] = Title + " - " + " Results";
Sheet.get_Range("A1:Z1", Type.Missing).Merge(Type.Missing);
Sheet.get_Range("A1", "A" + dtReturn.Rows.Count).EntireColumn.AutoFit();
//Add table headers going cell by cell.
Sheet.Cells[2, 1] = dtReturn.Columns[0].ColumnName;
Sheet.Cells[2, 2] = dtReturn.Columns[1].ColumnName;
Sheet.Cells[2, 3] = dtReturn.Columns[2].ColumnName;
Sheet.Cells[2, 4] = dtReturn.Columns[3].ColumnName;
//Format A1:D1 as bold, vertical alignment = center.
Sheet.get_Range("A2", "D2").Font.Bold = true;
Sheet.get_Range("A1", "D1").Font.Bold = true;
Sheet.get_Range("A1", "D1").Font.Size = 12;
Sheet.get_Range("A2", "D2").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
for (int i = 0; i < dtReturn.Rows.Count; i++)
{
Sheet.Cells[i + 3, 1] = dtReturn.Rows[i]["SNo"].ToString();
Sheet.Cells[i + 3, 2] = dtReturn.Rows[i]["Question"].ToString();
oRng = Sheet.get_Range("C"+(i+3),"C"+(i+3));
Sheet.Cells[i + 3, 3] = dtReturn.Rows[i]["Response"].ToString();
if (dtReturn.Rows[i]["Response"].ToString() == "Yes")
{
oRng.Font.ColorIndex = 17;
}
else
{
oRng.Font.ColorIndex = 13;
}
Sheet.Cells[i + 3, 4] = dtReturn.Rows[i]["Reason"].ToString();
}
Sheet.get_Range("A1", "D1").RowHeight = 21;
//AutoFit columns A:D.
//oRng = Sheet.get_Range("A1", "Z1");
//Work***.get_Range(A1:A1).get_End(Excel.XlDirection.xlDown).Row
int intRowcount = oRng.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell,
Missing.Value).Row;
Sheet.Cells[intRowcount+2, 2] = "Result:";
Sheet.Cells[intRowcount+3, 2] = "Yes";
Sheet.Cells[intRowcount+4, 2] = "No";
Sheet.Cells[intRowcount + 3, 3] = intYes;
Sheet.Cells[intRowcount + 4, 3] = intNo;
oRng = null;
//Sheet.Cells[intRowcount + 1, 1] = "welcome";
oRng = Sheet.get_Range("A1", "D" + intRowcount);
oRng.Select();
oRng.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle =
Excel.XlLineStyle.xlLineStyleNone;
oRng.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
oRng.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
oRng.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
object objFrom = "B" + (intRowcount + 3);
object objTo = "C" + (intRowcount + 4);
oRng = Sheet.get_Range(objFrom, objTo);
//oRng = Sheet.get_Range("B"+intRowcount+3, "C" + intRowcount+4);
//oRng = Sheet.get_Range("B8" , "C9");
oRng.Select();
// Excel pie Chart Drawing.
Excel.ChartObjects charts = (Excel.ChartObjects)Sheet.ChartObjects(Type.Missing);
Excel.ChartObject objChart = charts.Add(150, 20, 300, 200);
objChart.Chart.SetSourceData(oRng, Excel.XlRowCol.xlColumns);
objChart.Chart.ChartType = Excel.XlChartType.xlPie;
objChart.Chart.HasTitle = true;
objChart.Chart.ChartTitle.Text = "Result";
objChart.Chart.ChartArea.Interior.ColorIndex = 14;
objChart.Chart.ChartArea.Interior.Pattern = 1;
Excel.SeriesCollection seriesCollection =
(Excel.SeriesCollection)objChart.Chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.Item(seriesCollection.Count);
series.ApplyDataLabels(Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowValue,
true, true, false, false, false, true, true, false, Type.Missing);
oRng = Sheet.get_Range("B" + (intRowcount + 2), "B" + (intRowcount + 2));
oRng.Font.Bold = true;
oRng.Font.Size = 11;
WB.Save();
XL.Visible = true;
XL.UserControl = true;
when the line comes to WB.save() then in my excel it asks like this:
the following shape from your excel workbook will not saved in the xml spreed sheet.
so my pie chart is not saving my created excel. so please any body help me in this please