Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
        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
            {
            }
        }
Exemplo n.º 4
0
        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;
            }
        }
Exemplo n.º 5
0
        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); //返回文件保存的路径
        }