Introduction to the C WinForm export Excel method

  • 2020-05-26 09:56:38
  • OfStack

The preferred method for developers of NET is to use the COM component to invoke the Office software itself to create and read files, but it is extremely slow when the amount of data is large. The following code has been optimized to assign 1 array of 2-dimensional objects to 1 cell region (the following code can only be used to export data with no more than 26 columns) :

Office PIA


public static void ExportToExcel(DataSet dataSet, string outputPath)
{
    Excel.ApplicationClass excel = new Excel.ApplicationClass();
    Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);
    int sheetIndex = 0;
    foreach (System.Data.DataTable dt in dataSet.Tables)
    {
        object[,] data = new object[dt.Rows.Count + 1, dt.Columns.Count];
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            data[0, j] = dt.Columns[j].ColumnName;
        }
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                data[i + 1, j] = dt.Rows[i][j];
            }
        }
        string finalColLetter = string.Empty;
        string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        int colCharsetLen = colCharset.Length;
        if (dt.Columns.Count > colCharsetLen)
        {
            finalColLetter = colCharset.Substring(
                (dt.Columns.Count - 1) / colCharsetLen - 1, 1);
        }
        finalColLetter += colCharset.Substring(
                (dt.Columns.Count - 1) % colCharsetLen, 1);

        Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.Add(
            workbook.Sheets.get_Item(++sheetIndex),
            Type.Missing, 1, Excel.XlSheetType.xlWorksheet);
        sheet.Name = dt.TableName;
        string range = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
        sheet.get_Range(range, Type.Missing).Value2 = data;
        ((Excel.Range)sheet.Rows[1, Type.Missing]).Font.Bold = true;
    }
    workbook.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    workbook.Close(true, Type.Missing, Type.Missing);
    workbook = null;
    excel.Quit();
    KillSpecialExcel(excel);
    excel = null;
    GC.Collect();
    GC.WaitForPendingFinalizers();
}
[DllImport("user32.dll", SetLastError = true)]
static extern int GetWindowThreadProcessId(IntPtr hWnd, out int processId);
static void KillSpecialExcel(Excel.Application app)
{
    try
    {
        if (app != null)
        {
            int processId;
            GetWindowThreadProcessId(new IntPtr(app.Hwnd), out processId);
            System.Diagnostics.Process.GetProcessById(processId).Kill();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

File stream

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


public static void ExportToExcel(System.Data.DataSet ds, string path)
{
    StreamWriter sw = null;
    try
    {
        long totalCount = ds.Tables[0].Rows.Count;
        sw = new StreamWriter(path, false, Encoding.Unicode);
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
        {
            sb.Append(ds.Tables[0].Columns[i].ColumnName + "\t");
        }
        sb.Append(Environment.NewLine);
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                sb.Append(ds.Tables[0].Rows[i][j].ToString() + "\t");
            }
            sb.Append(Environment.NewLine);
        }
        sw.Write(sb.ToString());
        sw.Flush();
    }
    catch (IOException ioe)
    {
        throw ioe;
    }
    finally
    {
        if (sw != null)
        {
            sw.Close();
        }
    }
}


Related articles: