代码改变世界

C#读取Excel表

2012-11-23 16:51  露珠的微笑  阅读(371)  评论(0)    收藏  举报

C#读取Excel表内容存于dataset（工作中一个同事做的任务源码读了一天，累。。记录下来以后可能用得着）

View Code 
  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 using System.Web.UI;
  6 using System.Web.UI.WebControls;
  7 
  8 using System.Data.OleDb;
  9 using System.IO;
 10 using System.Data;
 11 using System.Text;
 12 
 13 using LitJson;
 14 using DAL;
 15 using System.Xml;
 16 namespace ESM_NET
 17 {
 18     public partial class DownloadTemplate1 : System.Web.UI.Page
 19     {
 20         protected void Page_Load(object sender, EventArgs e)
 21         {
 22             string username = Request["username"] == null ? "" : Request.QueryString["username"];
 23             int temid = Request["temid"] == null ? 0 :Convert.ToInt32( Request.QueryString["temid"]);
 24             if (!IsPostBack)
 25             {
 26             }
 27             else
 28             {
 29                 if (FileDoc.HasFile)
 30                 {
 31                     if (FileDoc.PostedFile.ContentLength > 1024000 * 12)
 32                     {
 33                         Response.Write("<script>parent.window.bb(2);alert('The file can not be more than 12M!')</script>");
 34                         return;
 35                     }
 36 
 37                     //get file name 
 38                     string FileName = FileDoc.FileName;
 39 
 40                     if (string.IsNullOrEmpty(FileName))
 41                     {
 42                         Response.Write("<script>parent.window.bb(2);alert('Please select a file to upload!')</script>");
 43                         return;
 44                     }
 45 
 46 
 47                     //get file type
 48                     string fileType = FileName.Substring(FileName.LastIndexOf('.')).ToLower();
 49 
 50                     //is doc the save file
 51                     if (fileType == ".xls" || fileType == ".xlsx")
 52                     {
 53                         ESM_NET.PublicCs.MainTain mainTain = new ESM_NET.PublicCs.Update();
 54                         
 55 
 56                         //判断模版名称是否存在
 57                         List<Template> list = mainTain.IsExist(temid);
 58                         if (list.Count < 1)
 59                         {
 60                             Response.Write("<script>parent.window.bb(2);alert('The template name error (suggestion: do not modify the name of the downloaded template)');</script>");
 61                             return;
 62                         }
 63                         else
 64                         {
 65 
 66 
 67                             #region 获取Excel里有几个字段
 68                             PublicCs.selects s = JsonMapper.ToObject<PublicCs.selects>(list[0].MouldSelects);
 69                             string[] displayName = s.DisplayName.Split(',');
 70                             string[] columnName = s.ColumnName.Split(',');
 71                             string[] Datatype = s.DBDataTypel.Split(',');
 72                             #endregion
 73 
 74                             #region 用DataSet装上传上来的Excel里的数据
 75                             FileDoc.SaveAs(Server.MapPath("TemUploadFile/" + FileName));
 76                             DataSet ds = ExecleDs(Server.MapPath("TemUploadFile/" + FileName), FileName);
 77                             if (File.Exists(Server.MapPath("TemUploadFile/" + FileName)))
 78                             {
 79                                 File.Delete(Server.MapPath("TemUploadFile/" + FileName));
 80                             }
 81 
 82                          
 83                            // DataTable dt = ds.Tables[0];
 84                             //int dd = dt.Rows.Count;
 85                             DataRow[] dr = ds.Tables[0].Select();
 86                             DataColumnCollection dc = ds.Tables[0].Columns;
 87                             #endregion
 88 
 89                             if (dc.Count == 1 && dr[0][0].ToString() == "")
 90                             {
 91                                 Response.Write("<script>parent.window.bb(2);alert('The template is empty!');</script>");
 92                                 return;
 93                             }
 94                             if (displayName.Length != dc.Count)
 95                             {
 96                                 //excel模版多了一列或少了一列
 97                                 Response.Write("<script>parent.window.bb(2);alert('You upload the template wrong!');</script>");
 98                                 return;
 99                             }
100 
101 
102                             #region
103 
104                             int rowsnum = ds.Tables[0].Rows.Count;
105                            
106 
107                             if (rowsnum <= 1)
108                             {
109                                 //模版是空的
110                                 Response.Write("<script>parent.window.bb(2);alert('The template is empty!');</script>");
111                                 return;
112                             }
113                             else
114                             {
115 
116                                 #region 上传上来的Excel的列名是否有错
117                                 for (int j = 0; j < dc.Count; j++)
118                                 {
119                                     if (dr[0][j].ToString() != displayName[j])
120                                     {
121                                         //列名有误
122                                         Response.Write("<script>parent.window.bb(2);alert('Template column name \"" + dr[0][j].ToString() + "\" is wrong!');</script>");
123                                         return;
124                                     }
125                                 }
126 
127                                 string isnull = "";
128                                 if (rowsnum == 2)
129                                 {
130                                     // for (int b = 0; b < dr.Length; b++)
131                                     //{
132                                     for (int n = 0; n < dc.Count; n++)
133                                     {
134                                         isnull += dr[1][n].ToString();
135                                     }
136                                     //}
137 
138                                     if (isnull == "")
139                                     {
140                                         Response.Write("<script>parent.window.bb(2);alert('No data to operate!');</script>");
141                                         return;
142                                     }
143                                 }
144 
145                                 #endregion
146 
147 
148                                 #region where?
149                                 string updates;
150                                 string updates1;
151                                 string column;
152                                 int indext_requestnum = -1;
153 
154                                 string dbtablename = "," + list[0].MouldDBTableName.ToLower() + ",";
155                                 if (dbtablename.IndexOf(",request,") >= 0)
156                                 {
157                                     column = "," + s.ColumnName.ToLower() + ",";
158                                     if (column.IndexOf(",request.id,") >= 0 || column.IndexOf(",request.requestnum,") >= 0)
159                                     {
160                                         for (int t = 0; t < columnName.Length; t++)
161                                         {
162                                             //if (columnName[t].ToLower() == "id")
163                                             //{
164                                             //indext_id = t; 
165                                             //}
166                                             if (columnName[t].Split('.')[1].ToLower() == "requestnum")
167                                             {
168                                                 indext_requestnum = t;
169                                             }
170                                         }
171                                     }
172 
173                                 }
174                                 #endregion
175 
176                                 if (indext_requestnum == -1)
177                                 {
178                                     //模版没包涵表的关键字段
179                                     Response.Write("<script>parent.window.bb(2);alert('Template not inclusion table key field!');</script>");
180                                     return;
181                                 }
182 
183                                 List<Request> requestlist = new List<Request>();
184                                 string indext_requestnumid = "";
185                                 StringBuilder xml = new StringBuilder();
186 
187                                 Response.Write("<script>parent.window.bb(1);</script>");
188                                 for (int i = 1; i < dr.Length; i++)
189                                 {
190                                     updates = ""; updates1 = "";
191                                     for (int j = 0; j < dc.Count; j++)
192                                     {
193                                         //就一列数据
194                                         if (dc.Count == 1)
195                                         {
196                                             updates = "";
197                                             updates1 = ","+ dr[i][indext_requestnum].ToString();
198                                             indext_requestnumid = dr[i][indext_requestnum].ToString() == "" ? "" : columnName[indext_requestnum] + "=" + dr[i][indext_requestnum].ToString();
199                                         }
200                                         else
201                                         {
202                                             if (j != indext_requestnum)
203                                             {
204                                                 if (Datatype[j] != "int")
205                                                 {
206                                                     updates += dr[i][j].ToString() == "" ? "" : "," + columnName[j] + "='" + dr[i][j].ToString() + "'";
207                                                     updates1 += "," + dr[i][j].ToString();
208                                                 }
209                                                 else
210                                                 {
211                                                     updates += dr[i][j].ToString() == "" ? "" : "," + columnName[j] + "=" + dr[i][j].ToString();
212                                                     updates1 += "," + dr[i][j].ToString();
213                                                 }
214                                             }
215                                             else
216                                             {
217                                                 indext_requestnumid = dr[i][indext_requestnum].ToString() == "" ? "" : columnName[indext_requestnum] + "=" + dr[i][indext_requestnum].ToString() + "";
218                                                 updates1 += "," + dr[i][indext_requestnum].ToString();
219                                             }
220                                         }
221                                     }
222 
223 
224 
225                                     #region 格式验证、判断是否存在这条数据
226                                         xml.Append("<book id='"+ i +"'>");
227                                         xml.Append("<key  RequestNum='" + dr[i][indext_requestnum].ToString() + "'>" + indext_requestnumid + "</key>");
228                                         xml.Append("<set>" +( updates == "" ? "" : updates.Substring(1)) + "</set> ");
229                                         xml.Append("<set1>" + (updates1 == "" ? "" : updates1.Substring(1)) + "</set1> ");
230                                         xml.Append("<TblName>request</TblName>");//?
231                                         xml.Append("<OperaType>" + list[0].MouldType + "</OperaType>");
232                                         xml.Append("<Operator>" + username + "</Operator>");
233                                         xml.Append("<Templateid>" + list[0].Id + "</Templateid>");
234                                         xml.Append("</book>");
235                                     #endregion
236                                    
237                                 }
238                                 // ==============
239                                
240                                 try
241                                 {
242                                     object[] obj = new object[] { "<request>" + xml.ToString() + "</request>" };
243                                     DataTable lis = mainTain.DoWoke(obj).Tables[0];
244                                     if (lis.Rows.Count > 0)
245                                     {
246 
247                                        
248 
249 
250                                         string indexexcel = "";
251                                         for (int h = 0; h < lis.Rows.Count; h++)
252                                         {
253                                             indexexcel += "、" + (lis.Rows[h][0]);
254                                         }
255 
256                                         if (indexexcel != "")
257                                         {
258                                             if (TipTable(displayName, lis, indext_requestnum) != "")
259                                             {
260                                                 Response.Write("<script>parent.window.bb(2);parent.window.aa('" + TipTable(displayName, lis, indext_requestnum) + "');</script>");
261                                             }
262                                             else
263                                             {
264                                                 Response.Write("<script>parent.window.bb(2);parent.window.CloseDiv();alert('Successful operation!');</script>");
265                                             }
266                                             //Response.Write("<script>parent.window.bb('');parent.window.aa('" + indexexcel.Substring(1) + " line update failed!');</script>");
267                                         }
268                                     }
269                                     else
270                                     {
271                                         Response.Write("<script>parent.window.bb(2);alert('Successful operation!');</script>");
272                                     }
273                                 }
274                                 catch(Exception ep)
275                                 {
276                                     Response.Write("<script>parent.window.bb(2);alert('The data format is wrong!');</script>");
277                                 }
278                             }
279                             #endregion
280                         }
281 
282                     }
283                     else
284                     {
285                         Response.Write("<script>parent.window.bb(2);alert('Please upload format \".xls, .xlsx \" file!');</script>");
286                     }
287                 }
288                 else
289                 {
290                     Response.Write("<script>parent.window.bb(2);alert('Please select a file to upload!');</script>");
291                 }
292             }
293         }
294 
295         /// <summary>
296         /// 从Excel文件导入数据
297         /// </summary>
298         /// <param name="ExcelStr">文件的全路径</param>
299         /// <param name="SheetName">Excel文档里的表名称</param>
300         public DataSet ExecleDs(string ExcelStr, string SheetName)
301         {
302             OleDbConnection MyConn_E = new OleDbConnection();
303             OleDbCommand MyComm_E = new OleDbCommand();
304             OleDbDataAdapter MyAdap = new OleDbDataAdapter();
305 
306             DataSet MyTable = new DataSet();
307 
308             string Conn_Str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelStr + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1;'";
309 
310             try
311             {
312                 MyConn_E.ConnectionString = Conn_Str;
313                 MyConn_E.Open();
314 
315                 //Get [sheet1$]
316                 DataTable dtSheetName=MyConn_E.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
317                 string[] strTableNames = new string[dtSheetName.Rows.Count];
318                 for (int k = 0; k <dtSheetName.Rows.Count; k++)
319                 {
320                     strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
321                 }
322                //end
323 
324                 MyComm_E.Connection = MyConn_E;
325                 MyComm_E.CommandText = "select * from [" + strTableNames[0] + "]";
326 
327                 MyAdap.SelectCommand = MyComm_E;
328 
329                 MyAdap.Fill(MyTable, SheetName);
330 
331             }
332             catch (OleDbException Err_My)
333             {
334                 DataSet MyTable1 = null;
335                 return MyTable1;
336             }
337 
338             if (MyConn_E.State == ConnectionState.Open)
339             {
340                 MyConn_E.Close();
341                 MyConn_E.Dispose();
342             }
343             MyComm_E.Dispose();
344             MyAdap.Dispose();
345 
346             return MyTable;
347         }
348 
349 
350         public int IsFramt(string value, string datatype, int c)
351         { 
352             switch(datatype)
353             {
354                 case "int": if (!Valid.isNumber(value)) { c++; } break;
355                 //case "varchar": if (!Valid.isNumber(value)) { c++; } break;
356                 case "datetime": if (!Valid.isDate(value)) { c++; } break;
357             }
358             return c;
359         }
360 
361         public string TipTable(string[] displayname, DataTable list, int indext_requestnum)
362         {
363            //Line number
364             DataRow[] drs = list.Select("tupes=2");//1:sucss;2:fial
365            
366             StringBuilder sb = new StringBuilder();
367             if (drs.Length > 0)
368             {
369                 sb.Append("<table width=\"100%\" border=\"0\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\" class=\"A_Create_table\">");
370 
371                 sb.Append(" <tr class=\"txt-Td-red\">");
372                 sb.Append("<td scope=\"col\">Type</td>");
373                 sb.Append("<td scope=\"col\">LineNum</td>");
374                 for (int i = 0; i < displayname.Length; i++)
375                 {
376                     sb.Append("<td scope=\"col\">" + displayname[i] + "</td>");
377                 }
378                 sb.Append("</tr>");
379 
380                 sb.Append(TipTable1(displayname, drs, indext_requestnum, "Error"));
381               
382 
383                 DataRow[] drs2 = list.Select("tupes=1");//1:sucss;2:fial
384                 if (drs2.Length > 0)
385                 {
386 
387                     sb.Append(TipTable1(displayname, drs2, indext_requestnum, "Sucess"));
388                   
389                 }
390 
391                 sb.Append("</table>");
392 
393             }
394 
395             return sb.ToString();
396         }
397 
398         public string TipTable1(string[] displayname, DataRow[] drs, int indext_requestnum,string classname) 
399         {
400             StringBuilder sb = new StringBuilder();
401                
402 
403                
404                 for (int i = 0; i < drs.Length; i++)
405                 {
406                         sb.Append("<tr>");
407                         sb.Append("<td class=txt-" + classname + ">" + classname + "</td>");
408                         sb.Append("<td class=txt-" + classname + ">" + drs[i][0] + "</td>");
409                         string[] set1 = drs[i][1].ToString().Split(',');
410                         for (int j = 0; j < set1.Length; j++)
411                         {
412                             sb.Append("<td class=txt-" + classname + ">" + set1[j] + "</td>");
413                         }
414                         sb.Append("</tr>");
415                             
416                   
417                 }
418                
419 
420             return sb.ToString();
421         }
422     }
423 }

 

