"C #" Excel exports merged rows and columns and dynamically loads rows and columns

Simple Excel export is good to do, as long as set the table header, the loop in the table to assign values to add data can, but if the table header is not fixed, and the number is indeterminate, this needs to be based on the characteristics of the query data to add the export.

Export:

As shown, the number of items is indeterminate, the number of months of time is also uncertain, so simple through the template is not possible. And the information in the database is the information of each commodity at different time, so the data of the same time may have more than one, a commodity in different time distribution, so it can be more than one.

Code section:

[CSharp]View PlainCopy

2. public int Datatabletoexcelbymproduct (DataTable dt_model, string sheetname)
4. {
6. Workbook = new Hssfworkbook ();
8. Isheet sheet = workbook. Createsheet (SheetName);
10. IRow row = null;
12. Icell cell = null;
14. //Style
16. Icellstyle style = workbook. Createcellstyle ();
18. Style. Alignment = HorizontalAlignment.Center; //Set the style of the cell: Horizontal Align Center
20. Style. VerticalAlignment = Verticalalignment.center; //Set cell style: Vertical Align Center
22. IFont font = workbook. CreateFont (); //Create a new Font style object
24. Font. Boldweight = Short .  MaxValue;
26. Style. SetFont (font);
28. DateTime nowtime = DateTime.Now;
32. //number of product codes
34. var dtgroup= (from P in Dt_model. AsEnumerable ()
36. Group p by new {
38. realname=p.field<string> ("Realname"),
40. name=p.field<string> ("Name")
42. }into g
44. Select New {
46. Realname=g.key.realname,
48. Name=g.key.name,
50. Counts=g.count ()
52. }). ToList ();
54. //Data table header, time
58. row = sheet. CreateRow (0);
60. Cell = row. Createcell (0);
62. Cell.  Setcellvalue ("time");
64. Cell. CellStyle = style;
66. //cellrangeaddress Four parameters: Start line, end row, start column, end column
68. Sheet.  Addmergedregion (new cellrangeaddress (0, 2, 0, 0));
72. //Access port 1,1,2, number of ports
74. Cell = row. Createcell (1);
76. Cell. Setcellvalue ("Import and export commodity Code");
78. Cell. CellStyle = style;
80. //cellrangeaddress Four parameters: Start line, end row, start column, end column
82. Sheet. Addmergedregion (new cellrangeaddress (0, 0, 1, 2*dtgroup.  Count));
86. //Product splicing
88. row = sheet. CreateRow (1);
90. row = sheet. CreateRow (2);
92. For ( int c=0;c<dtgroup. count;c++)
94. {
96. //Create Import/Export bank cell, start
98. Cell = row. Createcell (2*c+1);
100. Cell. Setcellvalue (Dtgroup[c]. Realname);
102. Cell. CellStyle = style;
104. //cellrangeaddress Four parameters: Start line, end row, start column, end column
106. Sheet.  Addmergedregion (new Cellrangeaddress (1, 1, 2*c+1, 2*c+2));
110. //Create imported cells
112. //Create a column
114. Cell=row. Createcell (2*c+1);
116. Cell.  Setcellvalue ("import");
118. Cell=row. Createcell (2*c+2);
120. Cell.      Setcellvalue ("exit");
122. }
126. var allyearcount= (from P in Dt_model. AsEnumerable ()
128. Group p by new {year_month=p.field<string> ("Year_month")} into M
130. Select New
132. {
134. Yearmonth =m.key.year_month,
136. Allyearcount=m.count ()
138. }). ToList ();
140. //year
142. For (int i=0;i < allyearcount.count;i++)
144. {
146. Row=sheet. CreateRow (i+3);
148. Cell=row. Createcell (0);
150. string Yearmonth=allyearcount[i].  Yearmonth;
154. int month =safeconvert.toint16 (yearmonth.substring (4,2));
156. Cell.  Setcellvalue ("1-" +month+"month");
162. //Trade to touch situation
164. For (int j=0;j<dtgroup. count;j++)
166. {
168. var items=dt_model. AsEnumerable (). Where (a=>a.field<string> ("Year_month") ==allyearcount[i]. Yearmonth && a.field<string> ("Name") ==dtgroup[j]. Name).  ToList ();
170. if (items. count>0)
172. {
174. Cell=row. Createcell (2*j+1);
176. Cell. Setcellvalue (Items[0].  field<string> ("EXPORT_DESN"));
178. Cell=row. Createcell (2*j+2);
180. Cell. Setcellvalue (Items[0].  field<string> ("IMPORT_DESN"));
182. }
184. Else
186. {
188. Cell=row. Createcell (2*j+1);
190. Cell.  Setcellvalue ("");
192. Cell=row. Createcell (2*j+2);
194. Cell.  Setcellvalue ("");
196. }
200. }
202. }
204. using (FileStream fsm=file.open (filename,filemode.openorcreate,fileaccess.readwrite))
206. {
208. Workbook. Write (FSM);
210. Fsm. Close ();
212. }
214. return 1;
218. }

In fact, the core part of the code is to create rows and columns and assign values to the table, if you have created a row is not created, as in this example, the time cell has created a row, so that "import and export commodity code" will not have to create the line again. However, creating a row must create a column that is created on top of the row, so even if the previous row has already created the column, the next row needs to be recreated.

Summary:

This method passes in the DataTable and the table name, if the data we return is not the direct output need to do some processing, we can use to add a field to the DataTable method, we want to store the results in the new field.

The idea of the export is the same, is to loop the rows and columns, in the table to assign values, the difference is from where to start assigning, the different places to solve, export is the same easy!

"C #" Excel exports merged rows and columns and dynamically loads rows and columns