return File (memory stream) encoding

Question

Hi I have problem to return right encoded file.

finalString is here only for my control of encoding and show me right strings, but returned excel file dont contain right czech characters... (Č č š Š Ž etc...) Can someone help me?

 byte[] bytes = encode.GetBytes(str.ToString());
 string finalString = encode.GetString(bytes);

Here is complete class:

public async Task<IActionResult> ExportExcel()
    {
       // List<Material> obj = new List<Material>();
       // obj = await _context.Material.ToListAsync();

        StringBuilder str = new StringBuilder();
        str.Append("<table border=`" + "1px" + "`b>");
        str.Append("<tr>");
        str.Append("<td><b>Id</b></td>");
        str.Append("<td><b>Name</b></td>");
        str.Append("</tr>");

      //  foreach (Material val in obj)
       // {
            str.Append("<tr>");
            str.Append("<td>" + "0" + "</td>");
            str.Append("<td>" + Česnek + "</td>");
            str.Append("<td><font face=Arial Narrow size=" + "14px" + ">" + val.Name.ToString() + "</font></td>");
            str.Append("</tr>");
       // }

        str.Append("</table>");


        string uc = Configuration.UserCulture;
        int codepage =  1250; // _cultureDataProvider.GetAnsiCodePage(uc);
        Encoding encode = Encoding.GetEncoding(codepage);

        byte[] bytes = encode.GetBytes(str.ToString());
        string finalString = encode.GetString(bytes);
        MemoryStream stream = new MemoryStream(bytes);

        return File(stream, "application/vnd.ms-excel", "excel.xls");


        /*
        HttpContext.Response.Headers.Add("content-disposition", "attachment; filename=Information" + DateTime.Now.Year.ToString() + ".xls");
        this.Response.ContentType = "application/vnd.ms-excel";
        byte[] temp = System.Text.Encoding.ASCII.GetBytes(str.ToString());
        return File(temp, "application/vnd.ms-excel");
        */
    }

If you solved your own problem there is nothing wrong with posting your own answer to the question and marking it accepted. (Please mark it accepted once the 2 day waiting period has passed, if you do not your question will keep getting promoted to the front page as a question with no accepted answer) — Scott Chamberlain
– Scott Chamberlain, Commented Jan 18, 2016 at 20:23
There are some IDisposables in this code that will need disposing otherwise you may create a memory leak — alastairtree
– alastairtree, Commented Jul 29, 2020 at 8:57

NathanOliver · Accepted Answer · 2016-01-18 21:14:15Z

I was able to solve it after one day - csv and tab .txt file solution: (working under Core CLR)

    public async Task<IActionResult> Export(string type)
    {
        string _OuputType = type.ToLower().Trim();  
        string _delimiter = _OuputType.Contains("csv") ? ";" : String.Empty;
        _delimiter = _OuputType.Contains("txt") ? "" : _delimiter;             

        IEnumerable<Material> data = await _context.Material.Include(m => m.Unit).ToListAsync(); 
        StringBuilder str = new StringBuilder();

        //file name
        string fileName = "Material-" + DateTime.Now.ToString("dd_MM_yyyy-H_mm_ss");

        //head
        str.Append("Name" + _delimiter + "\t");
        str.Append("AutoPlan" + _delimiter + "\t");
        str.Append("QuantityMinStock" + _delimiter + "\t");
        str.Append("QuantityMaxStock" + _delimiter + "\t");
        str.Append("QuantityPurchase" + _delimiter + "\t");
        str.Append("Unit" + _delimiter + "\t");
        str.Append("\r\n");

        //data
        foreach (Material m in data)
        {
            str.Append(m.id + _delimiter + "\t");
            str.Append(m.Name + _delimiter + "\t");
            str.Append(m.AutoPlan + _delimiter + "\t");
            str.Append(m.QuantityMinStock + _delimiter + "\t");
            str.Append(m.QuantityMaxStock + _delimiter + "\t");
            str.Append(m.QuantityPurchase + _delimiter + "\t");
            str.Append(m.Unit.LocalizedCode + _delimiter + "\t");
            str.Append("\r\n");
        }    

        //   int codepage = _cultureDataProvider.GetAnsiCodePage(Configuration.UserCulture); == This is my service to get code page (example: 1250, or 1252 etc...)
        Encoding encode = Encoding.GetEncoding(_cultureDataProvider.GetAnsiCodePage(Configuration.UserCulture));
        byte[] bytes = encode.GetBytes(str.ToString());


        if (_OuputType == "txt"){return File(bytes, "text/plain", fileName + ".txt");}
        return File(bytes, "application/msexcel", fileName + ".csv");
   }

You should post your answer here, instead of changing you "question" and referring to it from here.
I went ahead and moved your answer into the answer. The question section is only for questions and the answer section is only for answers.

Josh Crozier · Accepted Answer · 2016-01-24 04:13:51Z

ANSI does not provide valid encoding for Č, č, š, Š, Ž you should look for more suitable codepages like UTF-8, UTF-16, UTF-32, etc....

byte[] bytes = Encoding.UTF8.GetBytes(str.ToString());
string finalString = Encoding.UTF8.GetString(bytes);

Did you try to work using Office Interop? It would be more ease and right way to work with Excel.

Here is simple worksheet manager

public class ExcelManager
{
    public ExcelManager()
    {
        ItsApplication = null;
        ItsWorkbook = null;
        ItsWorksheet = null;


        AppInitialization = false;
        WrbInitialization = false;
        WrsInitialization = false;
    }
    /*////////////////////////////////////////////////////*/
    public void Initialize(bool visible)
    {
        try
        {
            if (ItsApplication == null)
            {
                ItsApplication = new Microsoft.Office.Interop.Excel.Application();
                ItsApplication.SheetsInNewWorkbook = 1;
                ItsApplication.Visible = visible;
                AppInitialization = true;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error!!!", MessageBoxButtons.OK,
                MessageBoxIcon.Error);
        }
    }
    public void CreateWorkbook()
    {
        try
        {
            if (ApplicationInitialized)
            {
                ItsWorkbook = ItsApplication.Workbooks.Add(MissingValue);
                WrbInitialization = true;
            }
            else
                MessageBox.Show(
                    "Couldnot create workbook!\n" +
                    "App instance didnot initialized!",
                    "Error!!!", MessageBoxButtons.OK,
                    MessageBoxIcon.Error);

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error!!!", MessageBoxButtons.OK,
                MessageBoxIcon.Error);
        }
    }
    public void CreateWorksheet()
    {
        try
        {
            if (WorkbookInitialized)
            {
                ItsWorksheet = ItsWorkbook.Worksheets.Add(MissingValue,
                    MissingValue, MissingValue, MissingValue);

                WrsInitialization = true;
                CurrentRow = 1;
            }
            else
                MessageBox.Show(
                    "Couldnot create worksheet!\n" +
                    "Workbook didnot created!", 
                    "Error!!!", MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error!!!", MessageBoxButtons.OK,
                MessageBoxIcon.Error);
        }
    }
    public void SetText(string text,int bold,int italic,int cells_offset)
    {
        try
        {
            if (WorksheetInitialized)
            {
                ArrayList lines_list = new ArrayList();
                string curr_line = "";

                for (int i = 0; i < text.Length; i++)
                {
                    if (text[i] == '\n' || (i == text.Length - 1))
                    {
                        lines_list.Add(curr_line);
                        curr_line = "";
                    }
                    curr_line += text[i];
                }

                CurrentRow += cells_offset;

                foreach (object line in lines_list)
                {
                    Range text_range =
                        ItsWorksheet.get_Range("A" + CurrentRow, "A" + CurrentRow);

                    text_range.Font.Bold = bold;
                    text_range.Font.Italic = italic;
                    text_range.Value2 = line;
                    text_range.WrapText = false;

                    CurrentRow++;
                }
            }
            else
                MessageBox.Show(
                    MessageBox.Show(
                    "Cannot output any text!\n" +
                    "Worksheed didnot created!",
                    "Error!!!", MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error!!!", MessageBoxButtons.OK,
                MessageBoxIcon.Error);
        }
    }

    public void SetData(System.Data.DataTable table, int bold, int italic,int rows_offset)
    {
        try
        {
            if (WorksheetInitialized)
            {
                CurrentRow += rows_offset;

                int TableRows = table.Rows.Count;
                int TableCols = table.Columns.Count;

                char ACol = 'A';

                Range TableRange = ItsWorksheet.get_Range("A" + CurrentRow,
                    ((char)((int)ACol + TableCols - 1)).ToString() + (CurrentRow + TableRows).ToString());

                TableRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;


                for (int i = 0; i < TableCols; i++)
                {
                    Range names_range = ItsWorksheet.get_Range(((char)((int)ACol + i)).ToString() + CurrentRow,
                        ((char)((int)ACol + i)).ToString() + CurrentRow);
                    names_range.Value2 = table.Columns[i].ColumnName;
                }

                for (int i = 1; i < TableRows + 1; i++)
                {
                    for (int j = 0; j < TableCols; j++)
                    {
                        Range cell_range = ItsWorksheet.get_Range(((char)((int)ACol + j)).ToString() + (CurrentRow + i),
                        ((char)((int)ACol + j)).ToString() + (CurrentRow + i));


                        cell_range.Value2 = table.Rows[i - 1].ItemArray[j];
                        cell_range.Font.Bold = bold;
                        cell_range.Font.Italic = italic;
                    }
                }

                CurrentRow += TableRows;
            }
            else
                MessageBox.Show(
                    "Cannot output any text!\n" +
                    "Worksheed didnot created!",
                    "Error!!!", MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error!!!", MessageBoxButtons.OK,
                MessageBoxIcon.Error);
        }
    }
    public void SaveCurrentWorksheet(string filename)
    {
        try
        {
            ItsWorksheet.SaveAs(filename,
                MissingValue,
                MissingValue,
                MissingValue,
                MissingValue,
                MissingValue,
                MissingValue,
                MissingValue,
                MissingValue,
                MissingValue);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error!!!", MessageBoxButtons.OK,
                MessageBoxIcon.Error);
        }

    }
    public void CloseCurrentWorksheet()
    {
        if (WorkbookInitialized)
        {
            ItsWorkbook.Close(MissingValue,
                MissingValue,
                MissingValue);

            ItsApplication.Quit();

            WrbInitialization = false;
            WrsInitialization = false;
            AppInitialization = false;
        }
    }
    public bool ApplicationInitialized
    {
        get
        {
            return AppInitialization;
        }
    }
    public bool WorkbookInitialized
    {
        get
        {
            return WrbInitialization;
        }
    }
    public bool WorksheetInitialized
    {
        get
        {
            return WrsInitialization;
        }
    }
    ////////////////////////////////////////////////////////
    private _Application ItsApplication;
    private _Worksheet ItsWorksheet;
    private _Workbook ItsWorkbook;
    // Initialization flags
    private bool AppInitialization;
    private bool WrbInitialization;
    private bool WrsInitialization;
    // Positioning text in rows
    private int CurrentRow;
    ////////////////////////////////////////////////////////
    // Define static members of class
    private static object MissingValue = System.Reflection.Missing.Value;
    private static object EndOfDocument = "\\endofdoc";
}

