1  /// <summary>
  2         /// 导出数据到EXCEL 多个表的
  3         /// </summary>
  4         /// <param name="ds">数据集</param>
  5         /// <param name="AbosultedFilePath">导出的 EXCEL 路径</param>
  6         /// <param name="name">EXCEL 工作簿的名字</param>
  7         /// <param name="title">表头</param>
  8         /// <returns>返回文件路径</returns>
  9         public static string ExportToExcels(System.Data.DataSet ds, string AbosultedFilePath, string[] name, string title)
 10         {
 11             try
 12             {
 13                 
 14                 string path = ConfigHelper.GetValue("execlFile");
 15 　　　　　　　　　　//判断路径是否存在
 16                 if (Directory.Exists(path))
 17                 {
 18 　　　　　　　　　　　　//删除文件夹及文件
 19                     foreach (string d in Directory.GetFileSystemEntries(path))
 20                     {
 21                         if (File.Exists(d))
 22                         { File.Delete(d); }
 23                     }
 24                     Directory.Delete(path, true);
 25                 }
 26                 int PageIndex = 0;
 27                 if (ds.Tables.Count <= 0)
 28                     return string.Empty;
 29                 for (int t = 0; t < ds.Tables.Count; t++)
 30                 {
 31                     System.Data.DataTable dt = ds.Tables[t];
 32                     int count = dt.Rows.Count;//获取datatable内数据量
 33                     int pagecount = 5000; //每页的数据
 34                     PageIndex = Pagount(count, pagecount); //获取分页数
 35                     string filename = t.ToString() == "0" ? "Area_Statistics" : "IP_statistics";
 36                     //存在分页时 创建新目录保存新execl文件
 37                     if (!Directory.Exists(path))
 38                     {
 39                         Directory.CreateDirectory(path);
 40                     }
 41                     for (int i = 1; i <= PageIndex; i++)
 42                     {
 43                         //将模板文件复制到新目录下
 44                         string fileName = path + "/" + filename + i + ".xls";
 45 　　　　　　　　　　　　　　//根据页码获取DataTable内的数据
 46                         System.Data.DataTable execlDT = GetPagedTable(dt, i, pagecount);
 47 　　　　　　　　　　　　　　//将DataTable内的数据写入execl
 48                         RenderDataTableToExcel(execlDT, fileName);
 49                     }
 50                 }
 51                 //完成写入后 压缩文件
 52                 ZipDir(path, path, 2, title);
 53                 return path + title + ".zip";
 54             }
 55             catch (Exception ex)
 56             {
 57                 Logger.Error("DataTable转execl失败" + ex.Message);
 58                 return string.Empty;
 59             }
 60         }
 61 #region 压缩文件
 62         /// <summary>
 63         /// 压缩文件夹
 64         /// </summary>
 65         /// <param name="DirToZip">文件夹路径</param>
 66         /// <param name="ZipedFile">输出文件路径</param>
 67         /// <param name="CompressionLevel">设置缓存大小</param>
 68         ///<param name="fileName">压缩后的文件名称</param>
 69         public static void ZipDir(string DirToZip, string ZipedFile, int CompressionLevel, string fileName)
 70         {
 71             try
 72             {
 73                 //压缩文件为空时默认与压缩文件夹同一级目录   
 74                 if (ZipedFile == string.Empty)
 75                 {
 76                     ZipedFile = DirToZip.Substring(DirToZip.LastIndexOf("\\") + 1);
 77                     ZipedFile = DirToZip.Substring(0, DirToZip.LastIndexOf("\\")) + "\\" + ZipedFile + ".zip";
 78                 }
 79                 if (System.IO.Path.GetExtension(ZipedFile) != ".zip")
 80                 {
 81                     ZipedFile = ZipedFile + fileName + ".zip";
 82                 }
 83                 using (ZipOutputStream zipoutputstream = new ZipOutputStream(System.IO.File.Create(ZipedFile)))
 84                 {
 85                     zipoutputstream.SetLevel(CompressionLevel);
 86                     Crc32 crc = new Crc32();
 87                     System.IO.DirectoryInfo myDir = new DirectoryInfo(DirToZip);
 88                     List<DictionaryEntry> fileList = GetAllFiles(DirToZip);
 89                     foreach (DictionaryEntry item in fileList)
 90                     {
 91                         //可能存在文件夹无法访问情况 需捕捉异常，根据实际情况返回
 92                         try
 93                         {
 94                             System.IO.FileStream fs = System.IO.File.OpenRead(item.Key.ToString());
 95                             byte[] buffer = new byte[fs.Length];
 96                             fs.Read(buffer, 0, buffer.Length);
 97                             ZipEntry entry = new ZipEntry(item.Key.ToString().Substring(DirToZip.Length + 1));
 98                             entry.DateTime = (DateTime)item.Value;
 99                             entry.Size = fs.Length;
100                             fs.Flush();
101                             fs.Close();
102                             crc.Reset();
103                             crc.Update(buffer);
104                             entry.Crc = crc.Value;
105                             zipoutputstream.PutNextEntry(entry);
106                             zipoutputstream.Write(buffer, 0, buffer.Length);
107                         }
108                         catch (Exception ex)
109                         {
110                             Logger.Error("压缩文件夹：" + ex.Message);
111                         }
112                     }
113                 }
114             }
115             catch (Exception ex)
116             {
117                 Logger.Error("压缩execl文件夹：" + ex.Message);
118             }
119         }
120 
121         /// <summary>   
122         /// 获取所有文件   
123         /// </summary>   
124         /// <returns></returns>   
125         private static List<DictionaryEntry> GetAllFiles(string dir)
126         {
127             try
128             {
129                 List<DictionaryEntry> dictonary = new List<DictionaryEntry>();
130                 if (!System.IO.Directory.Exists(dir))
131                 {
132                     return dictonary;
133                 }
134                 else
135                 {
136                     System.IO.DirectoryInfo root = new System.IO.DirectoryInfo(dir);
137                     System.IO.FileSystemInfo[] arrary = root.GetFileSystemInfos();
138                     for (int i = 0; i < arrary.Length; i++)
139                     {
140                         dictonary.Add(new DictionaryEntry(arrary[i].FullName, arrary[i].LastWriteTime));
141                     }
142                 }
143                 return dictonary;
144             }
145             catch (Exception ex)
146             {
147                 Logger.Error("获取文件夹下的所有文件" + ex.Message);
148                 return null;
149             }
150         }
151         #endregion
152 
153         #region DataTable分页
154         /// <summary>
155         /// DataTable分页
156         /// </summary>
157         /// <param name="dt">DataTable</param>
158         /// <param name="PageIndex">页索引,注意：从1开始</param>
159         /// <param name="PageSize">每页大小</param>
160         /// <returns>分好页的DataTable数据</returns>              第1页        每页10条
161         public static System.Data.DataTable GetPagedTable(System.Data.DataTable dt, int PageIndex, int PageSize)
162         {
163             if (PageIndex == 0) { return dt; }
164             System.Data.DataTable newdt = dt.Copy();
165             newdt.Clear();
166             int rowbegin = (PageIndex - 1) * PageSize;
167             int rowend = PageIndex * PageSize;
168 
169             if (rowbegin >= dt.Rows.Count)
170             { return newdt; }
171 
172             if (rowend > dt.Rows.Count)
173             { rowend = dt.Rows.Count; }
174             for (int i = rowbegin; i <= rowend - 1; i++)
175             {
176                 DataRow newdr = newdt.NewRow();
177                 DataRow dr = dt.Rows[i];
178                 foreach (DataColumn column in dt.Columns)
179                 {
180                     newdr[column.ColumnName] = dr[column.ColumnName];
181                 }
182                 newdt.Rows.Add(newdr);
183             }
184             return newdt;
185         }
186 
187         /// <summary>
188         /// 返回分页的页数
189         /// </summary>
190         /// <param name="count">总条数</param>
191         /// <param name="pageye">每页显示多少条</param>
192         /// <returns>如果 结尾为0：则返回1</returns>
193         public static int Pagount(int count, int pageye)
194         {
195             int page = 0;
196             int sesepage = pageye;
197             if (count % sesepage == 0) { page = count / sesepage; }
198             else { page = (count / sesepage) + 1; }
199             if (page == 0) { page += 1; }
200             return page;
201         }
202         #endregion
203 
204         #region Datatable转Execl
205         /// <summary>
206         /// 把Datatable中的数据保存成指定的Excel文件
207         /// </summary>
208         /// <param name="SourceTable">需要转成execl的DateTable</param>
209         /// <param name="FileName">详细的文件路径带文件名与格式</param>
210         public static void RenderDataTableToExcel(System.Data.DataTable SourceTable, string FileName)
211         {
212             Logger.Info("进入方法RenderDataTableToExcel 文件名：" + FileName);
213             HSSFWorkbook workbook = new HSSFWorkbook();
214             MemoryStream _ms = new MemoryStream();
215             // 创建Excel文件的Sheet
216             Sheet sheet = workbook.CreateSheet("Sheet1");
217             sheet.SetColumnWidth(0, 30 * 256); //设置单元格的宽度
218             sheet.SetColumnWidth(1, 20 * 256);//设置单元格的宽度
219             sheet.SetColumnWidth(2, 20 * 256);//设置单元格的宽度
220             // 创建行
221             Row headerRow = sheet.CreateRow(0);
222             // 把Datatable中的列名添加Sheet中第一列中作为表头
223             foreach (DataColumn column in SourceTable.Columns)
224                 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
225             int rowIndex = 1;
226             // 循环Datatable中的行和列添加数据到Excel中
227             foreach (DataRow row in SourceTable.Rows)
228             {
229                 Row dataRow = sheet.CreateRow(rowIndex);
230                 foreach (DataColumn column in SourceTable.Columns)
231                 {
232                     dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
233                 }
234                 rowIndex++;
235             }
236             try
237             {
238                 MemoryStream ms = _ms as MemoryStream;
239                 workbook.Write(ms);
240                 _ms.Flush();
241                 _ms.Position = 0;
242                 FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.ReadWrite);
243                 byte[] data = ms.ToArray();
244                 fs.Write(data, 0, data.Length);
245                 fs.Flush();
246                 fs.Close();
247                 ms.Flush();
248                 ms.Close();
249                 data = null;
250                 ms = null;
251                 fs = null;
252             }
253             catch (Exception ex)
254             {
255                 Logger.Error("把Datatable中的数据保存成指定的Excel文件:" + ex.Message);
256             }
257         }
258         #endregion