Lose style when export to excel or cvs

Hi ,


When I workbook.Save(report, SaveFormat.Excel97To2003) or SaveFormat.CSV save format other than pdf. I lose all the header, footer, page, time and all the style thing. How to fix that. thakns,

Tony

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and use the latest version:
Aspose.Cells
for .NET v7.1.2.7
and let us know your feedback

If you still face the issue, then please provide us your sample source files and code to replicate this issue.

Hi,


I have tried the new dll which you provide. The style still is not showing in my report. Only the pdf type works. The file has the style. All other saveformat only showing the data. I have attached the code here. Thanks,


Workbook workbook = new Workbook();
workbook.FileName = reportName;

// Import data from the DataTable and create a stacked area chart from it.
workbook.Worksheets[0].Cells.ImportDataTable(dataTable, true, “A1”);
workbook.Worksheets[0].AutoFitColumns();

Cells cells = workbook.Worksheets[0].Cells;
//Copy the first column to the third column

cells.InsertColumn(0);
cells.CopyColumn(cells, 5, 0);
cells.HideColumn(5);

PageSetup pageSetup = workbook.Worksheets[0].PageSetup;
pageSetup.CenterHorizontally = true;
pageSetup.SetHeader(1, “&“Times New Roman,Bold”&16” + reportName);
pageSetup.SetHeader(2, “&“Times New Roman,Bold”&D-&T”);
pageSetup.SetFooter(1, “&P”);

            <span style="color:blue;">int</span> i = workbook.Styles.Add();

            <span style="color:green;">//Accessing the newly added Style to the Excel object</span>
            <span style="color:#2b91af;">Style</span> style = workbook.Styles[i];

            <span style="color:green;">//Setting the vertical alignment of the text in the "A1" cell</span>
            style.VerticalAlignment = <span style="color:#2b91af;">TextAlignmentType</span>.Center;

            <span style="color:green;">//Setting the horizontal alignment of the text in the "A1" cell</span>
            style.HorizontalAlignment = <span style="color:#2b91af;">TextAlignmentType</span>.Center;

            <span style="color:green;">//Setting the font color of the text in the cell</span>
            style.Font.Color = <span style="color:#2b91af;">Color</span>.Black;
            
            <span style="color:green;">//Shrinking the text to fit in the cell</span>
            style.ShrinkToFit = <span style="color:blue;">true</span>;

            <span style="color:green;">//Setting the bottom border color of the cell to Gray</span>
            style.Borders[<span style="color:#2b91af;">BorderType</span>.BottomBorder].Color = <span style="color:#2b91af;">Color</span>.Gray;
            style.Borders[<span style="color:#2b91af;">BorderType</span>.TopBorder].Color = <span style="color:#2b91af;">Color</span>.Gray;
           

            <span style="color:green;">//Setting the bottom border type of the cell to medium</span>
            style.Borders[<span style="color:#2b91af;">BorderType</span>.BottomBorder].LineStyle = <span style="color:#2b91af;">CellBorderType</span>.Thin;
            style.Borders[<span style="color:#2b91af;">BorderType</span>.TopBorder].LineStyle = <span style="color:#2b91af;">CellBorderType</span>.Thin;
           

            <span style="color:green;">//Creating StyleFlag</span>
            <span style="color:#2b91af;">StyleFlag</span> styleFlag = <span style="color:blue;">new</span> <span style="color:#2b91af;">StyleFlag</span>();
            styleFlag.HorizontalAlignment = <span style="color:blue;">true</span>;
            styleFlag.VerticalAlignment = <span style="color:blue;">true</span>;
            styleFlag.ShrinkToFit = <span style="color:blue;">true</span>;
            styleFlag.Borders = <span style="color:blue;">true</span>;
            styleFlag.FontColor = <span style="color:blue;">true</span>;


            <span style="color:#2b91af;">Row</span> row = workbook.Worksheets[0].Cells.Rows[0];

            <span style="color:green;">//Accessing a row from the Rows collection</span>
            <span style="color:green;">//foreach (Row row in workbook.Worksheets[0].Cells.Rows)</span>
            <span style="color:green;">//{</span>
            <span style="color:green;">//    Row rows = row;</span>

                <span style="color:green;">//Assigning the Style object to the Style property of the row</span>
            row.ApplyStyle(style, styleFlag);
            <span style="color:green;">//}</span>

            workbook.Worksheets[0].Name = reportName;

           
            <span style="color:green;">//workbook.Worksheets[0].HPageBreaks.Add(1);</span>
            
            <span style="color:#2b91af;">MemoryStream</span> report = <span style="color:blue;">new</span> <span style="color:#2b91af;">MemoryStream</span>();

            
            <span style="color:blue;">if</span> (format.ToString() == <span style="color:#a31515;">"htm"</span>)
            {
                workbook.Save(report, <span style="color:#2b91af;">SaveFormat</span>.Html);
            }

            <span style="color:blue;">if</span> (format.ToString() == <span style="color:#a31515;">"pdf"</span>)
            {
                workbook.Save(report, <span style="color:#2b91af;">SaveFormat</span>.Pdf);
            }

            <span style="color:blue;">if</span> (format.ToString() == <span style="color:#a31515;">"tiff"</span>)

            {
                workbook.Save(report, <span style="color:#2b91af;">SaveFormat</span>.TIFF);
            }

            <span style="color:blue;">if</span> (format.ToString() == <span style="color:#a31515;">"xls"</span>)

            {
                workbook.Save(report, <span style="color:#2b91af;">SaveFormat</span>.Excel97To2003);
            }

            <span style="color:blue;">if</span> (format.ToString() == <span style="color:#a31515;">"csv"</span>)
            {
                workbook.Save(report, <span style="color:#2b91af;">SaveFormat</span>.CSV);
                
            }

            <span style="color:green;">//workbook.Save(report, opt.SaveFormat);</span>

