CODEWITHSUNDEEP
c#asp.net
JUAN
JUAN

Reputation: 523

how to create multiple excel sheets

well i have to creat just one excel file and 2 sheets both are fill using a 2 diferent DataTable, it gives the name the user only has to click save, the next code allows me to seend one datatable to one sheet (i am using C#, asp.net, and NOT using Visual Studio, i am writing in the Notepad my code):

string name2="Centroids";
        HttpContext context = HttpContext.Current;
        context.Response.Clear();
        foreach (System.Data.DataRow row in _myDataTable2.Rows)
        {   
            for (int i = 0; i < _myDataTable2.Columns.Count; i++)
                {
                context.Response.Write(row[i].ToString().Replace(",", string.Empty) + ",");
            }
        context.Response.Write(Environment.NewLine);
        }
    context.Response.ContentType = "text2/csv";
    context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name2 + ".csv");

but i have no idea how to creat the second sheet and use the second DataTable, any ideas of how to find a solution, this way the user has only to save and donwload only one document and not save as many DataTable are in the programa

Upvotes: 2

Views: 9098

Answers (3)

Antonio Rocabado
Antonio Rocabado

Reputation: 1

public bool LlenarExcelxlsx(List<DatosEntidad> listaOrigen)
{
    bool exito = false;
    string[] tipoLista = { "A", "B", "C" };
    string nombreArchivo = @"D:\prueba.xlsx";

    IWorkbook wb = new XSSFWorkbook();
    using (FileStream fileData = new FileStream(nombreArchivo, FileMode.Create, FileAccess.Write))
    {
        for (int k = 0; k < tipoLista.Length; k++)
        {
            List<DatosEntidad> listaDestino = listaOrigen
                .Where(c => c.tipo == tipoLista[k]).ToList();

            DataTable dt = ToDataTable(listaDestino);

            ISheet sheetx = wb.CreateSheet("Res_" + tipoLista[k] + k);
            ICreationHelper cH = wb.GetCreationHelper();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheetx.CreateRow(i);
                for (int j = 0; j < 13; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(cH.CreateRichTextString(dt.Rows[i].ItemArray[j].ToString()));
                }
            }
        }
        wb.Write(fileData);
        exito = true;
    }
    return exito;
}

Upvotes: 0

Antonio Bakula
Antonio Bakula

Reputation: 20693

You should use open source libraries for generating native excel files, there is no way you can create two sheets with csv.

Use NPOI (xls) or / and EPPlus (xlsx) and fully control your excel export, in this answer https://stackoverflow.com/a/9569827/351383 you can see example of creating excel file from DataTable with EPPlus. You can edit that method to accept DataTable list and create new sheets for each table, it's simple, just :

ExcelPackage pack = new ExcelPackage();
ExcelWorksheet ws = pack.Workbook.Worksheets.Add(sheetName);

Upvotes: 2

Firoz Ansari
Firoz Ansari

Reputation: 2515

You probably want to explore the possibility of using EPPlus. In my experience, using Response object has lot of constraints and take too much development effort to generate Excel file.

Url: http://epplus.codeplex.com/

Upvotes: 3

Related Questions