Free Support Forum - aspose.com

Re: How to use existing excel templates

Hi Kevin,

Well, I used your template .xml file, opened it, filled some data to the cells and saved it as .xls format. Every thing is fine including its format when I opened the resultant file into MS Excel.

Following is my testing code:

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=f:\\test\\mydb.mdb");
con.Open();
OleDbCommand cmd = new OleDbCommand("Select * from Table1",con);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds,"Table1");
Workbook wb = new Workbook();
wb.Open("f:\\test\\DailyRunSheetTemplate.xml",FileFormatType.SpreadsheetML);
Worksheet ws = wb.Worksheets[0];
//Import the datatable
ws.Cells.ImportDataTable(ds.Tables["Table1"],false, "A7");
wb.Save("f:\\test\\new_DailyRunSheetTemplate.xls");

Could you post your both input and output files with sample code here to reproduce the issue. We will check it soon.

Thank you.

Hi Kevin,

Well, I used your template .xml file, opened it, filled some data to the cells and saved it as .xls format. Every thing is fine including its format when I opened the resultant file into MS Excel.

Following is my testing code:

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=f:\\test\\mydb.mdb");
con.Open();
OleDbCommand cmd = new OleDbCommand("Select * from Table1",con);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds,"Table1");
Workbook wb = new Workbook();
wb.Open("f:\\test\\DailyRunSheetTemplate.xml",FileFormatType.SpreadsheetML);
Worksheet ws = wb.Worksheets[0];
//Import the datatable
ws.Cells.ImportDataTable(ds.Tables["Table1"],false, "A7");
wb.Save("f:\\test\\new_DailyRunSheetTemplate.xls");

Could you post your both input and output files with sample code here to reproduce the issue. We will check it soon.

Thank you.

Hi Amjad

Thanks for your reply. Soon after my emai I discovered the problem,

WbkDesigner.Open(String.Concat(theTemplateFilePath, theTemplateFileName))
WbkDesigner.Workbook.Save(String.Concat(theOutputFilePath, theOutputFileName))

I was doing a save immediately after opening the designer, and then continuing to populate it and then a final save. I don't understand the logic why it failed but at least I know now that the save before populating was blowing away the formatting or first worksheet.

Can you give me a little more help, I now have it populating but there are a couple of other details I have not got right yet...

1) whats the best way to apply the styles if we are using the import datasource method ?

2) Because I am hoping to pre-format the designtime templates - I am am using the designer class -
is this really necessary or it only a benefit if I use smart tags and could equally be using just the workbook class ?

3) the doco says the designer template should be a .xls format file ? is this critical to retaining formating or something ?

4) Because I dont know how long a worksheet will be until populated, the designtime formatting is appied to something like 60 rows in the first worksheet. The issue then is when you go to print it any remaining empty rows are treated as if they contain data - so the user gets lots of blank pages.

The only way I can think around this is to apply a row style one row at a time programmatically.

This will be difficult to create as there is quite a bit of formatting applied to get the columns and cells right.

How do I create a row style that contains styles for each column and can it be applied, as I am using the setdatasource method or have I got to loop throught the rows and use putvalue now ?

Please see attached for my attempt.

ps. what time zone are you in ? do you guys work on the support forum.on the weekend ?
many thanks for all your help.
kevin
Brisbane Australia

Hi,

1) whats the best way to apply the styles if we are using the import datasource method ?

Well, you may format the cells either before importing data from the datasource to the cells or after you have filled the data to the cells. I think you should create a style object, set its attributes for your desired formattings, then apply style to your desired cells, row, column etc.

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/approaches-to-format-data-in-cells.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/setting-display-formats-of-numbers-dates.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/dealing-with-font-settings.html

2) Because I am hoping to pre-format the designtime templates - I am am using the designer class -
is this really necessary or it only a benefit if I use smart tags and could equally be using just the workbook class ?

No, WorkbookDesigner class is not necessary here, you may use Workbook class. But if you are using the smart markers, you must use it then.

3) the doco says the designer template should be a .xls format file ? is this critical to retaining formating or something ?

No, your designer class can be in any format i.e. .xls, xlsx, xlt, csv, .xml, txt etc.

4) Because I dont know how long a worksheet will be until populated, the designtime formatting is appied to something like 60 rows in the first worksheet. The issue then is when you go to print it any remaining empty rows are treated as if they contain data - so the user gets lots of blank pages.

I think you can set PageSetup.PrintArea for your requirement.

The only way I can think around this is to apply a row style one row at a time programmatically.

This will be difficult to create as there is quite a bit of formatting applied to get the columns and cells right.

Generally you should do it to apply style to a row / column using Cells.ApplyRowStyle and Cells.ApplyColumnStyle, but you can set the default style for the whole workbook using Workbook.DefaultStyle attribute.

How do I create a row style that contains styles for each column and can it be applied, as I am using the setdatasource method or have I got to loop throught the rows and use putvalue now ?

Sorry, I Could not pick it. Perhaps you want how to apply style to an entire row:

e.g..,

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
Style style1;
StyleFlag flag1;
style1 = wb.Styles[wb.Styles.Add()];
style1.Custom = "yyyy-mm-dd";
flag1 = new StyleFlag();
flag1.NumberFormat = true;

//Apply style to the first column.
sheet.Cells.ApplyColumnStyle(0,style1, flag1);

Style style2;
StyleFlag flag2;
style2 = wb.Styles[wb.Styles.Add()];
style2.Number = 39;
flag2 = new StyleFlag();
flag2.NumberFormat = true;
//Apply style to the 11th row.
sheet.Cells.ApplyRowStyle(10,style2, flag2);