report.Seek(0,SeekOrigin.Begin);

var results = new FileStreamResult(report,  "text/html"); 
                        
results.FileDownloadName = "Report.htm"; // TODO: Get the view name and use in file name

return results;

Hi,

Thanks for your code example.

It appears to be fine. Please see csv format is a text based format, it does not support header & footers and style stuff.

You can check it by creating a stylish workbook in Ms-Excel and then saving it in csv format and then opening it back again, formatting will be lost.

For your problems in other formats, please provide me a complete runnable code/project to test this issue. Please remove any database dependencies, use hard coded sample test values.

It will be helpful if you could provide us your actual and expected output files to highlight the differences.

How about for html format. The style will be lose in html as well? The pdf one has the style. the .rar one is html foramt. Thanks,

Hi,

Please provide me a sample project that I could run and test your issue.

I will also need your actual and expected output files to look into this issue.

Is this behavior changed with the new releases?

Because I loose the style when saving the format other than pdf, xls, html.

Is the style loose on csv, xlsb?

MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(sb.ToString()));
LoadOptions loadOptions = new LoadOptions(LoadFormat.Html);

Workbook wb = new Workbook(ms, loadOptions);
Worksheet ws = wb.Worksheets[0];

Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];

Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag();
flag.Borders = true;
flag.Font = true;
ws.Cells.ApplyStyle(style, flag);
wb.Save(outputFile, SaveFormat.CSV);

Hi,

Thanks for your posting and considering Aspose.Cells.

CSV is a text based format and does not support styles. For example, if some cell A1 has a value “some text” and is bold, then when you convert to CSV, it will have a value “some text” but it will not be bold.

You can try converting your Excel file into CSV format with MS-Excel and see this result.

However, XLSB format supports styles. So if you are having problem with Styles while saving to XLSB format, then it might a be bug of Aspose.Cells. Please download and try the latest version: Aspose.Cells
for .NET v8.1.1.1
and see if it resolves your issues with XLSB format.

If your issue still persists, then please provide us your source file which you are converting to XLSB format and it is losing styles. We will look into it and update you asap.

Similarly, Styles information should be saved in XLS, HTML and PDF format.