1
///
<summary>
2 /// 依据excel模版导出数据
3 /// </summary>
4 /// <param name="path"></param>
5 /// <param name="gv"></param>
6 private void CreateExcelYLTeamList( string path, GridView gv)
7 {
8 // 请求一个Excel的类
9 Microsoft.Office.Interop.Excel.ApplicationClass excel = excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
10 Microsoft.Office.Interop.Excel._Workbook workbook = null;
11 Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
12
13 try
14 {
15
16 // 要保存的文件名
17 string FullFileName = path;
18 object missing = System.Reflection.Missing.Value;
19 try
20 {
21 // 打开模板文件，得到WorkBook对象
22 workbook = excel.Workbooks.Open(FullFileName, missing, missing, missing, missing, missing,
23 missing, missing, missing, missing, missing, missing, missing, missing, missing);
24 worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[ 1]; // 指定操作第一个表
25 // 激活
26 workbook.Activate();
27 int sum0 = 0;
28 int sum1 = 0;
29 int sum2 = 0;
30 int sum3 = 0;
31 int sum4 = 0;
32 int sum5 = 0;
33 double sum6 = 0;
34 double sum7 = 0;
35 double sum8 = 0;
36 double sum9 = 0;
37 double sum10 = 0;
38 int sum11 = 0;
39 int sum12 = 0;
40 double sum13 = 0;
41 double sum14 = 0;
42 double sum15 = 0;
43
44 // 给主表添加数据
45 excel.Cells[ 2, 1] = " 填报单位：武汉市墙改办 ";
46 object[,] dataArray = new object[gv.Rows.Count, 18];
47 for ( int i = 0; i < gv.Rows.Count; i++)
48 {
49 // System.Web.UI.WebControls.Label lb = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[0].FindControl("lbno");
50 dataArray[ 0, 0] = " 1 ";
51 dataArray[ 1, 0] = " 2 ";
52 if(i>= 2)
53 {
54 dataArray[i, 0] = " 2. " + (i - 1);
55 }
56 dataArray[gv.Rows.Count - 1, 0] = " 3 ";
57 System.Web.UI.WebControls.Label lbArea = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbArea ");
58 dataArray[i, 1] = ChangeCell(lbArea.Text);
59 dataArray[i, 2] = ChangeCell(ConvertStringToInt(gv.Rows[i].Cells[ 2].Text) + ConvertStringToInt(gv.Rows[i].Cells[ 3].Text)+ "");
60 dataArray[i, 3] = ChangeCell(gv.Rows[i].Cells[ 2].Text);
61 dataArray[i, 4] = ChangeCell(gv.Rows[i].Cells[ 3].Text);
62 dataArray[i, 5] = ChangeCell(gv.Rows[i].Cells[ 4].Text);
63 dataArray[i, 6] = ChangeCell(gv.Rows[i].Cells[ 5].Text);
64 dataArray[i, 7] = ChangeCell(gv.Rows[i].Cells[ 7].Text);
65 dataArray[i, 8] = ChangeCell(gv.Rows[i].Cells[ 8].Text);
66 dataArray[i, 9] = ChangeCell(gv.Rows[i].Cells[ 9].Text);
67 dataArray[i, 10] = ChangeCell(ConvertStringToDouble (gv.Rows[i].Cells[ 10].Text)+ConvertStringToDouble(gv.Rows[i].Cells[ 11].Text)+ "");
68 dataArray[i, 11] = ChangeCell(gv.Rows[i].Cells[ 10].Text);
69 dataArray[i, 12] = ChangeCell(gv.Rows[i].Cells[ 11].Text);
70 System.Web.UI.WebControls.Label lbFactoryCount = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbFactoryCount ");
71 dataArray[i, 13] = ChangeCell(lbFactoryCount.Text);
72
73 System.Web.UI.WebControls.Label lbFactoryCompareCount = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbFactoryCompareCount ");
74 dataArray[i, 14] = ChangeCell(lbFactoryCompareCount.Text);
75
76 System.Web.UI.WebControls.Label lbYearNumEd = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbYearNumEd ");
77 dataArray[i, 15] = ChangeCell(lbYearNumEd.Text);
78
79 System.Web.UI.WebControls.Label lbYearNumIngYear = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbYearNumIngYear ");
80 dataArray[i, 16] = ChangeCell(lbYearNumIngYear.Text);
81
82 System.Web.UI.WebControls.Label lbYearNumIngCompareYear = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbYearNumIngCompareYear ");
83 dataArray[i, 17] = ChangeCell(lbYearNumIngCompareYear.Text);
84
85 // sum0 += string.IsNullOrEmpty(dataArray[i, 2].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 2]);
86 // sum1 += string.IsNullOrEmpty(dataArray[i, 3].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 3]);
87 // sum2 += string.IsNullOrEmpty(dataArray[i, 4].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 4]);
88 // sum3 += string.IsNullOrEmpty(dataArray[i, 5].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 5]);
89 // sum4 += string.IsNullOrEmpty(dataArray[i, 6].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 6]);
90 // sum5 += string.IsNullOrEmpty(dataArray[i, 7].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 7]);
91 // sum6 += string.IsNullOrEmpty(dataArray[i, 8].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 8]);
92 // sum7 += string.IsNullOrEmpty(dataArray[i, 9].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 9]);
93 // sum8 += string.IsNullOrEmpty(dataArray[i, 10].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 10]);
94 // sum9 += string.IsNullOrEmpty(dataArray[i, 11].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 11]);
95 // sum10 += string.IsNullOrEmpty(dataArray[i, 12].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 12]);
96 // sum11 += string.IsNullOrEmpty(dataArray[i, 13].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 13]);
97 // sum12 += string.IsNullOrEmpty(dataArray[i, 14].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 14]);
98 // sum13 += string.IsNullOrEmpty(dataArray[i, 15].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 15]);
99 // sum14 += string.IsNullOrEmpty(dataArray[i, 16].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 16]);
100 // sum15 += string.IsNullOrEmpty(dataArray[i, 17].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 17]);
101
102 }
103 // worksheet.Cells[8, 3] = sum0.ToString();
104 // worksheet.Cells[8, 4] = sum1.ToString();
105 // worksheet.Cells[8, 5] = sum2.ToString();
106 // worksheet.Cells[8, 6] = sum3.ToString();
107 // worksheet.Cells[8, 7] = sum4.ToString();
108 // worksheet.Cells[8, 8] = sum5.ToString();
109 // worksheet.Cells[8, 9] = sum6.ToString();
110 // worksheet.Cells[8, 10] = sum7.ToString();
111 // worksheet.Cells[8, 11] = sum8.ToString();
112 // worksheet.Cells[8, 12] = sum9.ToString();
113 // worksheet.Cells[8, 13] = sum10.ToString();
114 // worksheet.Cells[8, 14] = sum11.ToString();
115 // worksheet.Cells[8, 15] = sum12.ToString();
116 // worksheet.Cells[8, 16] = sum13.ToString();
117 // worksheet.Cells[8, 17] = sum14.ToString();
118 // worksheet.Cells[8, 18] = sum15.ToString();
119 Range rag = worksheet.get_Range(worksheet.Cells[ 7, 1], worksheet.Cells[gv.Rows.Count + 6, 18]);
120 rag.Value2 = dataArray;
121 rag.Borders.LineStyle = 1;
122
123 // worksheet.Cells[gv.Rows.Count + 8 + 1, 1] = "3";
124 // worksheet.Cells[gv.Rows.Count + 8 + 1, 2] = "汇总";
125 // worksheet.Cells[gv.Rows.Count + 8 + 1, 3] = sum0;
126 // worksheet.Cells[gv.Rows.Count + 8 + 1, 4] = sum1;
127 // worksheet.Cells[gv.Rows.Count + 8 + 1, 5] = sum2;
128 // worksheet.Cells[gv.Rows.Count + 8 + 1, 6] = sum3;
129 // worksheet.Cells[gv.Rows.Count + 8 + 1, 7] = sum4;
130 // worksheet.Cells[gv.Rows.Count + 8 + 1, 8] = sum5;
131 // worksheet.Cells[gv.Rows.Count + 8 + 1, 9] = sum6;
132 // worksheet.Cells[gv.Rows.Count + 8 + 1, 10] = sum7;
133 // worksheet.Cells[gv.Rows.Count + 8 + 1, 11] = sum8;
134 // worksheet.Cells[gv.Rows.Count + 8 + 1, 12] = sum9;
135 // worksheet.Cells[gv.Rows.Count + 8 + 1, 13] = sum10;
136 // worksheet.Cells[gv.Rows.Count + 8 + 1, 14] = sum11;
137 // worksheet.Cells[gv.Rows.Count + 8 + 1, 15] = sum12;
138 // worksheet.Cells[gv.Rows.Count + 8 + 1, 16] = sum13;
139 // worksheet.Cells[gv.Rows.Count + 8 + 1, 17] = sum14;
140 // worksheet.Cells[gv.Rows.Count + 8 + 1, 18] = sum15;
141
142 Range rag0 = worksheet.get_Range(worksheet.Cells[ 1, 1], worksheet.Cells[ 1, 18]);
143 rag0.MergeCells = true; // 合并单元格
144 rag0.Value2 = Common.ConvertYearToString(Convert.ToInt32(ddlYear.SelectedValue)) + " 年度新型墙材与粘土砖生产应用情况统计表 ";
145
146 Range rag1 = worksheet.get_Range(worksheet.Cells[gv.Rows.Count + 6 + 1, 1], worksheet.Cells[gv.Rows.Count + 6 + 2, 18]);
147 rag1.MergeCells = true; // 合并单元格
148 rag1.Font.Bold = true;
149 rag1.Value2 = " 填表说明：“序号1”栏填写各市、州、林区、直管市中心城区统计数据，“序号2”栏小计所辖县级市、县城城区统计数据，“序号2.1”至“序号2.10”栏分别 \n "
150 +
151 " 填写所辖各县级市、县城城区数据，“序号3”栏汇总“序号1”与 “序号2”之和。 ";
152
153 Range rag2 = worksheet.get_Range(worksheet.Cells[gv.Rows.Count + 6 + 3, 1], worksheet.Cells[gv.Rows.Count + 6 + 3, 18]);
154 rag1.Font.Bold = rag2.Font.Bold = true;
155 rag1.Font.Size = rag2.Font.Size = 11;
156 rag1.Borders.LineStyle = 1;
157
158 Range rag3 = worksheet.get_Range(worksheet.Cells[ 4, 3], worksheet.Cells[ 4, 5]);
159 rag3.MergeCells = true; // 合并单元格
160 rag3.Borders.LineStyle = 1;
161 rag3.Value = ddlYear.SelectedValue + " 年底实有砖厂（家） ";
162
163 worksheet.Cells[gv.Rows.Count + 6 + 3, 1] = " 单位负责人： ";
164 worksheet.Cells[gv.Rows.Count + 6 + 3, 8] = " 填报人： ";
165 worksheet.Cells[gv.Rows.Count + 6 + 3, 17] = " 填报日期： " + DateTime.Today.Year + " 年 " + DateTime.Today.Month + " 月 " + DateTime.Today.Day + " 日 ";
166 excel.Application.DisplayAlerts = false; // 不显示提示信息
167 string filenewname =ddlYear.SelectedValue+ " 年度新型墙材与粘土砖生产应用情况统计表 " + " .xls ";
168 if (Directory.Exists(Server.MapPath( " ~/Download ")) == false)
169 {
170 Directory.CreateDirectory(Server.MapPath( " ~/Download "));
171 }
172 else
173 {
174 if (Directory.Exists(Server.MapPath( " ~/Download/TClayBrick ")) == false)
175 {
176 Directory.CreateDirectory(Server.MapPath( " ~/Download/TClayBrick "));
177 }
178 else
179 {
180 // 判断文件的存在
181 if (File.Exists(Server.MapPath( " ~/Download/TClayBrick/ " + filenewname + "")))
182 {
183 File.Delete(MapPath( " ~/Download/TClayBrick/ " + filenewname + ""));
184 }
185 else
186 {
187 File.Create(MapPath( " ~/Download/TClayBrick/ " + filenewname + ""));
188 }
189 }
190 }
191 string savefilenname = Server.MapPath(Request.ApplicationPath) + " Download\\TClayBrick\\ " + filenewname;
192 workbook.SaveAs(savefilenname, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
193 FileName = " download/TClayBrick/ " + filenewname;
194 ScriptManager.RegisterStartupScript( this.ImageReport, this.ImageReport.GetType(), " SaveSucceed ", " showDig(); ", true);
195 lblLink.Text = " <a target='_blank' href=' " + FileName + " '>下载</a> ";
196 // Response.Redirect(""+filenewname+"");
197 // HttpContext.Current.Response.Write(" <script language='javascript'>alert('导出成功！') </script>");
198 // HttpContext.Current.Response.Write(" <script language='javascript'>window.open(" +savefilenname+ ",'_bank')</script>");
199 }
200 finally
201 {
202 // 关闭，释放
203 if (workbook != null)
204 {
205 workbook.Close( false, null, null);
206 workbook = null;
207 worksheet = null;
208 }
209 }
210 }
211 finally
212 {
213 // 关闭，释放
214 if (excel != null)
215 {
216 excel.Quit();
217 excel = null;
218 }
219
220 }
221 }
222 // 将空对象转换成整形类型默认的0
223 protected int ConvertStringToInt( string text)
224 {
225 return string.IsNullOrEmpty(text)||text== " " ? 0 : Convert.ToInt32(text);
226 }
227 // 将空对象转换成浮点类型默认的0
228 protected Double ConvertStringToDouble( string text)
229 {
230 return string.IsNullOrEmpty(text) || text == " " ? 0 : Convert.ToDouble(text);
231 }
232 /// <summary>
233 /// 转换空数据列
234 /// </summary>
235 /// <param name="celltext"></param>
236 /// <returns></returns>
237 protected string ChangeCell( string celltext)
238 {
239 if ( string.IsNullOrEmpty(celltext) || celltext == " ")
240 {
241 return " 0 ";
242 }
243 else
244 {
245 return celltext.Trim();
246 }
247 }
2 /// 依据excel模版导出数据
3 /// </summary>
4 /// <param name="path"></param>
5 /// <param name="gv"></param>
6 private void CreateExcelYLTeamList( string path, GridView gv)
7 {
8 // 请求一个Excel的类
9 Microsoft.Office.Interop.Excel.ApplicationClass excel = excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
10 Microsoft.Office.Interop.Excel._Workbook workbook = null;
11 Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
12
13 try
14 {
15
16 // 要保存的文件名
17 string FullFileName = path;
18 object missing = System.Reflection.Missing.Value;
19 try
20 {
21 // 打开模板文件，得到WorkBook对象
22 workbook = excel.Workbooks.Open(FullFileName, missing, missing, missing, missing, missing,
23 missing, missing, missing, missing, missing, missing, missing, missing, missing);
24 worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[ 1]; // 指定操作第一个表
25 // 激活
26 workbook.Activate();
27 int sum0 = 0;
28 int sum1 = 0;
29 int sum2 = 0;
30 int sum3 = 0;
31 int sum4 = 0;
32 int sum5 = 0;
33 double sum6 = 0;
34 double sum7 = 0;
35 double sum8 = 0;
36 double sum9 = 0;
37 double sum10 = 0;
38 int sum11 = 0;
39 int sum12 = 0;
40 double sum13 = 0;
41 double sum14 = 0;
42 double sum15 = 0;
43
44 // 给主表添加数据
45 excel.Cells[ 2, 1] = " 填报单位：武汉市墙改办 ";
46 object[,] dataArray = new object[gv.Rows.Count, 18];
47 for ( int i = 0; i < gv.Rows.Count; i++)
48 {
49 // System.Web.UI.WebControls.Label lb = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[0].FindControl("lbno");
50 dataArray[ 0, 0] = " 1 ";
51 dataArray[ 1, 0] = " 2 ";
52 if(i>= 2)
53 {
54 dataArray[i, 0] = " 2. " + (i - 1);
55 }
56 dataArray[gv.Rows.Count - 1, 0] = " 3 ";
57 System.Web.UI.WebControls.Label lbArea = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbArea ");
58 dataArray[i, 1] = ChangeCell(lbArea.Text);
59 dataArray[i, 2] = ChangeCell(ConvertStringToInt(gv.Rows[i].Cells[ 2].Text) + ConvertStringToInt(gv.Rows[i].Cells[ 3].Text)+ "");
60 dataArray[i, 3] = ChangeCell(gv.Rows[i].Cells[ 2].Text);
61 dataArray[i, 4] = ChangeCell(gv.Rows[i].Cells[ 3].Text);
62 dataArray[i, 5] = ChangeCell(gv.Rows[i].Cells[ 4].Text);
63 dataArray[i, 6] = ChangeCell(gv.Rows[i].Cells[ 5].Text);
64 dataArray[i, 7] = ChangeCell(gv.Rows[i].Cells[ 7].Text);
65 dataArray[i, 8] = ChangeCell(gv.Rows[i].Cells[ 8].Text);
66 dataArray[i, 9] = ChangeCell(gv.Rows[i].Cells[ 9].Text);
67 dataArray[i, 10] = ChangeCell(ConvertStringToDouble (gv.Rows[i].Cells[ 10].Text)+ConvertStringToDouble(gv.Rows[i].Cells[ 11].Text)+ "");
68 dataArray[i, 11] = ChangeCell(gv.Rows[i].Cells[ 10].Text);
69 dataArray[i, 12] = ChangeCell(gv.Rows[i].Cells[ 11].Text);
70 System.Web.UI.WebControls.Label lbFactoryCount = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbFactoryCount ");
71 dataArray[i, 13] = ChangeCell(lbFactoryCount.Text);
72
73 System.Web.UI.WebControls.Label lbFactoryCompareCount = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbFactoryCompareCount ");
74 dataArray[i, 14] = ChangeCell(lbFactoryCompareCount.Text);
75
76 System.Web.UI.WebControls.Label lbYearNumEd = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbYearNumEd ");
77 dataArray[i, 15] = ChangeCell(lbYearNumEd.Text);
78
79 System.Web.UI.WebControls.Label lbYearNumIngYear = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbYearNumIngYear ");
80 dataArray[i, 16] = ChangeCell(lbYearNumIngYear.Text);
81
82 System.Web.UI.WebControls.Label lbYearNumIngCompareYear = (System.Web.UI.WebControls.Label)gv.Rows[i].Cells[ 0].FindControl( " lbYearNumIngCompareYear ");
83 dataArray[i, 17] = ChangeCell(lbYearNumIngCompareYear.Text);
84
85 // sum0 += string.IsNullOrEmpty(dataArray[i, 2].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 2]);
86 // sum1 += string.IsNullOrEmpty(dataArray[i, 3].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 3]);
87 // sum2 += string.IsNullOrEmpty(dataArray[i, 4].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 4]);
88 // sum3 += string.IsNullOrEmpty(dataArray[i, 5].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 5]);
89 // sum4 += string.IsNullOrEmpty(dataArray[i, 6].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 6]);
90 // sum5 += string.IsNullOrEmpty(dataArray[i, 7].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 7]);
91 // sum6 += string.IsNullOrEmpty(dataArray[i, 8].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 8]);
92 // sum7 += string.IsNullOrEmpty(dataArray[i, 9].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 9]);
93 // sum8 += string.IsNullOrEmpty(dataArray[i, 10].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 10]);
94 // sum9 += string.IsNullOrEmpty(dataArray[i, 11].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 11]);
95 // sum10 += string.IsNullOrEmpty(dataArray[i, 12].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 12]);
96 // sum11 += string.IsNullOrEmpty(dataArray[i, 13].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 13]);
97 // sum12 += string.IsNullOrEmpty(dataArray[i, 14].ToString()) ? 0 : Convert.ToInt32(dataArray[i, 14]);
98 // sum13 += string.IsNullOrEmpty(dataArray[i, 15].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 15]);
99 // sum14 += string.IsNullOrEmpty(dataArray[i, 16].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 16]);
100 // sum15 += string.IsNullOrEmpty(dataArray[i, 17].ToString()) ? 0 : Convert.ToDouble(dataArray[i, 17]);
101
102 }
103 // worksheet.Cells[8, 3] = sum0.ToString();
104 // worksheet.Cells[8, 4] = sum1.ToString();
105 // worksheet.Cells[8, 5] = sum2.ToString();
106 // worksheet.Cells[8, 6] = sum3.ToString();
107 // worksheet.Cells[8, 7] = sum4.ToString();
108 // worksheet.Cells[8, 8] = sum5.ToString();
109 // worksheet.Cells[8, 9] = sum6.ToString();
110 // worksheet.Cells[8, 10] = sum7.ToString();
111 // worksheet.Cells[8, 11] = sum8.ToString();
112 // worksheet.Cells[8, 12] = sum9.ToString();
113 // worksheet.Cells[8, 13] = sum10.ToString();
114 // worksheet.Cells[8, 14] = sum11.ToString();
115 // worksheet.Cells[8, 15] = sum12.ToString();
116 // worksheet.Cells[8, 16] = sum13.ToString();
117 // worksheet.Cells[8, 17] = sum14.ToString();
118 // worksheet.Cells[8, 18] = sum15.ToString();
119 Range rag = worksheet.get_Range(worksheet.Cells[ 7, 1], worksheet.Cells[gv.Rows.Count + 6, 18]);
120 rag.Value2 = dataArray;
121 rag.Borders.LineStyle = 1;
122
123 // worksheet.Cells[gv.Rows.Count + 8 + 1, 1] = "3";
124 // worksheet.Cells[gv.Rows.Count + 8 + 1, 2] = "汇总";
125 // worksheet.Cells[gv.Rows.Count + 8 + 1, 3] = sum0;
126 // worksheet.Cells[gv.Rows.Count + 8 + 1, 4] = sum1;
127 // worksheet.Cells[gv.Rows.Count + 8 + 1, 5] = sum2;
128 // worksheet.Cells[gv.Rows.Count + 8 + 1, 6] = sum3;
129 // worksheet.Cells[gv.Rows.Count + 8 + 1, 7] = sum4;
130 // worksheet.Cells[gv.Rows.Count + 8 + 1, 8] = sum5;
131 // worksheet.Cells[gv.Rows.Count + 8 + 1, 9] = sum6;
132 // worksheet.Cells[gv.Rows.Count + 8 + 1, 10] = sum7;
133 // worksheet.Cells[gv.Rows.Count + 8 + 1, 11] = sum8;
134 // worksheet.Cells[gv.Rows.Count + 8 + 1, 12] = sum9;
135 // worksheet.Cells[gv.Rows.Count + 8 + 1, 13] = sum10;
136 // worksheet.Cells[gv.Rows.Count + 8 + 1, 14] = sum11;
137 // worksheet.Cells[gv.Rows.Count + 8 + 1, 15] = sum12;
138 // worksheet.Cells[gv.Rows.Count + 8 + 1, 16] = sum13;
139 // worksheet.Cells[gv.Rows.Count + 8 + 1, 17] = sum14;
140 // worksheet.Cells[gv.Rows.Count + 8 + 1, 18] = sum15;
141
142 Range rag0 = worksheet.get_Range(worksheet.Cells[ 1, 1], worksheet.Cells[ 1, 18]);
143 rag0.MergeCells = true; // 合并单元格
144 rag0.Value2 = Common.ConvertYearToString(Convert.ToInt32(ddlYear.SelectedValue)) + " 年度新型墙材与粘土砖生产应用情况统计表 ";
145
146 Range rag1 = worksheet.get_Range(worksheet.Cells[gv.Rows.Count + 6 + 1, 1], worksheet.Cells[gv.Rows.Count + 6 + 2, 18]);
147 rag1.MergeCells = true; // 合并单元格
148 rag1.Font.Bold = true;
149 rag1.Value2 = " 填表说明：“序号1”栏填写各市、州、林区、直管市中心城区统计数据，“序号2”栏小计所辖县级市、县城城区统计数据，“序号2.1”至“序号2.10”栏分别 \n "
150 +
151 " 填写所辖各县级市、县城城区数据，“序号3”栏汇总“序号1”与 “序号2”之和。 ";
152
153 Range rag2 = worksheet.get_Range(worksheet.Cells[gv.Rows.Count + 6 + 3, 1], worksheet.Cells[gv.Rows.Count + 6 + 3, 18]);
154 rag1.Font.Bold = rag2.Font.Bold = true;
155 rag1.Font.Size = rag2.Font.Size = 11;
156 rag1.Borders.LineStyle = 1;
157
158 Range rag3 = worksheet.get_Range(worksheet.Cells[ 4, 3], worksheet.Cells[ 4, 5]);
159 rag3.MergeCells = true; // 合并单元格
160 rag3.Borders.LineStyle = 1;
161 rag3.Value = ddlYear.SelectedValue + " 年底实有砖厂（家） ";
162
163 worksheet.Cells[gv.Rows.Count + 6 + 3, 1] = " 单位负责人： ";
164 worksheet.Cells[gv.Rows.Count + 6 + 3, 8] = " 填报人： ";
165 worksheet.Cells[gv.Rows.Count + 6 + 3, 17] = " 填报日期： " + DateTime.Today.Year + " 年 " + DateTime.Today.Month + " 月 " + DateTime.Today.Day + " 日 ";
166 excel.Application.DisplayAlerts = false; // 不显示提示信息
167 string filenewname =ddlYear.SelectedValue+ " 年度新型墙材与粘土砖生产应用情况统计表 " + " .xls ";
168 if (Directory.Exists(Server.MapPath( " ~/Download ")) == false)
169 {
170 Directory.CreateDirectory(Server.MapPath( " ~/Download "));
171 }
172 else
173 {
174 if (Directory.Exists(Server.MapPath( " ~/Download/TClayBrick ")) == false)
175 {
176 Directory.CreateDirectory(Server.MapPath( " ~/Download/TClayBrick "));
177 }
178 else
179 {
180 // 判断文件的存在
181 if (File.Exists(Server.MapPath( " ~/Download/TClayBrick/ " + filenewname + "")))
182 {
183 File.Delete(MapPath( " ~/Download/TClayBrick/ " + filenewname + ""));
184 }
185 else
186 {
187 File.Create(MapPath( " ~/Download/TClayBrick/ " + filenewname + ""));
188 }
189 }
190 }
191 string savefilenname = Server.MapPath(Request.ApplicationPath) + " Download\\TClayBrick\\ " + filenewname;
192 workbook.SaveAs(savefilenname, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
193 FileName = " download/TClayBrick/ " + filenewname;
194 ScriptManager.RegisterStartupScript( this.ImageReport, this.ImageReport.GetType(), " SaveSucceed ", " showDig(); ", true);
195 lblLink.Text = " <a target='_blank' href=' " + FileName + " '>下载</a> ";
196 // Response.Redirect(""+filenewname+"");
197 // HttpContext.Current.Response.Write(" <script language='javascript'>alert('导出成功！') </script>");
198 // HttpContext.Current.Response.Write(" <script language='javascript'>window.open(" +savefilenname+ ",'_bank')</script>");
199 }
200 finally
201 {
202 // 关闭，释放
203 if (workbook != null)
204 {
205 workbook.Close( false, null, null);
206 workbook = null;
207 worksheet = null;
208 }
209 }
210 }
211 finally
212 {
213 // 关闭，释放
214 if (excel != null)
215 {
216 excel.Quit();
217 excel = null;
218 }
219
220 }
221 }
222 // 将空对象转换成整形类型默认的0
223 protected int ConvertStringToInt( string text)
224 {
225 return string.IsNullOrEmpty(text)||text== " " ? 0 : Convert.ToInt32(text);
226 }
227 // 将空对象转换成浮点类型默认的0
228 protected Double ConvertStringToDouble( string text)
229 {
230 return string.IsNullOrEmpty(text) || text == " " ? 0 : Convert.ToDouble(text);
231 }
232 /// <summary>
233 /// 转换空数据列
234 /// </summary>
235 /// <param name="celltext"></param>
236 /// <returns></returns>
237 protected string ChangeCell( string celltext)
238 {
239 if ( string.IsNullOrEmpty(celltext) || celltext == " ")
240 {
241 return " 0 ";
242 }
243 else
244 {
245 return celltext.Trim();
246 }
247 }