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

Free Support Forum - aspose.com

Aspose cell Formatting issue

Aspose.Cells Product Family
#1

I have written below code to export data into excel template
Workbook workbook = new Workbook(System.Web.Hosting.HostingEnvironment.MapPath("~/TemplateFile/Export.xlsx"));
Worksheet worksheet;
for (int i = 0; i < objLst[0].KPIData.Count; i++)
{
worksheet = workbook.Worksheets[i];
worksheet.Name = objLst[0].KPIData[i].Names;
var cells = worksheet.Cells;
var range = worksheet.Cells.MaxDisplayRange;
worksheet.Cells.ClearContents(range.FirstRow, range.FirstColumn, 10, range.ColumnCount);
ImportTableOptions obj1 = new ImportTableOptions();

                worksheet.Cells.ImportCustomObjects(objLst[0].KPIData[i].Data,0, 0, null);
                worksheet.AutoFitRows();
                worksheet.AutoFitColumns();
            }
            
            workbook.Save(System.Web.Hosting.HostingEnvironment.MapPath("~/ExcelExportedData/TestData.xlsx"), SaveFormat.Auto);

Issue : - above code is exporting data but taking the format of first row for all the cells where data is exported. I am not able to retain the format of the template file.

#2

@EVS,

Thanks for sharing sample code with details.

Could you change your code segment a bit as following, it should fix your issue:
e.g
Sample code:

Workbook workbook = new Workbook(System.Web.Hosting.HostingEnvironment.MapPath("~/TemplateFile/Export.xlsx"));
Worksheet worksheet;
for (int i = 0; i < objLst[0].KPIData.Count; i++)
{
worksheet = workbook.Worksheets[i];
worksheet.Name = objLst[0].KPIData[i].Names;
var cells = worksheet.Cells;
var range = worksheet.Cells.MaxDisplayRange;
worksheet.Cells.ClearContents(range.FirstRow, range.FirstColumn, 10, range.ColumnCount);
ImportTableOptions obj1 = new ImportTableOptions();
obj1.InsertRows = false;
                worksheet.Cells.ImportCustomObjects(objLst[0].KPIData[i].Data,0, 0, obj1);
                worksheet.AutoFitRows();
                worksheet.AutoFitColumns();
            }
            
            workbook.Save(System.Web.Hosting.HostingEnvironment.MapPath("~/ExcelExportedData/TestData.xlsx"),

Hope, this helps a bit.

#3

Thanks. Its working. I also looking for setting the caps for text columns. At present my text data is getting exported always in Caps even if I have data in small caps. how to do this setting.

#4

@EVS,

Good to know that your original issue is sorted out by the suggested code. Regarding your other issue (“setting the caps for text columns”), I am not entirely certain about it. Cells.ImportCustomObjects() method only imports data as it is there in the source. Do you think it is a bug in Aspose.Cells APIs which unnecessarily changes the data (small text/string to caps, etc.) when importing to the worksheet? Could you give us more details and your current output file by Aspose.Cells APIs. Also provide a sample file with your expected data (you may create your expected file in MS Excel manually), we will check it soon.

#5

image.png (3.8 KB)
I am attaching the print screen image for data whcih i am trying to export. In the exported file I always get the header names in CAPS. The code is below:

var jsonData = System.Web.Hosting.HostingEnvironment.MapPath("~/SampleData.json");
string json = string.Empty;
using (StreamReader r = new StreamReader(jsonData))
{
json = r.ReadToEnd();
List objLst = new List();
ExcelModel obj = JsonConvert.DeserializeObject(json);
objLst.Add(obj);
Workbook workbook = new Workbook(System.Web.Hosting.HostingEnvironment.MapPath("~/TemplateFile/Export.xlsx"));
Worksheet worksheet;
for (int i = 0; i < objLst[0].KPIData.Count; i++)
{
worksheet = workbook.Worksheets[i];
worksheet.Name = objLst[0].KPIData[i].Names;
var cells = worksheet.Cells;
var range = worksheet.Cells.MaxDisplayRange;
worksheet.Cells.ClearContents(range.FirstRow, range.FirstColumn, 10, range.ColumnCount);

                //Retain the template format
                ImportTableOptions obj1 = new ImportTableOptions();
                obj1.InsertRows = false;

                // Instantiate the error checking options
                ErrorCheckOptionCollection opts = worksheet.ErrorCheckOptions;
                int index = opts.Add();
                ErrorCheckOption opt = opts[index];
                // Disable the numbers stored as text option
                opt.SetErrorCheck(ErrorCheckType.TextNumber, false);
                // Set the range
                opt.AddRange(CellArea.CreateCellArea(0, 0, 1000, 50));
                
                worksheet.Cells.ImportCustomObjects(objLst[0].KPIData[i].Data,0, 0, obj1);
                worksheet.AutoFitRows();
                worksheet.AutoFitColumns();
                worksheet.Cells.Style.Font.CapsType = TextCapsType.All;
            }
            
            workbook.Save(System.Web.Hosting.HostingEnvironment.MapPath("~/ExcelExportedData/TestData.xlsx"), SaveFormat.Auto);
        }
#6

@EVS,

Thanks for the screenshot and sample code segment.

I still could not evaluate what you need to achieve and what issue you find with Aspose.Cells APIs when importing data into the spreadsheet. Aspose.Cells does not do anything with the headers or data, it should import the data from the source as it is. Kindly provide us your current output Excel file by Aspose.Cells APIs. Also provide a sample Excel file with your expected data (you may create your expected file in MS Excel manually), we will check it soon.