You can use SetData method to print tables to excel files.

Simple instruction:

Create instance of class;
Initialize world application by calling public void Initialize(bool visible) visible controll the wisibility of Excell window.
Create new workbook by calling CreateWorkbok;
And worksheet by CreateWorksheet;
Work with worksheet SetData - prints table DataTable;
Work with text SetText.

To save and close use SaveCurrentWorksheet and CloseCurrentWorksheet.

ExcelManager excel_manager = new ExcelManager()
excel_manager.Initialize(true);
excel_manager.CreateWorkbook();
excel_manager.CreateWorksheet();

excel_manager.SetText("Česnek!", 1, 0, 0);
excel_manager.SetData(systemTable, 0, 0, 2);
excel_manager.SetText("Лог консолі!", 1, 0, 3);

The result of calls:

Or you can use Open XML SDK for Office, to support multitask in your web application.

UTF8Encoding encode = new UTF8Encoding(); - this do not working too, any next idea please?
Office interop is not supported for use inside ASP.NET. To @MiroslavSiska, If you can switch to the newer xlsx standard that was released with you can use the Open XML SDK for Office which is the thing microsoft released to replace the interop to manipulate the Office 2007 and newer files.
See UPDATE-1 - this working under CORE CLR. Its fast solution for now (pass to Export(string Type) parameter: txt or csv and action will return .csv or .txt file)...

Collectives™ on Stack Overflow

return File (memory stream) encoding

2 Answers 2

2 Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Related