C # datatabel export excel (three methods)

 

Method 1: copy can be used directly, suitable for large volumes of data, tens of thousands of copies)

Microsoft. Office. Interop. Excel. Application appexcel = new Microsoft. Office. Interop. Excel. Application ();

SaveFileDialog savefiledialog = new SaveFileDialog ();

System. Reflection. Missing miss = System. Reflection. Missing. Value;

Appexcel = new Microsoft. Office. Interop. Excel. Application ();

Microsoft. Office. Interop. Excel. Workbook workbookdata;

Microsoft. Office. Interop. Excel. Worksheet worksheetdata;

Microsoft. Office. Interop. Excel. Range rangedata;

// Sets the object to be invisible.

Appexcel. Visible = false;

System. Globalization. CultureInfo currentci = System. Threading. Thread. CurrentThread. CurrentCulture;

System. Threading. Thread. CurrentThread. CurrentCulture = new System. Globalization. CultureInfo ("en-us ");

Workbookdata = appexcel. Workbooks. Add (miss );

Worksheetdata = (Microsoft. Office. Interop. Excel. Worksheet) workbookdata. Worksheets. Add (miss, miss );

// Assign a worksheet name

Worksheetdata. Name = "saved ";

For (int I = 0; I <dt. Columns. Count; I ++)

{

Worksheetdata. Cells [1, I + 1] = dt. Columns [I]. ColumnName. ToString ();

}

// Because the header has been written in the first row, all data should start from a2

Rangedata = worksheetdata. get_Range ("a2", miss );

Microsoft. Office. Interop. Excel. Range xlrang = null;

// Irowcount indicates the actual number of rows and the largest row

Int irowcount = dt. Rows. Count;

Int iparstedrow = 0, icurrsize = 0;

// Ieachsize is the value of each write row, which can be set by yourself

Int ieachsize = 1000;

// Icolumnaccount indicates the actual number of columns and the maximum number of Columns

Int icolumnaccount = dt. Columns. Count;

// Declare an array of ieachsize × icolumnaccount in the memory. ieachsize indicates the maximum number of rows stored each time, and icolumnaccount indicates the actual number of columns stored.

Object [,] objval = new object [ieachsize, icolumnaccount];

Icurrsize = ieachsize;

 

 

While (iparstedrow <irowcount)

{

If (irowcount-iparstedrow) <ieachsize)

Icurrsize = irowcount-iparstedrow;

// Use the for loop to assign values to the array

For (int I = 0; I <icurrsize; I ++)

{

For (int j = 0; j <icolumnaccount; j ++)

Objval [I, j] = dt. Rows [I + iparstedrow] [j]. ToString ();

System. Windows. Forms. Application. DoEvents ();

}

String X = "A" + (int) (iparstedrow + 2). ToString ();

String col = "";

If (icolumnaccount <= 26)

{

Col = (char) ('A' + icolumnaccount-1). ToString () + (int) (iparstedrow + icurrsize + 1). ToString ();

}

Else

{

Col = (char) ('A' + (icolumnaccount/26-1 ))). toString () + (char) ('A' + (icolumnaccount % 26-1 ))). toString () + (int) (iparstedrow + icurrsize + 1 )). toString ();

}

Xlrang = worksheetdata. get_Range (X, col );

// Call the value2 attribute of range to assign the value in memory to excel

Xlrang. Value2 = objval;

Iparstedrow = iparstedrow + icurrsize;

}

// Save the worksheet

System. Runtime. InteropServices. Marshal. ReleaseComObject (xlrang );

Xlrang = null;

// Call the method to close the excel Process

Appexcel. Visible = true;

 

 

Method 2: (self-built functions are suitable for large volumes of data and tens of thousands of data records)

Using System. IO;

Private void dataTableToCsv (DataTable table, string file)

{

String title = "";

FileStream fs = new FileStream (file, FileMode. OpenOrCreate );

// FileStream fs1 = File. Open (file, FileMode. Open, FileAccess. Read );

StreamWriter sw = new StreamWriter (new BufferedStream (fs), System. Text. Encoding. Default );

For (int I = 0; I <table. Columns. Count; I ++)

{

Title + = table. Columns [I]. ColumnName + "\ t"; // field: automatically jumps to the next cell.

}

Title = title. Substring (0, title. Length-1) + "\ n ";

Sw. Write (title );

Foreach (DataRow row in table. Rows)

{

String line = "";

For (int I = 0; I <table. Columns. Count; I ++)

{

Line + = row [I]. ToString (). Trim () + "\ t"; // content: automatically jumps to the next cell.

}

Line = line. Substring (0, line. Length-1) + "\ n ";

Sw. Write (line );

}

Sw. Close ();

Fs. Close ();

}

DataTableToCsv (dt, @ "c: \ 1.xls"); // call the Function

System. Diagnostics. Process. Start (@ "c: \ 1.xls"); // open an excel file

 

Www.2cto.com

Method 3: (you can adjust the cell format by yourself, suitable for the number of small batches)

Try

{

// If there is no data, do not execute it.

If (maid. Count = 0)

Return;

// Instantiate an Excel. Application Object

Microsoft. Office. Interop. Excel. Application excel = new Microsoft. Office. Interop. Excel. Application ();

// Set the background execution to invisible. If the value is true, an Excel file is opened and the data is written in.

// Excel. Visible = false;

Excel. Visible = true;

// Add a new Workbook. The Workbook is saved directly. The save dialog box is not displayed. The save dialog box is displayed when the Application is added. If the value is false, an error is returned.

Excel. Application. Workbooks. Add (true );

// Generate the column header name in Excel

For (int I = 0; I <dataGridView1.Columns. Count; I ++)

{

Excel. Cells [1, I + 1] = maid [I]. HeaderText;

}

// Save the data on the current page of The DataGridView in Excel

For (int I = 0; I <dataGridView1.Rows. Count-1; I ++)

{

For (int j = 0; j <dataGridView1.Columns. Count; j ++)

{

If (maid [j, I]. ValueType = typeof (string ))

{

Excel. Cells [I + 2, j + 1] = "'" + dataGridView1 [j, I]. Value. ToString ();

}

Else

{

Excel. Cells [I + 2, j + 1] = maid [j, I]. Value. ToString ();

}

}

}

// Set to prohibit the pop-up prompt box for saving and overwriting

Excel. DisplayAlerts = false;

Excel. AlertBeforeOverwriting = false;

/// Save the workbook

// Excel. Application. Workbooks. Add (true). Save ();

/// Save the excel file

// Excel. Save ("D:" + "\ KKHMD.xls ");

/// Make sure that the Excel process is disabled

// Excel. Quit (); // you can open the file directly.

// Excel = null;

}

Catch (Exception ex)

{

MessageBox. Show (ex. Message, "error prompt ");

}

Excel. output (DataTable) dataGridView1.DataSource );

From brian0031