导出到EXCEL：利用模版
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 }
