Save XLSX file to Stream and apply formatting to row in .NET

Aspose.Cells Product Family
1

I am trying to save the workbook as a stream and download it. The downloaded file gives a message that it is corrupted.

Here is my code:

License license = new License();
license.SetLicense(“Aspose.Total.lic”);

        Aspose.Cells.Workbook xlWorkBook = new Workbook();

        Worksheet worksheet = xlWorkBook.Worksheets[0];

        Cell cell = worksheet.Cells["A1"];
        cell.PutValue("ID");

        Cell cell1 = worksheet.Cells["A2"];
        cell1.PutValue("NewName");

        xlWorkBook.FileFormat = FileFormatType.Excel97To2003;

        MemoryStream ms = new MemoryStream();
        ms = xlWorkBook.SaveToStream();

        byte[] buffer = null;
        buffer = ms.ToArray();

        return buffer;
2

@francis.dev10,

Thanks for the sample code and details.

Well, if you are saving to XLSX file format, do no use SaveToStream method as it is an older method used to save to XLS file format. I think you may change your code like following:
e.g
Sample code:

.......
Aspose.Cells.Workbook xlWorkBook = new Workbook();

        Worksheet worksheet = xlWorkBook.Worksheets[0];

        Cell cell = worksheet.Cells["A1"];
        cell.PutValue("ID");

        Cell cell1 = worksheet.Cells["A2"];
        cell1.PutValue("NewName");

        //xlWorkBook.FileFormat = FileFormatType.Excel97To2003;

        MemoryStream ms = new MemoryStream();
            xlWorkBook.Save(ms, SaveFormat.Xlsx);
            ms.Seek(0, SeekOrigin.Begin)
     
        byte[] buffer = new byte[ms.Length];
        buffer = ms.ToArray();
        

return buffer;

Let us know if you still have any issue.

3

Thanks for the response.

I made these changes and now the error below is thrown. When I click yes It says the file is corrupted.

image.png (4.4 KB)

image.png (2.0 KB)

4

@francis.dev10

Thanks for considering Aspose APIs.

We tested this issue with the following sample code and it generated the output Excel file fine. Please see the screenshot below.

Download Link:
Output Excel File.zip (5.6 KB)

C#

Aspose.Cells.Workbook xlWorkBook = new Workbook();

Worksheet worksheet = xlWorkBook.Worksheets[0];

Cell cell = worksheet.Cells["A1"];
cell.PutValue("ID");

Cell cell1 = worksheet.Cells["A2"];
cell1.PutValue("NewName");

MemoryStream ms = new MemoryStream();
xlWorkBook.Save(ms, SaveFormat.Xlsx);
ms.Seek(0, SeekOrigin.Begin);

byte[] buffer = new byte[ms.Length];
buffer = ms.ToArray();

File.WriteAllBytes("outputExcelFile.xlsx", buffer);

Screenshot:

5

The issue was on our end while downloading the file. Thanks for the help.

6

@francis.dev10,

Good to know that you have figured out your issue now. Feel free to write us back if you have further comments or query, we will be happy to assist you soon.

7

Hi, Do you have a sample to set the style on a row in ASPOSE.CELLS 7.4.
I can’t seem to find a proper document for this.

8

@francis.dev10

Please use Row.AppyStyle() or Range.ApplyStyle() method for your needs. Please see the following sample code, its output Excel file and screenshot for a reference.

Output Excel File.zip (8.7 KB)

C#

//Create empty workbook
Workbook wb = new Workbook();

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//This is the 5th row via Range object
Range r = ws.Cells.CreateRange(4, 1, false);

//Similarly, this is the 8th row - here 7 means 8th
r = ws.Cells.CreateRange(7, 1, false);

//Set style on 8th row
Style st = wb.CreateStyle();
st.Font.IsBold = true;
st.Font.Color = Color.Red;
st.Pattern = BackgroundType.Solid;
st.ForegroundColor = Color.Yellow;

StyleFlag flag = new StyleFlag();
flag.All = true;

//Applying style created above
r.ApplyStyle(st, flag);

//Add some data in 8th row
Cell cell = ws.Cells[7, 3];
cell.PutValue("This is bold text in red color with yellow fill color.");

//Save the workbook
wb.Save("output.xlsx");

Screenshot