How to input data in excel sheets?

Question

I'm trying to do something like this:

I'm trying to add data in excel sheet. This is done correctly only if I add data to cells in first column. After that file name appears with the cells data. For example if the cell is C5, it'll append 4 file names to every input and then the data. Can someone help me with what I'm doing wrong?

public void Create()
    {
        string filepath = string.Empty;
        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2016;
            IWorkbook workbook = application.Workbooks.Create(1);          
            IWorksheet namedSheet = workbook.Worksheets[0];

            namedSheet.Range["A1"].Text = "Hey there";
            namedSheet.Range["A2"].Text = "Good Morning";
            namedSheet.Range["A6"].Text = "Here I am!";
            namedSheet.Range["B5"].Text = "1";

            MemoryStream ms = new MemoryStream();
            workbook.SaveAs(ms, "0.xlsx");
            byte[] bytes = ms.ToArray();
            ms.Flush();
            ms.Close();
            Response.Clear();
            Response.AddHeader("Content-Disposition", "attachment; filename=foo.xls");
            Response.AddHeader("Content-Length", bytes.Length.ToString());
            Response.ContentType = "application/octet-stream";
            Response.BinaryWrite(bytes);
    }
}

Hey there0.xlsx
Good Morning0.xlsx
0.xlsx1
Here I am!0.xlsx
Created with a trial version of Syncfusion Essential XlsIO

Why are you serving this file to the end user with a .xls extension, yet saving it on disk with a .xlsx extension? I would assume .xlsx is correct since you specified Excel 2016. — mason, Jul 24 '19 at 17:44
@mason It's deceptive. That overload of `SaveAs` doesn't use the second parameter as a filename. It's not actually saving to disk. — , Jul 24 '19 at 17:47
You should be flushing the array *before* getting its array buffer. — , Jul 24 '19 at 17:53
@Amy Interesting. Never worked with Syncfusion. renu - my advice still stands on making sure you're serving the file to the user with the correct extension and [MIME Type](https://stackoverflow.com/questions/4212861/what-is-a-correct-mime-type-for-docx-pptx-etc). Or if you're doing a CSV, then use .csv and `text/csv` — mason, Jul 24 '19 at 18:26

score 0 · Answer 1 · answered Jul 24 '19 at 17:46

0

According to the SyncFusion documentation, that SaveAs method doesn't do what you seem to think it does. The second parameter is not a filename, it's a delimeter. It saves a CSV with the second parameter used as a delimiter.

Signature:
void SaveAs( Stream stream, string separator )

Parameters
stream - Stream to save.
separator - Denotes separator for the CSV file types.

You should just use SaveAs(ms).

answered Jul 24 '19 at 17:46

Ah ha, that works too. It'll generate a CSV file with a comma as a delimiter. – Jul 24 '19 at 18:17
@renu If my answer was helpful, please consider accepting it and/or voting. See https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work for more information. If my answer wasn't sufficient, please let me know how it needs to be amended, thanks. – Jul 24 '19 at 18:35

score 0 · Answer 2 · answered Jul 25 '19 at 13:21

You are saving the stream with separator “0.xlsx” hence the added text is appended with “0.xlsx”. If you wish to save the file as csv, provide the separator parameter as ‘,’ also file extension as csv. Please find the updated code from your snippet.

using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2016;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet namedSheet = workbook.Worksheets[0];

                namedSheet.Range["A1"].Text = "Hey there";
                namedSheet.Range["A2"].Text = "Good Morning";
                namedSheet.Range["A6"].Text = "Here I am!";
                namedSheet.Range["B5"].Text = "1";

                MemoryStream ms = new MemoryStream();
                workbook.SaveAs(ms, ",");
                byte[] bytes = ms.ToArray();
                ms.Flush();
                ms.Close();
                Response.Clear();
                Response.AddHeader("Content-Disposition", "attachment; filename=foo.csv");
                Response.AddHeader("Content-Length", bytes.Length.ToString());
                Response.ContentType = "application/octet-stream";
                Response.BinaryWrite(bytes);
            }

We have also prepared sample for the same and sharing for your reference. The sample can be downloaded from following link.

Sample Link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Sample1353252283.zip

You can also save the file as xlsx without providing the separator parameter.

Please refer to below API reference to know more about different overloads for SaveAs

https://help.syncfusion.com/cr/file-formats/Syncfusion.XlsIO.Base~Syncfusion.XlsIO.IWorkbook~SaveAs.html

Please refer to below documentation to know more on working with XlsIO

https://help.syncfusion.com/file-formats/xlsio/overview

Regards,

Abirami

How to input data in excel sheets?

2 Answers2