We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Datatable to Template Excel

Am Trying to insert the values from Database to excel.

1) I took values from Database to Datatable
2) Now am trying to Download Excel with Values i done it Succesfully
3) But now am trying to Download Excel with format and formula in template excel save in disk i want to insert the values in excel and download the excel.

Hi,


We are not sure about your issue, could you elaborate your issue/ needs, so we could evaluate it properly and guide you through. Aspose.Cells can import data from the DataTable to fill the Excel sheet in the template file or new spreadsheet, see the document on different importing data options which Aspose.Cells provides including importing data from DataTable here:
http://www.aspose.com/docs/display/cellsnet/Importing+Data+to+Worksheets

Regarding your third point, could you elaborate how do you fill the formatted sheet, which Aspose.Cells APIs you are using or you are using your own codes? If possible please give us your sample code and template file here, you may create dynamic DataTable to fill the worksheet accordingly, we will check your issue soon.

Thank you.
  1. i want to fill the values in Excel that existing in Disk(template)
    2)The excel will contains cell formatting and formula in it
    3) After fill the values in that excel i want to download it

Hi Jaganlal,


Thank you for contacting support.

Please note, Aspose.Cells for .NET allows you to insert values in individual cells as well as import bulk data using any of the import methods offered by Cells class. Please check the below linked technical articles for your reference.
http://www.aspose.com/docs/display/cellsnet/Accessing+Cells+of+a+Worksheet
http://www.aspose.com/docs/display/cellsnet/Adding+Data+to+Cells
http://www.aspose.com/docs/display/cellsnet/Importing+Data+to+Worksheets

For formula related article, please refer to below link,
http://www.aspose.com/docs/display/cellsnet/Calculating+Formulas
http://www.aspose.com/docs/display/cellsnet/Using+Formulas+or+Functions+to+Process+Data

Apply formatting as per your requirements,
http://www.aspose.com/docs/display/cellsnet/Approaches+to+Format+Data+in+Cells

At last, save the file to disk in any supported format.
http://www.aspose.com/docs/display/cellsnet/Saving+Files

Please feel free to write back in case you have further questions for us.

Still i couldnt get the answer i required.
I have attached the Excel file conatins the range in that i have formatted values and formula in that
i have to fill the values taken from the datatable

Hi again,


I am afraid, your provided sample spreadsheet is corrupted therefore we were unable to see the contents in order to evaluate your requirements precisely. Please see the attached snapshot of the error.

Please check the below linked technical article on how you can manage Named Ranges, insert data into the range and apply formatting. You can opt a similar approach to iterate over the DataTable values and fill them in the range cells.
http://www.aspose.com/docs/display/cellsnet/Named+Ranges

Hope this helps a bit.

Sorry. please check this an give accurate answer to fill data into this excel and download it

Hi Jaganlal,


Please check the below provided code snippet and attached spreadsheet.

C#

DataTable table = CreateDataTable();
Workbook book = new Workbook(myDir + “Spike.xlsm”);
//Get Range by name
Range range = book.Worksheets.GetRangeByName(“test”);
//Identify range cells
int trows = range.RowCount;
int tcols = range.ColumnCount;
//Iterate over range cells and fill data
for (int row = 1; row < trows; row++ )
{
for (int col = 0; col < tcols-2; col++)
{
range[row, col].PutValue(table.Rows[row-1].ItemArray[col].ToString(), true);
}
}
//Calculate all formulas
book.CalculateFormula();
//Save results
book.Save(myDir + “output.xlsx”, SaveFormat.Xlsx);

In case you are building a web application, you may use an overload version of Save method to download the spreadsheet to local drive. The said method accepts HttpResponse, FileName, ContentDisposition and SaveFormat as parameters. Moreover, you may choose any SaveFormat from the supported list to save the results in desired format.

We would suggest you again to go through all previously shared articles in order get acquainted with Aspose.Cells API.

Thank you, It works for two rows only.
I am having 200 rows in the datatable dynamically the range should be increased
But the 200 will be dynamic some times it come 1000 or 10.
I want to increase the range dynamically
Please help me in this scenario

An then in the Output excel style applied in the Spike.xlsm as gone. it only have values and formula only no design in it. Design is missing. Please check the attachment

Hi Jaganlal,


We are currently working to provide a solution for the scenario shared at 535399. Please spare us little time to properly analyze the scenario. We will shortly get back to you with more details in this regard.

