public bool DataTableToExcelByMemberReport(DataTable dt, string outFileName, string[] hearArr, int SyRegCount, int PhoneRegCount, int SiteRegCount) { //定义循环中要使用的变量 BinaryWriter bw = null; HttpResponse contextResponse = null; MemoryStream stream = null; //ICSharpCode.SharpZipLib.Zip.ZipOutputStream s = null; int dvRowStart; if (dt.Rows.Count < 0) { return(false); } int sheetRows = 65535; //设置Sheet的行数,此为最大上限,本来是65536,因表头要占去一行 int sheetCount = (dt.Rows.Count - 1) / sheetRows + 1; //计算Sheet数 try { book = new Workbook(); book.Worksheets.Clear(); sheet = book.Worksheets.Add("统计信息"); sheet = book.Worksheets["统计信息"]; if (SiteRegCount != -1) { sheet.Cells[0, 0].PutValue("网站注册"); sheet.Cells[0, 1].PutValue(SiteRegCount.ToString()); } if (PhoneRegCount != -1) { sheet.Cells[0, 2].PutValue("电话注册"); sheet.Cells[0, 3].PutValue(PhoneRegCount.ToString()); } if (SyRegCount != -1) { sheet.Cells[0, 4].PutValue("转入新系统会员"); sheet.Cells[0, 5].PutValue(SyRegCount.ToString()); } int regSum = 0; if (SyRegCount != -1 || PhoneRegCount != -1 || SiteRegCount != -1) { SyRegCount = SyRegCount == -1 ? 0 : SyRegCount; PhoneRegCount = PhoneRegCount == -1 ? 0 : PhoneRegCount; SiteRegCount = SiteRegCount == -1 ? 0 : SiteRegCount; regSum = SiteRegCount + PhoneRegCount + SyRegCount; sheet.Cells[0, 6].PutValue("共注册会员:" + regSum.ToString()); } // sheet.Cells[0, 7].PutValue(regSum.ToString()); for (int i = 1; i <= sheetCount; i++) { sheet = book.Worksheets.Add(i.ToString()); sheet = book.Worksheets[i]; //计算起始行 if (hearArr != null && hearArr.Length > 0) { AddHeader(hearArr); } else { AddHeader(dt); } dvRowStart = (i - 1) * sheetRows; //dvRowEnd = dvRowStart + sheetRows - 1; //if (dvRowEnd > dt.Rows.Count - 1) //{ // dvRowEnd = dt.Rows.Count - 1; //} for (int r = 1; r < (sheetRows - 1); r++) { if (dvRowStart > dt.Rows.Count - 1) { break; } for (int c = 0; c < dt.Columns.Count; c++) { sheet.Cells[r, c].PutValue(dt.Rows[dvRowStart][c].ToString()); } dvRowStart++; } } sheet.AutoFitColumns(); //stream = book.SaveToStream(); Logger.Error("生成EXECL结束" + DateTime.Now.ToString()); #region 压缩 Logger.Error("压缩开始" + DateTime.Now.ToString()); string path = HttpContext.Current.Server.MapPath("/UpLoadFile/excel/temp/"); string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xls"; string rarName = fileName.Replace(".xls", ".rar"); string rarFileFullPath = path + rarName; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } book.Save(path + fileName); RARHelper.WinRAR(path, rarName, fileName); File.Delete(path + fileName); FileStream fileStream = new FileStream(rarFileFullPath, FileMode.Open); long fileSize = fileStream.Length; byte[] fileBuffer = new byte[fileSize]; fileStream.Read(fileBuffer, 0, (int)fileSize); fileStream.Close(); File.Delete(rarFileFullPath); #endregion Logger.Error("压缩结束" + DateTime.Now.ToString()); contextResponse = HttpContext.Current.Response; contextResponse.Clear(); contextResponse.Buffer = true; contextResponse.Charset = "GB2312"; //设置了类型为中文防止乱码的出现 contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", "" + outFileName + ".rar")); //定义输出文件和文件名 contextResponse.ContentEncoding = Encoding.Default; //contextResponse.ContentType = "application/octet-stream"; //设置输出文件类型为excel文件。 //s = new ICSharpCode.SharpZipLib.Zip.ZipOutputStream(contextResponse.OutputStream); //ICSharpCode.SharpZipLib.Zip.ZipEntry entry = new ICSharpCode.SharpZipLib.Zip.ZipEntry("" + outFileName + ".xls"); //s.PutNextEntry(entry); bw = new BinaryWriter(contextResponse.OutputStream); bw.Write(fileBuffer); } catch (Exception ex) { throw ex; return(false); } finally { bw.Flush(); bw.Close(); //s.Flush(); // s.Close(); contextResponse.Flush(); } return(true); }
public bool DataTableToExcel(DataTable dt, string outFileName, string[] hearArr) { //定义循环中要使用的变量 BinaryWriter bw = null; HttpResponse contextResponse = null; MemoryStream stream = null; //ICSharpCode.SharpZipLib.Zip.ZipOutputStream s = null; int dvRowStart; if (dt.Rows.Count < 0) { return(false); } int sheetRows = 65535; //设置Sheet的行数,此为最大上限,本来是65536,因表头要占去一行 int sheetCount = (dt.Rows.Count - 1) / sheetRows + 1; //计算Sheet数 try { book = new Workbook(); book.Worksheets.Clear(); for (int i = 0; i < sheetCount; i++) { sheet = book.Worksheets.Add(i.ToString()); sheet = book.Worksheets[i]; //计算起始行 if (hearArr != null && hearArr.Length > 0) { AddHeader(hearArr); } else { AddHeader(dt); } dvRowStart = i * sheetRows; //dvRowEnd = dvRowStart + sheetRows - 1; //if (dvRowEnd > dt.Rows.Count - 1) //{ // dvRowEnd = dt.Rows.Count - 1; //} for (int r = 1; r < (sheetRows - 1); r++) { if (dvRowStart > dt.Rows.Count - 1) { break; } for (int c = 0; c < dt.Columns.Count; c++) { //sheet.Cells[r, c].PutValue(dt.Rows[dvRowStart][c].ToString()); if (dt.Columns[c].ColumnName.ToLower() == "ordercode" || dt.Columns[c].ColumnName.ToLower() == "订单号" || dt.Columns[c].ColumnName.ToLower() == "订单编码" || dt.Columns[c].ColumnName.ToLower() == "订单编号") { sheet.Cells[r, c].PutValue("\"" + dt.Rows[dvRowStart][c].ToString() + "\""); } if (dt.Columns[c].ColumnName.ToLower().IndexOf("数") > -1 || dt.Columns[c].ColumnName.ToLower().IndexOf("金币") > -1) { sheet.Cells[r, c].PutValue(dt.Rows[dvRowStart][c]); } else { sheet.Cells[r, c].PutValue(dt.Rows[dvRowStart][c].ToString()); } } dvRowStart++; } } sheet.AutoFitColumns(); //stream = book.SaveToStream(); #region 压缩 string path = HttpContext.Current.Server.MapPath("/UpLoadFile/excel/temp/"); string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xls"; string rarName = fileName.Replace(".xls", ".rar"); string rarFileFullPath = path + rarName; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } book.Save(path + fileName); RARHelper.WinRAR(path, rarName, fileName); File.Delete(path + fileName); FileStream fileStream = new FileStream(rarFileFullPath, FileMode.Open); long fileSize = fileStream.Length; byte[] fileBuffer = new byte[fileSize]; fileStream.Read(fileBuffer, 0, (int)fileSize); fileStream.Close(); File.Delete(rarFileFullPath); #endregion contextResponse = HttpContext.Current.Response; contextResponse.Clear(); contextResponse.Buffer = true; contextResponse.Charset = "GB2312"; //设置了类型为中文防止乱码的出现 contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", "" + outFileName + ".rar")); //定义输出文件和文件名 contextResponse.ContentEncoding = Encoding.Default; //contextResponse.ContentType = "application/octet-stream"; //设置输出文件类型为excel文件。 //s = new ICSharpCode.SharpZipLib.Zip.ZipOutputStream(contextResponse.OutputStream); //ICSharpCode.SharpZipLib.Zip.ZipEntry entry = new ICSharpCode.SharpZipLib.Zip.ZipEntry("" + outFileName + ".xls"); //s.PutNextEntry(entry); bw = new BinaryWriter(contextResponse.OutputStream); bw.Write(fileBuffer); } catch (Exception ex) { throw ex; // return false; } finally { bw.Flush(); bw.Close(); //s.Flush(); // s.Close(); contextResponse.Flush(); } return(true); }
public string DataTableToExcelByMemberReport(DataTable dt, string outFileName, string[] hearArr, int SyRegCount, int PhoneRegCount, int SiteRegCount, bool isoutLine) { //定义循环中要使用的变量 BinaryWriter bw = null; HttpResponse contextResponse = null; MemoryStream stream = null; //ICSharpCode.SharpZipLib.Zip.ZipOutputStream s = null; int dvRowStart; if (dt.Rows.Count < 0) { return(""); } int sheetRows = 65535; //设置Sheet的行数,此为最大上限,本来是65536,因表头要占去一行 int sheetCount = (dt.Rows.Count - 1) / sheetRows + 1; //计算Sheet数 try { book = new Workbook(); book.Worksheets.Clear(); sheet = book.Worksheets.Add("统计信息"); sheet = book.Worksheets["统计信息"]; if (SiteRegCount != -1) { sheet.Cells[0, 0].PutValue("网站注册"); sheet.Cells[0, 1].PutValue(SiteRegCount.ToString()); } if (PhoneRegCount != -1) { sheet.Cells[0, 2].PutValue("电话注册"); sheet.Cells[0, 3].PutValue(PhoneRegCount.ToString()); } if (SyRegCount != -1) { sheet.Cells[0, 4].PutValue("转入新系统会员"); sheet.Cells[0, 5].PutValue(SyRegCount.ToString()); } int regSum = 0; if (SyRegCount != -1 || PhoneRegCount != -1 || SiteRegCount != -1) { SyRegCount = SyRegCount == -1 ? 0 : SyRegCount; PhoneRegCount = PhoneRegCount == -1 ? 0 : PhoneRegCount; SiteRegCount = SiteRegCount == -1 ? 0 : SiteRegCount; regSum = SiteRegCount + PhoneRegCount + SyRegCount; sheet.Cells[0, 6].PutValue("共注册会员:" + regSum.ToString()); } // sheet.Cells[0, 7].PutValue(regSum.ToString()); for (int i = 1; i <= sheetCount; i++) { sheet = book.Worksheets.Add(i.ToString()); sheet = book.Worksheets[i]; //计算起始行 if (hearArr != null && hearArr.Length > 0) { AddHeader(hearArr); } else { AddHeader(dt); } dvRowStart = (i - 1) * sheetRows; //dvRowEnd = dvRowStart + sheetRows - 1; //if (dvRowEnd > dt.Rows.Count - 1) //{ // dvRowEnd = dt.Rows.Count - 1; //} for (int r = 1; r < (sheetRows - 1); r++) { if (dvRowStart > dt.Rows.Count - 1) { break; } for (int c = 0; c < dt.Columns.Count; c++) { sheet.Cells[r, c].PutValue(dt.Rows[dvRowStart][c].ToString()); } dvRowStart++; } } sheet.AutoFitColumns(); string relativePath = "/UpLoadFile/excel/temp/"; string path = ServerPath + "/UpLoadFile/excel/temp/"; string fileName = outFileName + ".xls"; string rarName = fileName.Replace(".xls", ".rar"); string rarFileFullPath = path + rarName; relativePath = relativePath + rarName; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } book.Save(path + fileName); RARHelper.WinRAR(path, rarName, fileName); File.Delete(path + fileName); return(relativePath); } catch (Exception ex) { Logger.Error(ex.ToString()); return(""); } finally { } }
public string DataTableToExcel(DataTable dt, string outFileName, string[] hearArr, bool isOutLine) { int dvRowStart; if (dt.Rows.Count < 0) { return(""); } int sheetRows = 65535; //设置Sheet的行数,此为最大上限,本来是65536,因表头要占去一行 int sheetCount = (dt.Rows.Count - 1) / sheetRows + 1; //计算Sheet数 try { book = new Workbook(); book.Worksheets.Clear(); //book.ConvertNumericData = false; for (int i = 0; i < sheetCount; i++) { sheet = book.Worksheets.Add(i.ToString()); sheet = book.Worksheets[i]; //计算起始行 if (hearArr != null && hearArr.Length > 0) { AddHeader(hearArr); } else { AddHeader(dt); } dvRowStart = i * sheetRows; for (int r = 1; r < (sheetRows - 1); r++) { if (dvRowStart > dt.Rows.Count - 1) { break; } for (int c = 0; c < dt.Columns.Count; c++) { if (dt.Columns[c].ColumnName.ToLower() == "ordercode" || dt.Columns[c].ColumnName.ToLower() == "订单号" || dt.Columns[c].ColumnName.ToLower() == "订单编码" || dt.Columns[c].ColumnName.ToLower() == "订单编号") { sheet.Cells[r, c].PutValue("\"" + dt.Rows[dvRowStart][c].ToString() + "\""); } if (dt.Columns[c].ColumnName.ToLower().IndexOf("数") > -1 || dt.Columns[c].ColumnName.ToLower().IndexOf("金币") > -1) { sheet.Cells[r, c].PutValue(dt.Rows[dvRowStart][c]); } else { sheet.Cells[r, c].PutValue(dt.Rows[dvRowStart][c].ToString()); } } dvRowStart++; } } sheet.AutoFitColumns(); #region 压缩 string relativePath = "/UpLoadFile/excel/temp/"; string path = ServerPath + "/UpLoadFile/excel/temp/"; string fileName = outFileName + ".xls"; string rarName = fileName.Replace(".xls", ".rar"); string rarFileFullPath = path + rarName; relativePath = relativePath + rarName; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } book.Save(path + fileName); RARHelper.WinRAR(path, rarName, fileName); File.Delete(path + fileName); return(relativePath); //返回文件保存的路径 #endregion } catch (Exception ex) { throw ex; } }
public string GetDataTableToExcelByMemberReportPath(DataTable dt, string outFileName, string[] hearArr, int SyRegCount, int PhoneRegCount, int SiteRegCount) { string relativePath = ""; int dvRowStart; if (dt.Rows.Count < 0) { return(relativePath); } int sheetRows = 65535; //设置Sheet的行数,此为最大上限,本来是65536,因表头要占去一行 int sheetCount = (dt.Rows.Count - 1) / sheetRows + 1; //计算Sheet数 try { book = new Workbook(); book.Worksheets.Clear(); sheet = book.Worksheets.Add("统计信息"); sheet = book.Worksheets["统计信息"]; if (SiteRegCount != -1) { sheet.Cells[0, 0].PutValue("网站注册"); sheet.Cells[0, 1].PutValue(SiteRegCount.ToString()); } if (PhoneRegCount != -1) { sheet.Cells[0, 2].PutValue("电话注册"); sheet.Cells[0, 3].PutValue(PhoneRegCount.ToString()); } if (SyRegCount != -1) { sheet.Cells[0, 4].PutValue("转入新系统会员"); sheet.Cells[0, 5].PutValue(SyRegCount.ToString()); } int regSum = 0; if (SyRegCount != -1 || PhoneRegCount != -1 || SiteRegCount != -1) { SyRegCount = SyRegCount == -1 ? 0 : SyRegCount; PhoneRegCount = PhoneRegCount == -1 ? 0 : PhoneRegCount; SiteRegCount = SiteRegCount == -1 ? 0 : SiteRegCount; regSum = SiteRegCount + PhoneRegCount + SyRegCount; sheet.Cells[0, 6].PutValue("共注册会员:" + regSum.ToString()); } for (int i = 1; i <= sheetCount; i++) { sheet = book.Worksheets.Add(i.ToString()); sheet = book.Worksheets[i]; //计算起始行 if (hearArr != null && hearArr.Length > 0) { AddHeader(hearArr); } else { AddHeader(dt); } dvRowStart = (i - 1) * sheetRows; for (int r = 1; r < (sheetRows - 1); r++) { if (dvRowStart > dt.Rows.Count - 1) { break; } for (int c = 0; c < dt.Columns.Count; c++) { if (dt.Columns[c].ColumnName.ToLower() == "ordercode" || dt.Columns[c].ColumnName.ToLower() == "订单号" || dt.Columns[c].ColumnName.ToLower() == "订单编码" || dt.Columns[c].ColumnName.ToLower() == "订单编号") { sheet.Cells[r, c].PutValue("\"" + dt.Rows[dvRowStart][c].ToString() + "\""); } if (dt.Columns[c].ColumnName.ToLower().IndexOf("数") > -1 || dt.Columns[c].ColumnName.ToLower().IndexOf("金币") > -1) { sheet.Cells[r, c].PutValue(dt.Rows[dvRowStart][c]); } else { sheet.Cells[r, c].PutValue(dt.Rows[dvRowStart][c].ToString()); } } dvRowStart++; } } sheet.AutoFitColumns(); Logger.Info("生成EXECL结束" + DateTime.Now.ToString()); relativePath = "/UpLoadFile/excel/temp/"; string path = ServerPath + "/UpLoadFile/excel/temp/"; string fileName = outFileName + ".xls"; string rarName = fileName.Replace(".xls", ".rar"); string rarFileFullPath = path + rarName; relativePath = relativePath + rarName; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } book.Save(path + fileName); RARHelper.WinRAR(path, rarName, fileName); File.Delete(path + fileName); Logger.Info("压缩结束" + DateTime.Now.ToString()); } catch (Exception ex) { throw ex; } finally { } return(relativePath); //返回文件保存的路径 }