wb.Save("d:\\test\\rowandcolumn_formattings.xls");

ps. what time zone are you in ? do you guys work on the support forum.on the weekend ?
many thanks for all your help.

We are in asia pacific. If there are some queries from our customers, we can work on weekend too.

Thank you.

Hi Amjad

Thank you for your excellent reply, I have been working to implment the aspose.cells into my winform app, and nearly there. I will read your detailed reply, but in the mean time I want to purchase the cells produt but before doing so and thinking of also buying the aspose editor for .net, I have had a bit of a look on the site and can only find examples how to open and save a file to and from the hard drive. I need to somehow save the file as maybea stream and then have my dataccess layer save the stream to the database and similarly open some data stored in the database for editing. I am a bit new to streams and see we can use file or memory streams have you got any examples show how this could be done.

Also think I can create a oie chart with aspose.cells, is there a pier chart example available.

many thanks for all your help.

kevin

Hi,

Thanks for considering Aspose.

Well, Aspose.Cells for .NET provide some overloaded versions for Workbook.Open() method that allows to open the workbook data from streams: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/aspose.cells.workbook.open_overloads.html Similarly you can save the workbook data to streams using an overloaded version of Workbook.Save() method: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/aspose.cells.workbook.save_overloads.html

Sample code (the code shows how to save workbook data to memory streams) :

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
MemoryStream modelStream = new MemoryStream();
workbook.Open(@"d:\test\Test_File.xls", FileFormatType.Excel2003);
workbook.Save(modelStream, Aspose.Cells.FileFormatType.Excel2003);

For importing data from database, you may try some methods (you may extract data from base database table(s) to the worksheet) e.g.., Cells.ImportDataTable(), Cells.ImportFromDataReader(), Cells.ImportDataView() etc, Please check: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/importing-data-to-worksheets.html

Aspose.Cells for .NET support to create all types (2D and 3D oriented) of standard (pie, bar, column, line etc. ) and custom charts using its APIs, For creating charts please check the sections in the documentation: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/creating-charts.html

Pie Chart Sample Example:

Workbook workbook = new Workbook();
//Set default font
Style style = workbook.DefaultStyle;
style.Font.Name = "Tahoma";
workbook.DefaultStyle = style;
Worksheet sheet = workbook.Worksheets[0];
//Set the name of worksheet
sheet.Name = "Data";
Cells cells = workbook.Worksheets[0].Cells;
//Put a value into a cell
cells["A1"].PutValue("Region");
cells["A2"].PutValue("France");
cells["A3"].PutValue("Germany");
cells["A4"].PutValue("England");
cells["A5"].PutValue("Sweden");
cells["A6"].PutValue("Italy");
cells["A7"].PutValue("Spain");
cells["A8"].PutValue("Portugal");
cells["B1"].PutValue("Sale");
cells["B2"].PutValue(70000);
cells["B3"].PutValue(55000);
cells["B4"].PutValue(30000);
cells["B5"].PutValue(40000);
cells["B6"].PutValue(35000);
cells["B7"].PutValue(32000);
cells["B8"].PutValue(10000);

int sheetIndex = workbook.Worksheets.Add();
sheet = workbook.Worksheets[sheetIndex];
//Set the name of worksheet
sheet.Name = "Chart";
//Create chart
int chartIndex = 0;
chartIndex = sheet.Charts.Add(ChartType.Pie,1,1,25,10);
Chart chart = sheet.Charts[chartIndex];
//Set properties of chart
chart.PlotArea.Area.ForegroundColor = Color.Coral;
chart.PlotArea.Area.FillFormat.SetTwoColorGradient(Color.Yellow,Color.White,GradientStyleType.Vertical,2);
chart.PlotArea.Border.IsVisible = false;
//Set properties of chart title
chart.Title.Text = "Sales By Region";
chart.Title.TextFont.Color = Color.Blue;
chart.Title.TextFont.IsBold = true;
chart.Title.TextFont.Size = 12;
//Set properties of nseries
chart.NSeries.Add("Data!B2:B8", true);
chart.NSeries.CategoryData = "Data!A2:A8";
chart.NSeries.IsColorVaried = true;
//Set the DataLabels in the chart
DataLabels datalabels;
for ( int i = 0; i < chart.NSeries.Count ;i ++ )
{
datalabels = chart.NSeries[i].DataLabels;
datalabels.Postion = LabelPositionType.InsideBase;
datalabels.IsCategoryNameShown = true;
datalabels.IsValueShown = true;
datalabels.IsPercentageShown = false;
datalabels.IsLegendKeyShown = false;

}
//Set the ChartArea
ChartArea chartarea = chart.ChartArea;
chartarea.Area.Formatting = FormattingType.Custom;
chartarea.Area.FillFormat.Texture = TextureType.BlueTissuePaper;
//Set the Legend.
Legend legend = chart.Legend;
legend.Position = LegendPositionType.Left;
legend.Height = 100;
legend.Width = 130;
legend.Y= 1500;
legend.TextFont.IsBold= true;
legend.Border.Color = Color.Blue;
legend.Area.Formatting = FormattingType.Custom;

//Settting FillFormat.
FillFormat fillformat = legend.Area.FillFormat;
fillformat.Texture = TextureType.Bouquet;

//legend.Area.FillFormat.SetTwoColorGradient(Color.Yellow,Color.White,GradientStyleType.Horizontal,1);
//Save the excel file
workbook.Save("d:\\test\\pie_chart211.xls");

Thank you.