Regarding the recently shared problem of loosing formatting, you may see from the output shared from us that the formatting stays intact after inserting the values. In case you are using the latest version of Aspose.Cells for .NET 8.0.0 on your end, the problem isn’t the API but the code snippet itself. We will evaluate it further on our end by looking at your code. We request you to kindly create a sample console application (you may use some hard coded values for the data eliminating any dependencies so that the example should properly run on our side), zip it and post it here to reproduce the issue. Please also attach your template files here.

hi i have attached the console file i have worked.
In Console application the excel formatted came but the range didnt increased.
I have attached templates with this and i have attached dll what am using for the application.
Excel style is not applying while am using web application and range not increased.
Please give output as soon possible.

Hi Jaganlal,


Thank you for providing the sample application.

We have found an easy and less error prone solution for your over all scenario. We have used the Smart Markers to achieve the ultimate requirement while avoiding any formatting related problems. Please read about Smart Markers in detail from this technical article.

Please check the below provided code snippet and the attached files for your kind reference.

  • Data.xlsx is actually the same file containing the data to be inserted in the resultant spreadsheet.
  • Designer.xlsx contains the Smart Markers and formatting.
  • Output.xlsx is the resultant file generated through below code.

C#

Workbook data = new Workbook(myDir + “Data.xlsx”);
Cells cells = data.Worksheets[0].Cells;
//Export data to DataTable
DataTable table = data.Worksheets[0].Cells.ExportDataTable(0, 0, cells.MaxDataRow +1 , cells.MaxDataColumn +1, true);
//Rename the DataTable name to match the Smart Markers
table.TableName = “source”;

//Create WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();
//Open the template file (which contains Smart Markers)
designer.Workbook = new Workbook(myDir + “designer.xlsx”);

//Set the DataTable as the data source
designer.SetDataSource(table);

//Process the Smart Markers to fill the data into the worksheet
designer.Process(true);

//Calculate all formulas
designer.Workbook.CalculateFormula();

//Save the result
designer.Workbook.Save(myDir + “output.xlsx”, SaveFormat.Xlsx);

Please let us know if this fulfills your requirement.

An then i have one more issue i want to add values dynamically colum wise
eg: name age
jhon 20
joe 21
jack 22

this eg value will be in Datatable.

output:

name jhon joe jack
age 20 21 22
pink highlight color should be in one range (Result)

If i read Result Range i should get all value in that.

Ranges should be increase dynamically

we should transpose in specific range.

Hi Jaganlal,


I see you have removed your message regarding the MVC5 project. Could you please confirm if you are able to get results as shared in my post?

Regarding the recently shared scenario, I will perform some tests and will let you know here.

Yes i got answer for MVC5 Project too Format and range also got increased dynamically.
Thank you for answer.

Now i need answer for the scenario mentioned above.
Want to do transpose and dynamically increase the column

Hi Jaganlal,


Thank you for the confirmation.

We are currently working on your recently shared requirement of converting Columns to Rows, and we will shortly respond back with possible solution for this requirement.

Hi Jaganlal,


Thank you for your patience.

Please check the simplest code to perform transpose on a range of cells.

C#

//Get Cells of the worksheet on which transpose has to be applied
Cells cells = workbook.Worksheets[0].Cells;
//Create a range of source cells
Range range = cells.CreateRange(“A1:B2”);
//Create a range of destination cells
Range dRange = cells.CreateRange(“C5:D6”);
//Create an instance of PasteOptions
PasteOptions options = new PasteOptions();
//Set the Transpose property to true
options.Transpose = true;
//Copy the source cells to new range while applying transpose
dRange.Copy(range, options);

Unfortunately, the above code will not work in your particular scenario when you have 31592 rows and wish to convert them to columns, because according to MS Excel specifications none of the supported spreadsheet formats could have more than 16,384 columns.

Please let us know if we can be of further assistance to you.

Thank you for your reply.

I have seen this code before and i found another way to horizontal easily using Smart Markers
&=TableName.ColumnName(horizontal) -> it will add column horizontal
&=TableName.ColumnName(horizontal,copystyle) -> it will add column horizontal with first column style
&=TableName.ColumnName(horizontal,copystyle,noadd) -> it will add column horizontal with first column style and it will add new columns overwrite existing column.


Please verify an tell its correct

Hi Jaganlal,


Thank you for writing back.

Yes, you can use the horizontal repeat markers to fulfill your requirements but unfortunately you have to face the similar problem as discussed in my previous post, that is; you cannot create a spreadsheet having more than allowed number of columns for a specified file format.

Although if you have less number of columns to be filled, you have to modify the designer a little to get the columns repeated horizontally. Attached to this post is the modified designer, a spreadsheet containing a little data and the resultant output, whereas the code snippet is the same as shared here.