How to export excel file in c# in multiple sheets

Question

I have this code that export an excel file using c# from 1 listview. My problem now is if I have 2 listviews that will be generated in 1 excel file, my plan is listview1 is for sheet1 and listview2 is for sheet2.

this is my code generates listview1 to excel into sheet1:

string[] st = new string[listView1.Columns.Count];
DirectoryInfo di = new DirectoryInfo(Environment.ExpandEnvironmentVariables("%USERPROFILE%") + @"\Desktop\");
if (di.Exists == false)
    di.Create();
fileName f = new fileName();
if (f.ShowDialog() == DialogResult.OK)
{
    StreamWriter sw = new StreamWriter(Environment.ExpandEnvironmentVariables("%USERPROFILE%") + @"\Desktop\" + f.Filenam + ".xls", false);
    sw.AutoFlush = true;
    string header = "";
    for (int col = 0; col < listView1.Columns.Count; col++)
    {
        header += listView1.Columns[col].Text.ToString() + "\t";
    }
    sw.Write(header);
    int rowIndex = 1;
    int row = 0;
    string st1 = "";
    for (row = 0; row < listView1.Items.Count; row++)
    {
        if (rowIndex <= listView1.Items.Count)
            rowIndex++;

        st1 = "\n";

        for (int col = 0; col < listView1.Columns.Count; col++)
        {
            st1 = st1 + listView1.Items[row].SubItems[col].Text.ToString() + "\t";
        }
        sw.Write(st1);
    }
    sw.Close();
    FileInfo fil = new FileInfo(Environment.ExpandEnvironmentVariables("%USERPROFILE%") + @"\Desktop\" + f.Filenam + ".xls");
    if (fil.Exists == true)
        MessageBox.Show("Process Completed", "Export to Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
}

score 3 · Answer 1 · edited Jan 01 '14 at 16:35

3

I have used EPPlus, its free, give performance benefits, and provides many Excel functions. Other option you can look for is Microsoft InterOp for Excel export, but it has performance problems.

edited Jan 01 '14 at 16:35

BenMorel

34,448
50
182
322

answered Jul 26 '13 at 14:18

Irfan

2,713
3
29
43

1

+1 on EPPlus - only downside is that it creates xlsx files so if you need xls it may not be for you. – andrew Jul 26 '13 at 15:03

score 1 · Answer 2 · edited May 23 '17 at 11:49

As Irfan already mentioned, you could do that with Microsoft.Office.Interop

Excel.Application xlsApp = new Excel.Application();
Excel.Workbook workbook;
workbook = xlsApp.Workbooks.Open(configuration.XLSExportedFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
foreach (Excel.Worksheet sheet in workbook.Sheets)
{
      workbook.Activate();
      sheet.Activate();
}

That would help you to step through all your sheets in your Excel file. Now in case you want to expand it with multiple sheets, you can read C# how to add Excel Worksheet programmatically Office XP / 2003 in order to find how to add additional sheets.

What you could do now, is put your text into a variable for example HTML or any kind of information from your listbox.

string html = Clipboard.GetText(TextDataFormat.Html);

Now you can play around with the Clipboard(alternative solution, but to avoid).

Clipboard.SetText(html);

It actually enters information into your Clipboard, what you could do is pass all your listbox values into the Clipboard.

And now the final step would be to paste it at the position that you want.

sheet.Range(cellmapp).PasteSpecial();

Where cellmap is supposed to be for example A1. In case your text that you have in your listbox is too long you can adapt your rows/columns.

//Auto fits all columns and rows
//https://stackoverflow.com/questions/14748322/c-sharp-autofit-method-for-excel-sheet
sheet.Columns.AutoFit();
sheet.Rows.AutoFit();

Don't forget, if you work with COM processes, close every object and file.

workbook.SaveAs(configuration.XLSExportedFile);
workbook.Close();
xlsApp.Quit();
xlsApp.Dispose();

I don't say that would be the best solution to do, but you could look up for OpenXML also.

score 0 · Answer 3 · answered Jul 26 '13 at 14:51

I've used ClosedXML before, the syntax is really clean and simple and you can get this sort of task done in just a few lines

This block shows how to add a list to a new sheet and save it

List<string> list = new List<string>();
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sample Sheet");
worksheet.Cell(1, 1).Value = list;
workbook.SaveAs("HelloWorld.xlsx");

How to export excel file in c# in multiple sheets

3 Answers3