c#下载excel模板

View Code 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;




using ESM_NET.PublicCs;
using System.Drawing;
using BLL;
using DAL;



using LitJson;

namespace ESM_NET
{
    /// <summary>
    /// $codebehindclassname$ 的摘要说明
    /// </summary>

    public class CreateTemplateExcel : System.Web.UI.Page, IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            HttpRequest Request = context.Request;
            HttpResponse Response = context.Response;

            int templateid = Request.QueryString["templateid"] == "" ? 0 : Convert.ToInt32(Request.QueryString["templateid"]);
            BLL.MainTain mt = new BLL.MainTain();
            List<Template> lit = mt.GetDataList(BasePage.getConn(),new object[]{templateid});

            if (lit.Count > 0)
            {

                #region 获取Excel里有几个字段
                PublicCs.selects s = JsonMapper.ToObject<PublicCs.selects>(lit[0].MouldSelects);
                string[] displayName = s.DisplayName.Split(',');
                //string[] columnName = s.ColumnName.Split(',');
                //string[] Datatype = s.DBDataTypel.Split(',');
                #endregion

                //创建Excel 文档
                //Creating Excel documents
                Aspose.Cells.License license = new Aspose.Cells.License();
                license.SetLicense("Aspose.Cells.lic");

                Aspose.Cells.Workbook wkbkReport = new Aspose.Cells.Workbook();
                Aspose.Cells.Cells cells = wkbkReport.Worksheets[0].Cells;

                for (int i = 0; i< displayName.Length; i++)
                {
                    //设置Excel 文档头部标题
                    //Excel documents set the head title
                    cells[0, i].PutValue(displayName[i]);
                   
                }

                //把Excel 文档以流的方式输出到页面提供下载
                //The Excel document to stream output to the page available for download
                string ExportContentType = "Application/Excel";
                Response.AddHeader("Content-Disposition", "filename="+lit[0].MouldName+".xls");
                System.IO.MemoryStream S = wkbkReport.SaveToStream();
                Response.Clear();
                Response.Buffer = true;
                Response.ContentType = ExportContentType;
                Response.BinaryWrite(S.ToArray());
                Response.Flush();
                Response.Close();

            }
        }

    }
}