Inserting Table into worksheet

Aspose.Cells Product Family
1

Hi,

I have the two queries on Aspose.cells. Please provide the information about this:

1) I am importing the DataTable into worksheet and preparing chart with below code:

int cnt = oWorksheet.Cells.ImportDataTable(DtGraphData, true, 20, 0, true);

int chartIndex = oWorksheet.Charts.Add(ChartType.Column, 9, 9, 21, 15);

Chart chart = oWorksheet.Charts[chartIndex];
chart.NSeries.Add(“B22:B23”, true);
chart.NSeries.CategoryData = “A22:A23”;

//ASeries aSeries = chart.NSeries[0];
//aSeries.Name = “=B1”;
chart.IsLegendShown = true;
chart.Title.Text = “Analysis”;


This is works fine for me, but DataTable is importing into sheet in given row/column indexes. I need the DataTable data in Table format with border ‘2’. Could you please provide info how to achieve this?

2) and the other one is, I don’t want to see Cells in worksheet, only table and chart is enough for me. Is there any way to handle this?

Thanks,

2

Hi,

1) Well, you may create a range based on your desired imported data and apply/set borders, see the sample code below. Note: This is just a sample code and you may modify it according to your scenario/need.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Cells cells = workbook.Worksheets[0].Cells;

DataSet ds = new DataSet();

System.Data.DataTable dt = new DataTable(“Table1”);
dt.Columns.Add(“Customer Identification Number”, typeof(int));
dt.Columns.Add(“Customer Name”, typeof(string));
dt.Columns.Add(“Description Text”, typeof(string));
for (int i = 1; i <= 200; i++)
{
System.Data.DataRow dr = dt.NewRow();
dr[“Customer Identification Number”] = i;
dr[“Customer Name”] = "Name " + i;
dr[“Description Text”] = "Description " + i;
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);

cells.ImportDataTable(ds.Tables[0], true, “A1”);

//apply style to a range of cells.
Range range = cells.CreateRange(“A1”, CellsHelper.CellIndexToName(cells.MaxDataRow,cells.MaxDataColumn));
Style stl = workbook.Styles[workbook.Styles.Add()];
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

StyleFlag flg = new StyleFlag();
flg.Borders = true;

range.ApplyStyle(stl, flg);

sheet.AutoFitColumns();

//Disable the gridlines for your need.
sheet.IsGridlinesVisible = false;

workbook.Save(“e:\test\outputfile.xls”);

2) In MS Excel Worksheet the cells are rendered with grid lines which will not be shown when taking the preview of the worksheet in MS Excel. However, you may disable gridlines for your need too, see the line of code:

//Disable the gridlines for your need.
sheet.IsGridlinesVisible = false;



Thank you.