Introduction to C# WinForm Export Excel Method

The preferred method of .NET developers is to call the Office software itself through the COM component to realize the creation and reading and writing of files, but the amount of data is extremely slow when the amount of data is large; the following code has been optimized to assign a two-dimensional object array to a cell area (the following code can only be used to export data with no more than 26 columns):

Office PIA

Copy the codeThe code is as follows:

public static void ExportToExcel(DataSet dataSet, string outputPath)
{
    excel = new ();
    workbook = ();
    int sheetIndex = 0;
    foreach ( dt in )
    {
        object[,] data = new object[ + 1, ];
        for (int j = 0; j < ; j++)
        {
            data[0, j] = [j].ColumnName;
        }
        for (int j = 0; j < ; j++)
        {
            for (int i = 0; i < ; i++)
            {
                data[i + 1, j] = [i][j];
            }
        }
        string finalColLetter = ;

        string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        int colCharsetLen = ;
        if ( > colCharsetLen)
        {
            finalColLetter = (
                ( - 1) / colCharsetLen - 1, 1);
        }
        finalColLetter += (
                ( - 1) % colCharsetLen, 1);

        sheet = ()(
            .get_Item(++sheetIndex),
            , 1, );
        = ;
        string range = ("A1:{0}{1}", finalColLetter, + 1);
        sheet.get_Range(range, ).Value2 = data;
        (()[1, ]). = true;
    }
    (outputPath, , ,
        , , , ,
        , , , , );
    (true, , );
    workbook = null;
    ();
    KillSpecialExcel(excel);
    excel = null;
    ();
    ();
}

[DllImport("", SetLastError = true)]
static extern int GetWindowThreadProcessId(IntPtr hWnd, out int processId);

static void KillSpecialExcel( app)
{
    try
    {
        if (app != null)
        {
            int processId;
            GetWindowThreadProcessId(new IntPtr(), out processId);
            (processId).Kill();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

File Stream

This method is significantly more efficient than the first one, and it does not require Office installation, but the exported xls file does not comply with Excel's format standards. When opening the generated xls file, it will be prompted: The file you are trying to open is in a different format that specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file.

Copy the codeThe code is as follows:

public static void ExportToExcel( ds, string path)
{
    StreamWriter sw = null;
    try
    {
        long totalCount = [0].;
        sw = new StreamWriter(path, false, );
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < [0].; i++)
        {
            ([0].Columns[i].ColumnName + "\t");
        }
        ();
        for (int i = 0; i < [0].; i++)
        {
            for (int j = 0; j < [0].; j++)
            {
                ([0].Rows[i][j].ToString() + "\t");
            }
            ();
        }
        (());
        ();
    }
    catch (IOException ioe)
    {
        throw ioe;
    }
    finally
    {
        if (sw != null)
        {
            ();
        }
    }
}