c # bulk export data to excel [This is a personal sharing]

 This post last edited by the rocket2010 on 2011-07-24 01:29:42 My approach is not to use Microsoft excel operations, because that really burst slowly, one by one cell cycle to write, it is too slow. [Unbelieving friends can try]
The use io write the words do not have half a minute
my database data volume: 120000 row 29

technical points: 1. using StreamWriter sw = new StreamWriter (path, false, Encoding.GetEncoding ("gb2312")); ultimately generate files
2. use StringBuilder sb = new StringBuilder (); class to check out the data combined into one long string disposable insert to excel in
sb.Append (ds.Tables [0]. Columns [k]. ColumnName.ToString () + "\ t");
Note that you can not be missed "\ t" This is very important! Because c # "\ t" is equivalent to the keyboard Tab [friends can try: Open a new txt and then enter a press Tab, Enter 2 Press the Tab, Enter 3 Press Tab to save, and then open the excel Save the file to just pull into txt file open you will find the original. Such words written 123 respectively in each cell will be on it. Therefore, the above only use "\ t" to link the database out of the pile of data, so that once guided into, they will be filled in accordance with each cell! ]

need to reference:
using System.Threading;
using System.IO;
as follows:

 

private void button1_Click (object sender, EventArgs e) 

{
saveFileDialog1.Title = "Save the excel file";
saveFileDialog1.InitialDirectory = "c: \ \";
saveFileDialog1.Filter = "Excel97-2003 (*. xls) | *. xls | All Files ; (*. *) | *. * ";
saveFileDialog1.ShowDialog ();
if (saveFileDialog1.FileName == "" | | saveFileDialog1.FileName = = null) 

{
MessageBox.Show ("File name can not be empty!");
return;
} 

string path = saveFileDialog1.FileName;
string constr = "Data Source =.; Initial Catalog = Exhibition; User ID = sa ; Password = ";
string sql = GetStrSql ();
DataSet ds = new DataSet ();
using (SqlConnection con = new SqlConnection (constr)) 

{
SqlDataAdapter da = new SqlDataAdapter (sql, con);
da.Fill (ds);
} 

if (ds == null) 

{
MessageBox.Show ("Data acquisition is wrong!");
return;
} 

WriteExcel (ds, path);
} 



public void WriteExcel (DataSet ds, string path) 

