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,
- 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,
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,
Sorry. please check this an give accurate answer to fill data into this excel and download it
Hi Jaganlal,
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);
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,
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,
- 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.
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);
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,
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,
Hi Jaganlal,
//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);
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,