How do I save DataGrid to XLS with c#?

Question

I want to add to my application an ability to save data to XLS (old excel files).

I've seen an example on how to do this in ASP.NET here, but I don't know how this translates into c# code for desktop applications.

I also had a look at excel automation, which looks like this:

private void button1_Click(object sender, EventArgs e)
{
    Excel.Application xlApp ;
    Excel.Workbook xlWorkBook ;
    Excel.Worksheet xlWorkSheet ;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Excel.ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Add(misValue);

    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    xlWorkSheet.Cells[1, 1] = "http://csharp.net-informations.com";

    xlWorkBook.SaveAs("csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);

    MessageBox.Show("Excel file created , you can find the file c:\\csharp-Excel.xls");
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

And I don't want to use it because:

1. It will be ueberslow if I export large amounts of data

2. I want this to work on PCs without Microsoft Excel installed if possible

I've also tried CarlosAg.Excel.Xml, and it seems to work, except that when I'm opening the file, I'm getting warning from excel, that the file is not in XLS format, but some other one.

Can anyone recommend to me a free c# library which would do this, or show me how to use conventional .Net libraries to save the data to XLS?

Answer 1

Use NOPI, which available on codeplex.com

This project is the .NET version of POI Java project at http://poi.apache.org/. POI is an open source project which can help you read/write xls, doc, ppt files. It has a wide application.

Here's an example on how to use NOPI:

        using (FileStream fileOut = new FileStream("poi-test.xls", FileMode.OpenOrCreate))
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            var worksheet = workbook.CreateSheet("POI Worksheet");

            // index from 0,0... cell A1 is cell(0,0)
            var row1 = worksheet.CreateRow((short)0);

            var cellA1 = row1.CreateCell((short)0);
            cellA1.SetCellValue("Hello");
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.FillForegroundColor = HSSFColor.GOLD.index;
            cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;//.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellA1.CellStyle = cellStyle;

            ICell cellB1 = row1.CreateCell((short)1);
            cellB1.SetCellValue("Goodbye");
            cellStyle = workbook.CreateCellStyle();
            cellStyle.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
            cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            cellB1.CellStyle = cellStyle;

            var cellC1 = row1.CreateCell((short)2);
            cellC1.SetCellValue(true);

            var cellD1 = row1.CreateCell((short)3);
            cellD1.SetCellValue(new DateTime());
            cellStyle = workbook.CreateCellStyle();
            cellStyle.DataFormat = HSSFDataFormat
                    .GetBuiltinFormat("m/d/yy h:mm");
            cellD1.CellStyle = cellStyle;

            workbook.Write(fileOut);
        }

Ref: Creating Excel spreadsheets .XLS and .XLSX in C# by Leniel Macaferi .