{
try 

{
long totalCount = ds.Tables [0]. Rows.Count;
lblTip.Text = "Total" + totalCount ; + "of data. ';
Thread.Sleep (1000);
long rowRead = 0;
float percent = 0;


StreamWriter sw = new StreamWriter (path, false, Encoding . GetEncoding ("gb2312"));
StringBuilder sb = new StringBuilder ();
for (int k = 0; k < ds.Tables [0]. Columns.Count; k + +) 

{
sb.Append (ds.Tables [0] . Columns [k]. ColumnName.ToString () + "\ t");
} 

sb.Append (Environment.NewLine);


for (int i = 0; i < ds.Tables [0]. Rows.Count; i + +) 

{
rowRead + +;
percent = ((float) ( 100 * rowRead)) / totalCount;
Pbar.Maximum = (int) totalCount;
Pbar.Value = (int) rowRead;
lblTip.Text = "is Write ["+ percent.ToString (" 0.00 ") +"%] ... Data ";
System.Windows.Forms.Application.DoEvents ( );


for (int j = 0; j 
{
sb. Append (ds.Tables [0]. Rows [i] [j]. ToString () + "\ t");
} 

sb.Append (Environment.NewLine);
} 

sw.Write (sb.ToString ());
sw.Flush ();
sw.Close ();
MessageBox.Show ("Excel file specified has been generated!");
} 

catch (Exception ex) 

{
MessageBox.Show (ex.Message);
}
} 



public string GetStrSql () 

{
string strSql = "select d. * from (select cBarcode, max (case halldoorid When '36 'then '1' else '0 'end) [1.1 Hall], max (case halldoorid When '37 'then '1' else '0 'end) [2.1 Hall], max (case halldoorid When '38' then '1 ' else '0 'end) [3.1 Hall], max (case halldoorid When '39' then '1 'else '0' end ;) [4.1 Hall], max (case halldoorid When '40 'then '1' else '0 'end) [5.1 Hall], max (case halldoorid When '41 'then '1' else '0 'end) [6.1 Hall], max (case halldoorid When '42 'then '1' else '0 'end) [8.1 Hall], max (case halldoorid When '43' then '1 ' else '0 'end) [1.2 Hall], max (case halldoorid When '44' then '1 'else '0' end) [2.2 Hall], max (case halldoorid When '45 'then '1' else '0 'end) [3.2 Hall], max ( case halldoorid When '46 'then '1' else '0 'end) [4.2 Hall], max (case halldoorid When '47 'then '1' else '0 'end) [5.2 Hall], max (case halldoorid When '48' then '1 ' else '0 'end) [9.1 Hall], max (case halldoorid When '49' then '1 'else '0' end) [10.1 Hall], max (case halldoorid When '50 'then '1' else '0 'end) [11.1 Hall], max ( case halldoorid When '51 'then '1' else '0 'end) [12.1 Hall], max (case halldoorid When '52 'then '1' else '0 'end) [13.1 Hall], max (case halldoorid When '53' then '1 ' else '0 'end) [9.2 Hall], max (case halldoorid When '54' then '1 'else '0' end) [10.2 Hall], max (case halldoorid When '55 'then '1' else '0 'end) [11.2 Hall], max ( case halldoorid When '56 'then '1' else '0 'end) [12.2 Hall], max (case halldoorid When '57 'then '1' else '0 'end) [13.2 Hall], max (case halldoorid When '58' then '1 ' else '0 'end) [9.3 Hall], max (case halldoorid When '59' then '1 'else '0' end) [10.3 Hall], max (case halldoorid When '60 'then '1' else '0 'end) [11.3 Hall], max ( case halldoorid When '61 'then '1' else '0 'end) [VIP3.2 Hall], max (case halldoorid When '62 'then '1' else '0 'end) [VIP11.3 Hall], max (case halldoorid When '63' then ' 1 'else '0' end) [VIP14.3 Hall] from tblReadCard group by cBarcode) d left join (select cBarcode ; audience barcode from (select distinct cBarcode, cReadDate from tblReadCard where cReadDate between '2011-07-08 'and '2011-07-11') a group ; by cBarcode) c on d.cBarcode = c. audience barcode group by cBarcode, [1.1, Hall], [2.1, Hall], [Hall 3.1], [4.1 Hall], [5.1, Hall], [Hall 6.1], [8.1, Hall], [Hall 1.2], [2.2, Hall], [3.2, Hall], [Hall 4.2], [5.2, Hall ], [9.1, Hall], [Hall 10.1], [11.1 Hall], [Hall 12.1], [13.1 Hall], [Hall 9.2], [10.2 Hall], [Hall 11.2], [Hall 12.2], [13.2 Hall], [9.3, Hall], [Hall 10.3], [11.3 Hall], [VIP3.2 Hall], [VIP11.3 Hall], [VIP14.3 Hall] ";
return strSql;
} 

------ Solution ------------------------------------ --------
save out this is not a true Excel file .......... just save when you change the extension. xls friends

its essence or a text file.
------ Solution ----------------------------- ---------------
Yes, it is a text file, although it can be opened excel. So this can not be calculated Export to "Excel". In addition, export to excel excel sometimes also convenient to call the function and style, etc. So, what is going on it.
------ For reference only -------------------------------------- -
first very own yourself
------ For reference only --------------------------- ------------
just someone encountered this problem thank you.
------ For reference only -------------------------------------- -
more people look go ...........
------ For reference only ----------------- ----------------------
Thank landlord, there is time to try something
------ For reference only ------ ---------------------------------
seen good
------ For reference only ---------------------------------------
Although he did not come across. But look a little good. Thank
------ For reference only ------------------------------------- -
ask your 120,000 rows of data written to the DataSet,
computer memory can only put it?
------ For reference only -------------------------------------- -
Microsoft excel operations, you can bulk export, huh. 50W rows of data and also less than two minutes ......
------ For reference only ------------------------- --------------

mainly convenient - but really not very fast ~
------ For reference only ----- ----------------------------------
form program to export?
------ For reference only -------------------------------------- -
LZ cattle which have been you found the original so you can
I gnaw did not find it! ! !
Environment.NewLine, is \ r \ n mean?

------ For reference only ---------------------------------- -----
fact csv file
------ For reference only -------------------------- -------------
you try to open the file "xx.xls" format with the file extension specified format is inconsistent. Open the file before you test file is not corrupted and is from a trusted source. Whether to immediately open the file?

exported, with Excel opens with this prompt, click "Yes", you can open correctly.
Why does this prompt?
------ For reference only -------------------------------------- -
learning, thank landlord
------ For reference only ----------------------------- ----------
a sheet pages can not exceed 65535 rows, you can create more than one sheet after the page to save.
------ For reference only -------------------------------------- -
original landlord resolved, Collectors! ! ! !
------ For reference only -------------------------------------- -
top look good!
------ For reference only -------------------------------------- -

---- - For reference only ---------------------------------------
study the matter, Thank landlord
------ For reference only ------------------------------------ ---


You can use the statement in the sql insert so many ranks of the statement cycle try not to know the brothers
------ For reference only ----------- ----------------------------

Xiongtai you posted under your way to let everyone learn Next
------ For reference only ------------------------------------- -


对, string str = "first line" + System. ; Environment.NewLine + "second line";
is equal to \ r \ n mean
------ For reference only -------------------------- -------------

posted under the best possible code under study
look efficiency
------ For reference only ------------------------------- --------
This method is good, I used to use excel operation class each cell had handwritten, exhausted
------ For reference only ---------------------------------------
studied. . . .
------ For reference only -------------------------------------- -
passing onlookers ~ ~ ~ ~ ~ · · ·
------ For reference only ------------------- --------------------
brother ...... you so long to write a sql As it ...... ......

------ For reference only ---------------------------------- -----
brother, you help improve the next thing that SQL
------ For reference only -------------------- -------------------
Haotie buddy cattle too
------ For reference only --------- ------------------------------
to an already written part of the header of the execl in how append?
------ For reference only -------------------------------------- -

  This reply was moderator deleted at 2012-05-29 08:31:23
------ For reference only ---------------------------------- -----
support!
------ For reference only -------------------------------------- -

I did not see it, is how to solve. . .

landlord belief in an Excel sheet opens 12W article? Explain! !


------ For reference only ---------------------------------- -----
understand ~ novice various error. Notes landlord how much to write about it?
------ For reference only -------------------------------------- -
I have to export EXCEL, 10 million data, why StringBuilder strb = new StringBuilder ();
strb.Append (); method would error, why is this?
------ For reference only -------------------------------------- -
trigger type "System.OutOfMemoryException" exception. Know please tell me, thank you. QQ: 373944728
mailbox wj373944728@126.com
------ For reference only ---------------------------- -----------
two days I met a lot of data into EXCEL problem started I also use a cell of the imported methods, but the speed is too slow introduction, today Here the landlord found a new way, thank you.
------ For reference only -------------------------------------- -
collection look .....
------ For reference only --------------------------- ------------
if not through the database directly to the excel file as a data source to be processed, read out the contents of how to excel excel file contents are displayed in the gridview control it?
------ For reference only -------------------------------------- -
landlord 12W I have for you do not understand an import to excel sheet can only import 6W is how much you do