Exemplo n.º 1
0
 /// <summary>
 /// 设置单元格内容
 /// </summary>
 /// <param name="row">单元格行标(从0开始,下同)</param>
 /// <param name="col">单元格列标</param>
 /// <param name="o">写入内容</param>
 public void SetValue(int r, int c, object o)
 {
     if (o != null)
     {
         if (r <= osheet.LastRowNum)
         {
             IRow row = osheet.GetRow(r);
             if (row == null)
             {
                 row = osheet.CreateRow(r);
                 row.HeightInPoints = 14;
             }
             if (c <= row.LastCellNum)
             {
                 ICell cell = row.GetCell(c);
                 cell.SetCellValue(o.ToString());
             }
             else
             {
                 for (int j = row.LastCellNum; j < c; j++)
                 {
                     row.CreateCell(j + 1);
                     ICell      cell22 = row.GetCell(j + 1);
                     ICellStyle style  = obook.CreateCellStyle();
                     cell22.CellStyle = style;
                 }
                 ICell cell = row.GetCell(c);
                 cell.SetCellValue(o.ToString());
             }
         }
         else
         {
             for (int i = osheet.LastRowNum; i < r; i++)
             {
                 IRow row22 = osheet.CreateRow(i + 1);
                 row22.HeightInPoints = 14;
             }
             IRow row = osheet.GetRow(r);
             for (int j = row.LastCellNum; j < c; j++)
             {
                 row.CreateCell(j + 1);;
                 ICell      cell22 = row.GetCell(j + 1);
                 ICellStyle style  = obook.CreateCellStyle();
                 cell22.CellStyle = style;
             }
             ICell cell = row.GetCell(c);
             cell.SetCellValue(o.ToString());
         }
     }
 }
Exemplo n.º 2
0
        public ICellStyle Cellstyle()
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            IFont font = book.CreateFont();

            font.FontName = "宋体";
            //font.setFontHeightInPoints((short)10);
            font.FontHeightInPoints = 10;
            // 普通单元格样式
            ICellStyle style = book.CreateCellStyle();

            style.SetFont(font);
            //style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
            //style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
            //style.setWrapText(true);
            //style.setLeftBorderColor(HSSFColor.BLACK.index);
            //style.setBorderLeft((short)1);
            //style.setRightBorderColor(HSSFColor.BLACK.index);
            //style.setBorderRight((short)1);
            //style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
            //style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色.
            //style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色.

            return(style);
        }
Exemplo n.º 3
0
            /// <summary>
            /// Init
            /// </summary>
            /// <param name="Response"></param>
            public void Init(HttpResponseBase Response)
            {
                this.Response = Response;
                if (Book == null)
                {
                    Book = new NPOI.HSSF.UserModel.HSSFWorkbook();

                    this.DataFormat                   = Book.CreateDataFormat();
                    this.DateCellStyle                = Book.CreateCellStyle();
                    this.DateTimeCellStyle            = Book.CreateCellStyle();
                    this.TimeCellStyle                = Book.CreateCellStyle();
                    this.DateCellStyle.DataFormat     = DataFormat.GetFormat("yyyy/MM/dd");
                    this.DateTimeCellStyle.DataFormat = DataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");
                    this.TimeCellStyle.DataFormat     = DataFormat.GetFormat("HH:mm:ss");

                    DefaultSheet = Book.CreateSheet("Sheet1");
                }
            }
Exemplo n.º 4
0
        public FileResult Export(Pagination rq, AdminCredential User)
        {
            #region 报表导出
            var report = service.Get(rq.vid).Data;
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            ICellStyle style = workbook.CreateCellStyle();
            style.FillForegroundColor = (short)24;// NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
            style.FillPattern         = FillPattern.SolidForeground;
            style.BorderTop           = BorderStyle.Thin;
            style.BorderLeft          = BorderStyle.Thin;
            style.BorderRight         = BorderStyle.Thin;
            style.BorderBottom        = BorderStyle.Thin;
            style.Alignment           = HorizontalAlignment.Center;
            style.VerticalAlignment   = VerticalAlignment.Center;

            IFont font = workbook.CreateFont();       //创建一个字体样式对象
            font.FontHeightInPoints = 10;             //字体大小
            font.FontName           = "宋体";           //和excel里面的字体对应
            font.Boldweight         = short.MaxValue; //字体加粗
            font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
            style.SetFont(font);                      //将字体样式赋给样式对象

            NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet(report.Name);
            NPOI.SS.UserModel.IRow   row1   = sheet1.CreateRow(0);
            row1.Height = 430;

            var FieldList = JsonConvert.DeserializeObject <List <ViewFieldModel> >(service.GetReportColumns(rq.vid.Value));
            int i         = 0;
            foreach (var item in FieldList)
            {
                ICell cell = row1.CreateCell(i);
                cell.SetCellValue(item.Title);
                cell.CellStyle = style;
                i++;
            }
            rq.Filter = Session[rq.vid.ToString() + "Filter"].ToString();
            DataTable dt = service.GetExportReportData(rq, User);
            //将数据逐步写入sheet1各个行
            for (int z = 0; z < dt.Rows.Count; z++)
            {
                var row = dt.Rows[z];
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(z + 1);
                int c = 0;
                foreach (var item in FieldList)
                {
                    rowtemp.CreateCell(c++).SetCellValue(row[item.Field].ToString());
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            workbook.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return(File(ms, "application/vnd.ms-excel", report.Name + ".xls"));

            #endregion
        }
Exemplo n.º 5
0
        void CreateExcel(object sender)
        {
            try
            {
                string filePath = Application.StartupPath + "\\DailyPOSSales.xls";

                if (System.IO.File.Exists(filePath))
                {
                    File.Delete(filePath);
                }

                NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();

                NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Daily POS Sales");

                //第1行
                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);

                ICell fistCell = row.CreateCell(0);

                fistCell.SetCellValue("Daily POS Sales");

                ICellStyle firstRowStyle = workbook.CreateCellStyle();
                firstRowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;

                IFont firstRowFont = workbook.CreateFont();
                firstRowFont.FontName = "Tahoma";
                firstRowFont.FontHeightInPoints = 20;
                firstRowFont.Color = HSSFColor.Blue.Index;
                firstRowFont.Boldweight = short.MaxValue;

                firstRowStyle.SetFont(firstRowFont);

                fistCell.CellStyle = firstRowStyle;
                row.HeightInPoints = 25;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 15));

                using (FileStream fs = File.OpenWrite(filePath))
                {
                    workbook.Write(fs);
                }
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            finally
            {
                //this.Invoke(new Action(() =>
                //{
                //    (sender as Button).Enabled = true;
                //}));
            }
        }
Exemplo n.º 6
0
        public static byte[] getExcelData(DataTable t
                                          , GridColumns cols)
        {
            var workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();

            var sheet = workbook.CreateSheet("data");

            var rowIndex = 0;
            var row      = sheet.CreateRow(rowIndex);
            var font1    = workbook.CreateFont();

            font1.IsBold = true;



            for (int i = 0; i < cols.Count; i++)
            {
                var col = cols[i];

                var cell = row.CreateCell(i);
                cell.SetCellValue(col.title);
                cell.CellStyle = workbook.CreateCellStyle();
                cell.CellStyle.SetFont(font1);

                sheet.SetColumnWidth(i, 5500);
            }



            foreach (DataRow r in t.Rows)
            {
                rowIndex++;

                row = sheet.CreateRow(rowIndex);
                for (int iCol = 0; iCol < cols.Count; iCol++)
                {
                    var col = cols[iCol];

                    var cell = row.CreateCell(iCol);
                    cell.SetCellValue(r[col.datafield].ToString());
                    //cell.CellStyle.GetFont(workbook).IsBold = false;
                }
            }


            using (var fileData = new System.IO.MemoryStream())
            {
                workbook.Write(fileData);

                return(g.ConvertStreamToByteArray(fileData));
            }
        }
Exemplo n.º 7
0
        public static MemoryStream ExportAuditLog(int zoneTableId, DateTime?startTime, DateTime?endTime, string logType, string detail)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            //获取list数据
            //List<AuditLogEntity> list = new List<AuditLogEntity>();
            //for (int i = 0; i < 50; i++)
            //{
            //    AuditLogEntity en = new AuditLogEntity();
            //    en.ID = i;
            //    en.LogType = "App";
            //    en.Detail = "detail" + i;
            //    en.LogTime = DateTime.Now;
            //    en.LogOperator = "Michael.he";

            //    list.Add(en);
            //}

            List <AuditLogEntity> list = AuditLogAccess.GetList(zoneTableId, startTime, endTime, logType, detail);

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("Log Type");
            row1.CreateCell(1).SetCellValue("Detail");
            row1.CreateCell(2).SetCellValue("Log Time");
            row1.CreateCell(3).SetCellValue("Log Operator");
            var notesStyle = book.CreateCellStyle();

            notesStyle.WrapText = true;//设置换行这个要先设置
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < list.Count; i++)
            {
                var rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(list[i].LogType.ToString());
                rowtemp.CreateCell(1).SetCellValue(list[i].Detail.Replace("<br />", "\n"));
                rowtemp.Cells[1].CellStyle = notesStyle;
                rowtemp.CreateCell(2).SetCellValue(list[i].LogTime.ToString(CultureInfo.InvariantCulture));
                rowtemp.CreateCell(3).SetCellValue(list[i].LogOperator);
            }

            sheet1.SetColumnWidth(1, 160 * 256);
            sheet1.SetColumnWidth(2, 20 * 256);
            // 写入到客户端
            var ms = new MemoryStream();

            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);

            return(ms);
        }
Exemplo n.º 8
0
        protected void btnExport_Click(object sender, EventArgs e)
        {
            DateTime start = Common.St.ToDateTime(txtStart.Value + " 00:00:00");
            DateTime end   = Common.St.ToDateTime(txtEnd.Value + " 23:59:59");
            var      list  = DAL.WorkPlanRule.Get(start, end);

            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_2.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
            NPOI.SS.UserModel.ISheet         sheet = book.GetSheet("周上线记录");

            NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
            style.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            style.WrapText          = true;
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

            NPOI.SS.UserModel.IRow  row  = sheet.GetRow(0);
            NPOI.SS.UserModel.ICell cell = row.GetCell(0);
            cell.SetCellValue("技术测试周上线记录(" + start.ToString("yyyy年MM月dd日") + "-" + end.ToString("yyyy年MM月dd日") + ")");
            // 内容
            int i = 2;

            foreach (var o in list)
            {
                NPOI.SS.UserModel.IRow  row2  = sheet.CreateRow(i);
                NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0);
                cell0.CellStyle = style;
                cell0.SetCellValue(o.SheepNo);
                NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1);
                cell1.CellStyle = style;
                cell1.SetCellValue(o.Project.Name);
                NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2);
                cell2.CellStyle = style;
                cell2.SetCellValue(Common.St.ToDateTimeString(o.PublishTime, "yyyy-MM-dd"));
                i++;
            }

            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("每周项目上线记录", System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }
Exemplo n.º 9
0
        ///// <summary>
        ///// 樣式的值是否改變
        ///// </summary>
        //internal bool StyleValueIsChange
        //{
        //    get { return _styleValueIsChange; }
        //    set{_styleValueIsChange =value;}
        //}

        #endregion

        #region 方法

        internal ICellStyle GetCellStyle(NPOI.HSSF.UserModel.HSSFWorkbook workBook)
        {
            if (workBook == null)
            {
                throw new ArgumentNullException("workBook參數不能為NULL。");
            }

            if (_cellStyle == null || _styleValueIsChange)
            {
                _cellStyle = workBook.CreateCellStyle();
                IFont font = workBook.FindFont((short)_fontBold, _fontColor, (short)(_fontSize * 20), _fontName, _isItalic, _isStrikeout, _fontTypeOffset, (byte)_underline);
                if (font == null)
                {
                    font                    = workBook.CreateFont();
                    font.FontName           = this._fontName;
                    font.Boldweight         = (short)this._fontBold;
                    font.FontHeightInPoints = (short)this._fontSize;
                    font.Underline          = (byte)this.UnderLine;
                    font.IsItalic           = _isItalic;
                    font.Color              = _fontColor;
                    font.IsStrikeout        = _isStrikeout;
                    font.TypeOffset         = _fontTypeOffset;
                }
                _cellStyle.SetFont(font);
                IDataFormat format = workBook.CreateDataFormat();
                _cellStyle.BorderBottom      = this._borderBottom;
                _cellStyle.BorderLeft        = this._borderLeft;
                _cellStyle.BorderRight       = this._borderRight;
                _cellStyle.BorderTop         = this._borderTop;
                _cellStyle.Alignment         = this._alignment;
                _cellStyle.VerticalAlignment = this.VerticalAlignment;
                if (!string.IsNullOrEmpty(_dataFormart))
                {
                    _cellStyle.DataFormat = format.GetFormat(this._dataFormart);
                }
                _cellStyle.WrapText = this._warpText;
                _styleValueIsChange = false;
            }
            return(_cellStyle);
        }
Exemplo n.º 10
0
        protected void btnExport_Click(object sender, EventArgs e)
        {
            var list = GetList().AsEnumerable().Select(a => new { Name = GetBugzillaUserName(a.Field <int>("reporter")), CC = a.Field <Int64>("cc") }).Where(a => a.Name != "");

            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_5.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
            NPOI.SS.UserModel.ISheet         sheet = book.GetSheet("bug统计");

            NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
            style.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            style.WrapText          = true;
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

            // 内容
            int i = 2;

            foreach (var o in list)
            {
                NPOI.SS.UserModel.IRow  row2  = sheet.CreateRow(i);
                NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0);
                cell0.CellStyle = style;
                cell0.SetCellValue(o.Name);
                NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1);
                cell1.CellStyle = style;
                cell1.SetCellValue(o.CC.ToString());
                i++;
            }

            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("Bug数统计", System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }
Exemplo n.º 11
0
        /// <summary>
        /// 将DataGridView中的数据,从起始列开始,原样导出到EXCEL中
        /// </summary>
        /// <param name="mView">源DataGridView</param>
        /// <param name="startIndex">起始列序号</param>
        /// <returns>0-成功,1-失败</returns>
        static public int Deport(System.Windows.Forms.DataGridView mView, int startIndex)
        {
            try
            {
                IWorkbook  mWorkbook  = new NPOI.HSSF.UserModel.HSSFWorkbook();
                ISheet     mSheet     = mWorkbook.CreateSheet("Sheet1");
                ICellStyle styleRight = mWorkbook.CreateCellStyle();
                //ICellStyle NumberStyle = mWorkbook.CreateCellStyle();
                //IDataFormat format = mWorkbook.CreateDataFormat();
                styleRight.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
                //NumberStyle.DataFormat = format.GetFormat("0.00");
                ICell mCell;
                int   i, j;
                int   x = 0;
                //DataGridView mView = dgvReport;

                //添加标头
                IRow mRow = mSheet.CreateRow(0);
                for (i = startIndex; i <= mView.Columns.Count - 1; i++)
                {
                    if (mView.Columns[i].Visible == true)
                    {
                        mCell = mRow.CreateCell(x);
                        x++;
                        mCell.SetCellValue(mView.Columns[i].HeaderText);
                        mCell.CellStyle = styleRight;
                    }
                }
                //添加内容

                for (i = 1; i <= mView.RowCount; i++)
                {
                    mRow = mSheet.CreateRow(i);
                    x    = 0;
                    for (j = startIndex; j <= mView.Columns.Count - 1; j++)
                    {
                        if (mView.Columns[j].Visible == true)
                        {
                            mCell = mRow.CreateCell(x);
                            x++;
                            if (PubFunc.IsNumber(Convert.ToString(mView.Rows[i - 1].Cells[j].Value)) == true)
                            {
                                mCell.SetCellValue(Convert.ToDouble(mView.Rows[i - 1].Cells[j].Value));
                            }
                            else
                            {
                                mCell.SetCellValue(Convert.ToString(mView.Rows[i - 1].Cells[j].Value));
                            }
                            mCell.CellStyle = styleRight;
                        }
                    }
                }



                //创建文件
                SaveFileDialog mDialog = new SaveFileDialog();
                mDialog.AddExtension     = true;
                mDialog.DefaultExt       = "xls";
                mDialog.Filter           = "Excel Worksheets(*.xls)|*.xls";
                mDialog.InitialDirectory = System.Environment.CurrentDirectory;
                mDialog.FileName         = "导出数据-" + DateTime.Now.ToString("yyyyMMddhhmmss");
                if (mDialog.ShowDialog() == DialogResult.OK)
                {
                    Stream mFile = mDialog.OpenFile();
                    mWorkbook.Write(mFile);
                    mFile.Close();
                    MessageBox.Show("保存成功!", "提示");
                    return(0);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
            return(1);
        }
Exemplo n.º 12
0
        public byte[] exportToExcel(DataSet source)
        {
            var workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();

            var sheet = workbook.CreateSheet("data");

            var rowIndex = 0;
            var row      = sheet.CreateRow(rowIndex);
            var font1    = workbook.CreateFont();

            font1.IsBold = true;



            for (int i = 0; i < cols.Count; i++)
            {
                var col = cols[i];

                var cell = row.CreateCell(i);
                cell.SetCellValue(col.title);
                cell.CellStyle = workbook.CreateCellStyle();
                cell.CellStyle.SetFont(font1);

                sheet.SetColumnWidth(i, 5500);
            }



            foreach (DataRow r in source.Tables[0].Rows)
            {
                rowIndex++;

                row = sheet.CreateRow(rowIndex);
                for (int iCol = 0; iCol < cols.Count; iCol++)
                {
                    var col = cols[iCol];

                    var cell = row.CreateCell(iCol);


                    string sVal = "";

                    if (r[col.datafield] is DateTime && col.Format.isEmpty() == false)
                    {
                        sVal = r.getDateFormat(col.datafield, col.Format);
                    }
                    else
                    {
                        sVal = r[col.datafield].ToString();
                    }


                    cell.SetCellValue(sVal);
                }
            }


            using (var fileData = new System.IO.MemoryStream())
            {
                workbook.Write(fileData);

                return(g.ConvertStreamToByteArray(fileData));
            }
        }
Exemplo n.º 13
0
        public static bool DataGridview2Sheet(System.Windows.Forms.DataGridView dataGridView1, string tableName)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         c  = wb.CreateSheet(tableName);

            List <DataGridViewColumn> ListColumns = new List <DataGridViewColumn>();

            foreach (DataGridViewColumn i in dataGridView1.Columns)
            {
                if (i.Visible == true)
                {
                    ListColumns.Add(i);
                }
            }

            if (dataGridView1.Rows.Count <= 0)
            {
                return(false);
            }

            foreach (DataGridViewColumn dc in ListColumns)
            {
                if (dc.Visible == false)
                {
                    continue;
                }
                if (dc.ValueType == typeof(int) || dc.ValueType == typeof(decimal) || dc.ValueType == typeof(double))
                {
                    c.SetColumnWidth(dc.Index, 10 * 256);
                }
                else
                {
                    c.SetColumnWidth(dc.Index, 20 * 256);
                }
            }


            #region 表头
            NPOI.SS.UserModel.IRow RowHeader = c.CreateRow(0);
            var FirstCell = RowHeader.CreateCell(0);
            FirstCell.SetCellValue(BugsBox.Pharmacy.AppClient.Common.AppClientContext.Config.Store.Name + tableName);
            NPOI.SS.UserModel.ICellStyle cellstyleHeader = wb.CreateCellStyle();
            cellstyleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            cellstyleHeader.BorderLeft   = NPOI.SS.UserModel.BorderStyle.THIN;
            cellstyleHeader.BorderRight  = NPOI.SS.UserModel.BorderStyle.THIN;
            cellstyleHeader.BorderTop    = NPOI.SS.UserModel.BorderStyle.THIN;

            NPOI.SS.UserModel.IFont CellFontHeader = wb.CreateFont();
            CellFontHeader.FontName           = "微软雅黑";
            CellFontHeader.FontHeightInPoints = 16;
            cellstyleHeader.SetFont(CellFontHeader);
            cellstyleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            FirstCell.CellStyle       = cellstyleHeader;

            c.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, ListColumns.Count - 1));
            #endregion

            #region 标题行 居中并且有框线
            NPOI.SS.UserModel.ICellStyle CellStyleTitles = wb.CreateCellStyle();
            CellStyleTitles.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            CellStyleTitles.BorderLeft   = NPOI.SS.UserModel.BorderStyle.THIN;
            CellStyleTitles.BorderRight  = NPOI.SS.UserModel.BorderStyle.THIN;
            CellStyleTitles.BorderTop    = NPOI.SS.UserModel.BorderStyle.THIN;
            CellStyleTitles.Alignment    = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            NPOI.SS.UserModel.IRow RowTitle = c.CreateRow(1);
            int cindex = 0;
            foreach (DataGridViewColumn hc in ListColumns)
            {
                if (!hc.Visible)
                {
                    continue;
                }
                NPOI.SS.UserModel.ICell cell = RowTitle.CreateCell(cindex);
                cindex++;
                cell.CellStyle = CellStyleTitles;
                if (!string.IsNullOrEmpty(hc.HeaderText))
                {
                    cell.SetCellValue(hc.HeaderText);
                }
            }
            #endregion

            #region 列表 有框线,默认左对齐
            NPOI.SS.UserModel.ICellStyle CellStyleLeftAlignmentCell = wb.CreateCellStyle();
            CellStyleLeftAlignmentCell.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            CellStyleLeftAlignmentCell.BorderLeft   = NPOI.SS.UserModel.BorderStyle.THIN;
            CellStyleLeftAlignmentCell.BorderRight  = NPOI.SS.UserModel.BorderStyle.THIN;
            CellStyleLeftAlignmentCell.BorderTop    = NPOI.SS.UserModel.BorderStyle.THIN;

            foreach (DataGridViewRow i in dataGridView1.Rows)
            {
                NPOI.SS.UserModel.IRow row = c.CreateRow(i.Index + 2);
                cindex = 0;
                foreach (DataGridViewCell col in i.Cells)
                {
                    if (!col.Visible)
                    {
                        continue;
                    }
                    NPOI.SS.UserModel.ICell xcell = row.CreateCell(cindex);
                    cindex++;

                    //设置居中对齐,如果是string则左对齐
                    if (col.ValueType == typeof(string) || col.ValueType == typeof(Guid))
                    {
                        xcell.CellStyle = CellStyleLeftAlignmentCell;//默认左对齐的风格
                    }
                    else
                    {
                        xcell.CellStyle = CellStyleTitles;//默认居中对齐的风格,与标题栏一致
                    }

                    if (col.Value == null)
                    {
                        continue;
                    }
                    if (col.ValueType == typeof(string))
                    {
                        xcell.SetCellValue(col.Value.ToString());
                    }
                    else
                    if (col.ValueType == typeof(decimal) || col.ValueType == typeof(int) || col.ValueType == typeof(double))
                    {
                        xcell.SetCellValue(double.Parse(col.Value.ToString()));
                    }
                    else
                    {
                        xcell.SetCellValue(col.Value.ToString());
                    }
                }
            }
            #endregion

            using (SaveFileDialog sfd = new SaveFileDialog())
            {
                sfd.Filter   = "Excel电子表格|*.xls";
                sfd.FileName = tableName + DateTime.Now.Ticks.ToString();
                if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    try
                    {
                        using (System.IO.FileStream fs = System.IO.File.OpenWrite(sfd.FileName))
                        {
                            wb.Write(fs);
                            MessageBox.Show("导出成功!");
                        }
                    }
                    catch (System.IO.IOException ex)
                    {
                        MessageBox.Show("导出失败!\n" + ex.Message);
                        return(false);
                    }
                }
            }
            return(true);
        }
Exemplo n.º 14
0
        static public int InvoiceDeport(System.Windows.Forms.DataGridView mView)
        {
            try
            {
                IWorkbook  mWorkbook  = new NPOI.HSSF.UserModel.HSSFWorkbook();
                ISheet     mSheet     = mWorkbook.CreateSheet("Sheet1");
                ICellStyle styleRight = mWorkbook.CreateCellStyle();
                //ICellStyle NumberStyle = mWorkbook.CreateCellStyle();
                //IDataFormat format = mWorkbook.CreateDataFormat();
                styleRight.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
                styleRight.WrapText          = true;
                ICell mCell;
                int   i;


                //添加标头
                IRow mRow = mSheet.CreateRow(0);

                int m = 0;
                mCell = mRow.CreateCell(m++);
                mCell.SetCellValue("公司名称");
                mSheet.SetDefaultColumnStyle(m, styleRight);//设置样式
                mCell = mRow.CreateCell(m++);
                mCell.SetCellValue("总金额");
                mSheet.SetDefaultColumnStyle(m, styleRight);
                mCell = mRow.CreateCell(m++);
                mCell.SetCellValue("发票类型");
                mSheet.SetDefaultColumnStyle(m, styleRight);
                mCell = mRow.CreateCell(m++);
                mCell.SetCellValue("开票日期");
                mSheet.SetDefaultColumnStyle(m, styleRight);
                mCell = mRow.CreateCell(m++);
                mCell.SetCellValue("商品名称");
                mSheet.SetDefaultColumnStyle(m, styleRight);
                mCell = mRow.CreateCell(m++);
                mCell.SetCellValue("单价");
                mSheet.SetDefaultColumnStyle(m, styleRight);
                mCell = mRow.CreateCell(m++);
                mCell.SetCellValue("数量");
                mSheet.SetDefaultColumnStyle(m, styleRight);
                mCell = mRow.CreateCell(m++);
                mCell.SetCellValue("金额");
                mSheet.SetDefaultColumnStyle(m, styleRight);
                //添加内容
                int mExcelRowCount = 1;
                for (i = 0; i <= mView.RowCount - 1; i++)
                {
                    if (Convert.ToBoolean(mView.Rows[i].Cells["ColCombine"].Value) == true)
                    {
                        mRow = mSheet.CreateRow(mExcelRowCount++);
                        string InvoiceContent = string.Empty;
                        //公司名称
                        mCell = mRow.CreateCell(0);
                        mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["customerNameDataGridViewTextBoxColumn"].Value));
                        //总金额
                        mCell = mRow.CreateCell(1);
                        mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceMoneyDataGridViewTextBoxColumn"].Value));
                        //发票类型
                        mCell = mRow.CreateCell(2);
                        mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceTypeDataGridViewTextBoxColumn"].Value));
                        //开票日期
                        mCell = mRow.CreateCell(3);
                        mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceDateDataGridViewTextBoxColumn"].Value));
                        //开票内容
                        InvoiceContent = Convert.ToString(mView.Rows[i].Cells["invoiceContentDataGridViewTextBoxColumn"].Value);
                        string[] contents = System.Text.RegularExpressions.Regex.Split(InvoiceContent, "!#!");
                        for (int n = 0; n < contents.Length - 3; n += 4)
                        {
                            mCell = mRow.CreateCell(4);
                            mCell.SetCellValue(contents[n]);
                            mCell = mRow.CreateCell(5);
                            mCell.SetCellValue(contents[n + 1]);
                            mCell = mRow.CreateCell(6);
                            mCell.SetCellValue(contents[n + 2]);
                            mCell = mRow.CreateCell(7);
                            mCell.SetCellValue(contents[n + 3]);

                            if (n < contents.Length - 7)
                            {
                                mRow = mSheet.CreateRow(mExcelRowCount++);
                            }
                        }
                        mSheet.CreateRow(mExcelRowCount++);
                    }
                }



                //创建文件
                SaveFileDialog mDialog = new SaveFileDialog();
                mDialog.AddExtension     = true;
                mDialog.DefaultExt       = "xls";
                mDialog.Filter           = "Excel Worksheets(*.xls)|*.xls";
                mDialog.InitialDirectory = System.Environment.CurrentDirectory;
                mDialog.FileName         = "开票-" + DateTime.Now.ToString("yyyyMMddhhmmss");
                if (mDialog.ShowDialog() == DialogResult.OK)
                {
                    Stream mFile = mDialog.OpenFile();
                    mWorkbook.Write(mFile);
                    mFile.Close();
                    MessageBox.Show("保存成功!", "提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
            return(0);
        }
Exemplo n.º 15
0
        protected void ExportButton_Click(object sender, EventArgs e)
        {
            ProductSearchInfo productSearch = new ProductSearchInfo();

            productSearch.IsSale         = (int)BoolType.True;
            productSearch.Name           = ShopCommon.ConvertToT <string>(Name.Text);
            productSearch.ClassId        = ShopCommon.ConvertToT <string>(ClassID.Text);
            productSearch.BrandId        = ShopCommon.ConvertToT <int>(BrandID.Text);
            productSearch.StorageAnalyse = ShopCommon.ConvertToT <int>(StorageAnalyse.Text);
            var data = ProductBLL.SearchList(1, 1000, productSearch, ref Count);

            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("Sheet1");
            sheet.DefaultColumnWidth = 18;
            sheet.CreateFreezePane(0, 1, 0, 1);

            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            row.Height = 20 * 20;
            row.CreateCell(0).SetCellValue("商品名称");
            row.CreateCell(1).SetCellValue("剩余库存量");
            row.CreateCell(2).SetCellValue("库存下限");
            row.CreateCell(3).SetCellValue("库存上限");

            //设置表头格式
            var headFont = book.CreateFont();

            headFont.Boldweight         = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            headFont.FontHeightInPoints = 10;
            var headStyle = book.CreateCellStyle();

            headStyle.SetFont(headFont);
            headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            headStyle.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            foreach (var cell in row.Cells)
            {
                cell.CellStyle = headStyle;
            }

            foreach (var entity in data)
            {
                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(data.IndexOf(entity) + 1);
                dataRow.CreateCell(0).SetCellValue(entity.Name);
                dataRow.CreateCell(1).SetCellValue(ShowStorageCount(entity.TotalStorageCount - entity.SendCount, entity.ImportActualStorageCount));
                dataRow.CreateCell(2).SetCellValue(Convert.ToString(entity.LowerCount));
                dataRow.CreateCell(3).SetCellValue(Convert.ToString(entity.UpperCount));

                var style = book.CreateCellStyle();
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                foreach (var cell in dataRow.Cells)
                {
                    cell.CellStyle = style;
                }
            }

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
            Response.End();
        }
    protected void lbtn_export_ribao_export_Click(object sender, EventArgs e)
    {
        string url = datemin.Value;

        if (url == "")
        {
            SystemTool.AlertShow(this, "开始日期不能为空");
            return;
        }
        url = datemax.Value;
        if (url == "")
        {
            SystemTool.AlertShow(this, "结束日期不能为空");
            return;
        }
        url = img1.ImageUrl;
        if (url == "")
        {
            SystemTool.AlertShow(this, "图片为空,请先刷新");
            return;
        }
        if (ViewState["date"].ToString() != datemin.Value || ViewState["date2"].ToString() != datemax.Value)
        {
            SystemTool.AlertShow(this, "当前图片与日期不一致,请先刷新后再导出此报表");
            return;
        }
        string imgurl = Server.MapPath("~/" + url);
        //Response.Write("<script>window.open('print.aspx?date="+datemin.Value+"&url="+url+"','_blank')</script>");
        string  sql = @"select facename,sensorNo,bracketNo,distance,max(zlmax) zlmax,AVG(zlavg) zlavg,min(zlmin) zlmin,MAX(cclmax) cclmax,AVG(cclavg) cclavg,MAX(mzlmax) mzlmax,AVG(mzlavg) mzlavg from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' group by bracketNo,sensorNo,distance,facename order by bracketno";
        DataSet ds  = DB.ExecuteSqlDataSet(sql, null);

        if (ds.Tables[0].Rows.Count <= 0)
        {
            SystemTool.AlertShow(this, "报表为空,请在初撑力与末阻力页面查询数据后再导出此报表");
            return;
        }
        //整面最大值、最小值
        string  zhengmianmax = ds.Tables[0].Compute("Max(zlmax)", "true").ToString();
        string  zhengmianmin = ds.Tables[0].Compute("Min(zlmin)", "true").ToString();
        string  zhengmianavg = Convert.ToDecimal(ds.Tables[0].Compute("avg(zlavg)", "true").ToString()).ToString("0.00");
        string  shangbumax   = "0.00";
        string  shangbumin   = "0.00";
        string  shangbuavg   = "0.00";
        string  zhongbumax   = "0.00";
        string  zhongbumin   = "0.00";
        string  zhongbuavg   = "0.00";
        string  xiabumax     = "0.00";
        string  xiabumin     = "0.00";
        string  xiabuavg     = "0.00";
        string  sqlszx       = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='上部'";
        DataSet dsszx        = DB.ExecuteSqlDataSet(sqlszx, null);

        if (dsszx.Tables[0].Rows.Count > 0)
        {
            shangbumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00");
            shangbumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00");
            shangbuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00");
        }
        sqlszx = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='中部'";
        dsszx  = DB.ExecuteSqlDataSet(sqlszx, null);
        if (dsszx.Tables[0].Rows.Count > 0)
        {
            zhongbumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00");
            zhongbumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00");
            zhongbuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00");
        }
        sqlszx = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='下部'";
        dsszx  = DB.ExecuteSqlDataSet(sqlszx, null);
        if (dsszx.Tables[0].Rows.Count > 0)
        {
            xiabumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00");
            xiabumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00");
            xiabuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00");
        }
        NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
        NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("sheet1");
        NPOI.SS.UserModel.ICellStyle     style = book.CreateCellStyle();
        //设置单元格的样式:水平对齐居中
        style.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Center;
        style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
        style.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
        style.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
        style.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
        style.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;

        NPOI.SS.UserModel.ICellStyle styleleft = book.CreateCellStyle();
        //设置单元格的样式:水平对齐居左
        styleleft.Alignment    = NPOI.SS.UserModel.HorizontalAlignment.Left;
        styleleft.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleft.BorderLeft   = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleft.BorderRight  = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleft.BorderTop    = NPOI.SS.UserModel.BorderStyle.Thin;
        NPOI.SS.UserModel.ICellStyle styleleftcenter = book.CreateCellStyle();
        //设置单元格的样式:居左居中
        styleleftcenter.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Left;
        styleleftcenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
        styleleftcenter.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleftcenter.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleftcenter.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleftcenter.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleftcenter.WrapText          = true;
        //将新的样式赋给单元格
        //cell.CellStyle = style;
        //设置一个合并单元格区域,使用上下左右定义CellRangeAddress区域
        //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列
        //第一行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
        NPOI.SS.UserModel.IRow  row   = sheet.CreateRow(0);
        NPOI.SS.UserModel.ICell cell0 = row.CreateCell(0);
        cell0.SetCellValue("综采支架压力综合日报表【报表日期" + datemin.Value + "-" + datemax.Value + "】");
        cell0.CellStyle             = style;
        row.CreateCell(1).CellStyle = style; row.CreateCell(2).CellStyle = style; row.CreateCell(3).CellStyle = style; row.CreateCell(4).CellStyle = style; row.CreateCell(5).CellStyle = style; row.CreateCell(6).CellStyle = style; row.CreateCell(7).CellStyle = style; row.CreateCell(8).CellStyle = style; row.CreateCell(9).CellStyle = style;
        //第二行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 9));
        NPOI.SS.UserModel.IRow  row1  = sheet.CreateRow(1);
        NPOI.SS.UserModel.ICell cell1 = row1.CreateCell(0);
        cell1.SetCellValue("单位:兆帕  工作面名称:" + ds.Tables[0].Rows[0]["facename"].ToString() + "  认证编号:  打印日期:" + DateTime.Now.ToString("yyyy-MM-dd"));
        cell1.CellStyle = style;
        row1.CreateCell(1).CellStyle = style; row1.CreateCell(2).CellStyle = style; row1.CreateCell(3).CellStyle = style; row1.CreateCell(4).CellStyle = style; row1.CreateCell(5).CellStyle = style; row1.CreateCell(6).CellStyle = style; row1.CreateCell(7).CellStyle = style; row1.CreateCell(8).CellStyle = style; row1.CreateCell(9).CellStyle = style;
        // 第三行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 9));
        NPOI.SS.UserModel.IRow  row2  = sheet.CreateRow(2);
        NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(0);
        cell2.CellStyle = style;
        row2.CreateCell(1).CellStyle = style; row2.CreateCell(2).CellStyle = style; row2.CreateCell(3).CellStyle = style; row2.CreateCell(4).CellStyle = style; row2.CreateCell(5).CellStyle = style; row2.CreateCell(6).CellStyle = style; row2.CreateCell(7).CellStyle = style; row2.CreateCell(8).CellStyle = style; row2.CreateCell(9).CellStyle = style;
        row2.Height = 4800;
        //将图片文件读入一个字符串
        byte[] bytes      = System.IO.File.ReadAllBytes(imgurl);
        int    pictureIdx = book.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);

        NPOI.HSSF.UserModel.HSSFPatriarch patriarch = (NPOI.HSSF.UserModel.HSSFPatriarch)sheet.CreateDrawingPatriarch();
        // 插图片的位置  HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
        //dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一
        //dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
        //dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。
        //dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
        //col1和row1 :图片左上角的位置,以excel单元格为参考,比喻这两个值为(1,1),那么图片左上角的位置就是excel表(1,1)单元格的右下角的点(A,1)右下角的点。
        //col2和row2:图片右下角的位置,以excel单元格为参考,比喻这两个值为(2,2),那么图片右下角的位置就是excel表(2,2)单元格的右下角的点(B,2)右下角的点。
        NPOI.HSSF.UserModel.HSSFClientAnchor anchor = new NPOI.HSSF.UserModel.HSSFClientAnchor(10, 10, 10, 10, 0, 2, 9, 3);
        //把图片插到相应的位置
        NPOI.HSSF.UserModel.HSSFPicture pict = (NPOI.HSSF.UserModel.HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
        //第三行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 9));
        NPOI.SS.UserModel.IRow  row3  = sheet.CreateRow(3);
        NPOI.SS.UserModel.ICell cell3 = row3.CreateCell(0);
        cell3.SetCellValue("监测数据统计表:");
        cell3.CellStyle = styleleft;
        row3.CreateCell(1).CellStyle = styleleft; row3.CreateCell(2).CellStyle = styleleft; row3.CreateCell(3).CellStyle = styleleft; row3.CreateCell(4).CellStyle = styleleft; row3.CreateCell(5).CellStyle = styleleft; row3.CreateCell(6).CellStyle = styleleft; row3.CreateCell(7).CellStyle = styleleft; row3.CreateCell(8).CellStyle = styleleft; row3.CreateCell(9).CellStyle = styleleft;
        //第四行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 8, 0, 1));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 2, 3));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 4, 5));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 6, 7));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 8, 9));

        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 2, 3));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 4, 5));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 6, 7));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 8, 9));

        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 2, 3));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 4, 5));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 6, 7));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 8, 9));
        NPOI.SS.UserModel.IRow  row4   = sheet.CreateRow(4);
        NPOI.SS.UserModel.ICell cell40 = row4.CreateCell(0);
        cell40.SetCellValue("工作面工作阻力统计:");
        cell40.CellStyle = style;

        NPOI.SS.UserModel.ICell cell41 = row4.CreateCell(1); cell41.CellStyle = style;
        NPOI.SS.UserModel.ICell cell42 = row4.CreateCell(2); cell42.SetCellValue("整面"); cell42.CellStyle = style;
        NPOI.SS.UserModel.ICell cell43 = row4.CreateCell(3); cell43.CellStyle = style;
        NPOI.SS.UserModel.ICell cell44 = row4.CreateCell(4); cell44.SetCellValue("上部"); cell44.CellStyle = style;
        NPOI.SS.UserModel.ICell cell45 = row4.CreateCell(5); cell45.CellStyle = style;
        NPOI.SS.UserModel.ICell cell46 = row4.CreateCell(6); cell46.SetCellValue("中部"); cell46.CellStyle = style;
        NPOI.SS.UserModel.ICell cell47 = row4.CreateCell(7); cell47.CellStyle = style;
        NPOI.SS.UserModel.ICell cell48 = row4.CreateCell(8); cell48.SetCellValue("下部"); cell48.CellStyle = style;
        NPOI.SS.UserModel.ICell cell49 = row4.CreateCell(9); cell49.CellStyle = style;

        NPOI.SS.UserModel.IRow  row5   = sheet.CreateRow(5);
        NPOI.SS.UserModel.ICell cell50 = row5.CreateCell(0); cell50.CellStyle = style;
        NPOI.SS.UserModel.ICell cell51 = row5.CreateCell(1); cell51.CellStyle = style;
        NPOI.SS.UserModel.ICell cell52 = row5.CreateCell(2); cell52.SetCellValue("最大"); cell52.CellStyle = style;
        NPOI.SS.UserModel.ICell cell53 = row5.CreateCell(3); cell53.SetCellValue("最小"); cell53.CellStyle = style;
        NPOI.SS.UserModel.ICell cell54 = row5.CreateCell(4); cell54.SetCellValue("最大"); cell54.CellStyle = style;
        NPOI.SS.UserModel.ICell cell55 = row5.CreateCell(5); cell55.SetCellValue("最小"); cell55.CellStyle = style;
        NPOI.SS.UserModel.ICell cell56 = row5.CreateCell(6); cell56.SetCellValue("最大"); cell56.CellStyle = style;
        NPOI.SS.UserModel.ICell cell57 = row5.CreateCell(7); cell57.SetCellValue("最小"); cell57.CellStyle = style;
        NPOI.SS.UserModel.ICell cell58 = row5.CreateCell(8); cell58.SetCellValue("最大"); cell58.CellStyle = style;
        NPOI.SS.UserModel.ICell cell59 = row5.CreateCell(9); cell59.SetCellValue("最小"); cell59.CellStyle = style;
        NPOI.SS.UserModel.IRow  row6   = sheet.CreateRow(6);
        NPOI.SS.UserModel.ICell cell61 = row6.CreateCell(1); cell61.CellStyle = style;
        NPOI.SS.UserModel.ICell cell62 = row6.CreateCell(2); cell62.SetCellValue("平均"); cell62.CellStyle = style;
        NPOI.SS.UserModel.ICell cell63 = row6.CreateCell(3); cell63.CellStyle = style;
        NPOI.SS.UserModel.ICell cell64 = row6.CreateCell(4); cell64.SetCellValue("平均"); cell64.CellStyle = style;
        NPOI.SS.UserModel.ICell cell65 = row6.CreateCell(5); cell65.CellStyle = style;
        NPOI.SS.UserModel.ICell cell66 = row6.CreateCell(6); cell66.SetCellValue("平均"); cell66.CellStyle = style;
        NPOI.SS.UserModel.ICell cell67 = row6.CreateCell(7); cell67.CellStyle = style;
        NPOI.SS.UserModel.ICell cell68 = row6.CreateCell(8); cell68.SetCellValue("平均"); cell68.CellStyle = style;
        NPOI.SS.UserModel.ICell cell69 = row6.CreateCell(9); cell69.CellStyle = style;
        NPOI.SS.UserModel.IRow  row7   = sheet.CreateRow(7);
        NPOI.SS.UserModel.ICell cell70 = row7.CreateCell(0); cell70.CellStyle = style;
        NPOI.SS.UserModel.ICell cell71 = row7.CreateCell(1); cell71.CellStyle = style;
        NPOI.SS.UserModel.ICell cell72 = row7.CreateCell(2); cell72.SetCellValue(zhengmianmax); cell72.CellStyle = style;
        NPOI.SS.UserModel.ICell cell73 = row7.CreateCell(3); cell73.SetCellValue(zhengmianmin); cell73.CellStyle = style;
        NPOI.SS.UserModel.ICell cell74 = row7.CreateCell(4); cell74.SetCellValue(shangbumax); cell74.CellStyle = style;
        NPOI.SS.UserModel.ICell cell75 = row7.CreateCell(5); cell75.SetCellValue(shangbumin); cell75.CellStyle = style;
        NPOI.SS.UserModel.ICell cell76 = row7.CreateCell(6); cell76.SetCellValue(zhongbumax); cell76.CellStyle = style;
        NPOI.SS.UserModel.ICell cell77 = row7.CreateCell(7); cell77.SetCellValue(zhongbumin); cell77.CellStyle = style;
        NPOI.SS.UserModel.ICell cell78 = row7.CreateCell(8); cell78.SetCellValue(xiabumax); cell78.CellStyle = style;
        NPOI.SS.UserModel.ICell cell79 = row7.CreateCell(9); cell79.SetCellValue(xiabumin); cell79.CellStyle = style;
        NPOI.SS.UserModel.IRow  row8   = sheet.CreateRow(8);
        NPOI.SS.UserModel.ICell cell80 = row8.CreateCell(0); cell80.CellStyle = style;
        NPOI.SS.UserModel.ICell cell81 = row8.CreateCell(1); cell81.CellStyle = style;
        NPOI.SS.UserModel.ICell cell82 = row8.CreateCell(2); cell82.SetCellValue(zhengmianavg); cell82.CellStyle = style;
        NPOI.SS.UserModel.ICell cell83 = row8.CreateCell(3); cell83.CellStyle = style;
        NPOI.SS.UserModel.ICell cell84 = row8.CreateCell(4); cell84.SetCellValue(shangbuavg); cell84.CellStyle = style;
        NPOI.SS.UserModel.ICell cell85 = row8.CreateCell(5); cell85.CellStyle = style;
        NPOI.SS.UserModel.ICell cell86 = row8.CreateCell(6); cell86.SetCellValue(zhongbuavg); cell86.CellStyle = style;
        NPOI.SS.UserModel.ICell cell87 = row8.CreateCell(7); cell87.CellStyle = style;
        NPOI.SS.UserModel.ICell cell88 = row8.CreateCell(8); cell88.SetCellValue(xiabuavg); cell88.CellStyle = style;
        NPOI.SS.UserModel.ICell cell89 = row8.CreateCell(9); cell89.CellStyle = style;
        //第5行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 0, 0));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 1, 1));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 2, 3));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 4, 5));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 6, 7));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 8, 9));
        NPOI.SS.UserModel.IRow  row9    = sheet.CreateRow(9);
        NPOI.SS.UserModel.ICell cell90  = row9.CreateCell(0); cell90.SetCellValue("分机编号"); cell90.CellStyle = style;
        NPOI.SS.UserModel.ICell cell91  = row9.CreateCell(1); cell91.SetCellValue("支架编号"); cell91.CellStyle = style;
        NPOI.SS.UserModel.ICell cell92  = row9.CreateCell(2); cell92.SetCellValue("安装位置"); cell92.CellStyle = style;
        NPOI.SS.UserModel.ICell cell93  = row9.CreateCell(3); cell93.CellStyle = style;
        NPOI.SS.UserModel.ICell cell94  = row9.CreateCell(4); cell94.SetCellValue("工作阻力"); cell94.CellStyle = style;
        NPOI.SS.UserModel.ICell cell95  = row9.CreateCell(5); cell95.CellStyle = style;
        NPOI.SS.UserModel.ICell cell96  = row9.CreateCell(6); cell96.SetCellValue("初撑力"); cell96.CellStyle = style;
        NPOI.SS.UserModel.ICell cell97  = row9.CreateCell(7); cell97.CellStyle = style;
        NPOI.SS.UserModel.ICell cell98  = row9.CreateCell(8); cell98.SetCellValue("末阻力"); cell98.CellStyle = style;
        NPOI.SS.UserModel.ICell cell99  = row9.CreateCell(9); cell99.CellStyle = style;
        NPOI.SS.UserModel.IRow  row10   = sheet.CreateRow(10);
        NPOI.SS.UserModel.ICell cell100 = row10.CreateCell(0); cell100.CellStyle = style;
        NPOI.SS.UserModel.ICell cell101 = row10.CreateCell(1); cell101.CellStyle = style;
        NPOI.SS.UserModel.ICell cell102 = row10.CreateCell(2); cell102.CellStyle = style;
        NPOI.SS.UserModel.ICell cell103 = row10.CreateCell(3); cell103.CellStyle = style;
        NPOI.SS.UserModel.ICell cell104 = row10.CreateCell(4); cell104.SetCellValue("最大"); cell104.CellStyle = style;
        NPOI.SS.UserModel.ICell cell105 = row10.CreateCell(5); cell105.SetCellValue("平均"); cell105.CellStyle = style;
        NPOI.SS.UserModel.ICell cell106 = row10.CreateCell(6); cell106.SetCellValue("最大"); cell106.CellStyle = style;
        NPOI.SS.UserModel.ICell cell107 = row10.CreateCell(7); cell107.SetCellValue("平均"); cell107.CellStyle = style;
        NPOI.SS.UserModel.ICell cell108 = row10.CreateCell(8); cell108.SetCellValue("最大"); cell108.CellStyle = style;
        NPOI.SS.UserModel.ICell cell109 = row10.CreateCell(9); cell109.SetCellValue("平均"); cell109.CellStyle = style;
        //
        string jiancefenxi = "支架编号:";

        for (int i = 11; i < ds.Tables[0].Rows.Count + 11; i++)
        {
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i, 2, 3));
            NPOI.SS.UserModel.IRow  row11   = sheet.CreateRow(i);
            NPOI.SS.UserModel.ICell cell110 = row11.CreateCell(0); cell110.SetCellValue(ds.Tables[0].Rows[i - 11]["sensorNo"].ToString()); cell110.CellStyle = style;
            NPOI.SS.UserModel.ICell cell111 = row11.CreateCell(1); cell111.SetCellValue(ds.Tables[0].Rows[i - 11]["bracketno"].ToString()); cell111.CellStyle = style;
            NPOI.SS.UserModel.ICell cell112 = row11.CreateCell(2); cell112.SetCellValue(ds.Tables[0].Rows[i - 11]["distance"].ToString()); cell112.CellStyle = style;
            NPOI.SS.UserModel.ICell cell113 = row11.CreateCell(3); cell113.CellStyle = style;
            NPOI.SS.UserModel.ICell cell114 = row11.CreateCell(4); cell114.SetCellValue(ds.Tables[0].Rows[i - 11]["zlmax"].ToString()); cell114.CellStyle = style;
            NPOI.SS.UserModel.ICell cell115 = row11.CreateCell(5); cell115.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["zlavg"].ToString()).ToString("0.00")); cell115.CellStyle = style;
            NPOI.SS.UserModel.ICell cell116 = row11.CreateCell(6); cell116.SetCellValue(ds.Tables[0].Rows[i - 11]["cclmax"].ToString()); cell116.CellStyle = style;
            NPOI.SS.UserModel.ICell cell117 = row11.CreateCell(7); cell117.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["cclavg"].ToString()).ToString("0.00")); cell117.CellStyle = style;
            NPOI.SS.UserModel.ICell cell118 = row11.CreateCell(8); cell118.SetCellValue(ds.Tables[0].Rows[i - 11]["mzlmax"].ToString()); cell118.CellStyle = style;
            NPOI.SS.UserModel.ICell cell119 = row11.CreateCell(9); cell119.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["mzlavg"].ToString()).ToString("0.00")); cell119.CellStyle = style;
            if (ViewState["yujingzhi"].ToString() != "0")
            {
                decimal fenxi     = Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == "" ? "0.00" : ds.Tables[0].Rows[i - 11]["zlavg"].ToString());
                decimal yujingzhi = Convert.ToDecimal(ViewState["yujingzhi"].ToString());
                if (fenxi >= yujingzhi)
                {
                    jiancefenxi += "" + ds.Tables[0].Rows[i - 11]["bracketno"].ToString() + "超压(" + fenxi.ToString("0.00") + "),";
                }
            }
        }
        jiancefenxi += "请注意观察";
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 11, ds.Tables[0].Rows.Count + 11, 0, 9));
        NPOI.SS.UserModel.IRow  rowa   = sheet.CreateRow(ds.Tables[0].Rows.Count + 11);
        NPOI.SS.UserModel.ICell cella0 = rowa.CreateCell(0); cella0.SetCellValue("监测分析结论:"); cella0.CellStyle = styleleft;
        NPOI.SS.UserModel.ICell cella1 = rowa.CreateCell(1); cella1.CellStyle = style;
        NPOI.SS.UserModel.ICell cella2 = rowa.CreateCell(2); cella2.CellStyle = style;
        NPOI.SS.UserModel.ICell cella3 = rowa.CreateCell(3); cella3.CellStyle = style;
        NPOI.SS.UserModel.ICell cella4 = rowa.CreateCell(4); cella4.CellStyle = style;
        NPOI.SS.UserModel.ICell cella5 = rowa.CreateCell(5); cella5.CellStyle = style;
        NPOI.SS.UserModel.ICell cella6 = rowa.CreateCell(6); cella6.CellStyle = style;
        NPOI.SS.UserModel.ICell cella7 = rowa.CreateCell(7); cella7.CellStyle = style;
        NPOI.SS.UserModel.ICell cella8 = rowa.CreateCell(8); cella8.CellStyle = style;
        NPOI.SS.UserModel.ICell cella9 = rowa.CreateCell(9); cella9.CellStyle = style;
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 12, ds.Tables[0].Rows.Count + 12, 0, 9));
        NPOI.SS.UserModel.IRow rowb = sheet.CreateRow(ds.Tables[0].Rows.Count + 12);
        rowb.Height = 2400;
        NPOI.SS.UserModel.ICell cellb0 = rowb.CreateCell(0); cellb0.SetCellValue("" + jiancefenxi); cellb0.CellStyle = styleleftcenter;
        NPOI.SS.UserModel.ICell cellb1 = rowb.CreateCell(1); cellb1.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb2 = rowb.CreateCell(2); cellb2.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb3 = rowb.CreateCell(3); cellb3.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb4 = rowb.CreateCell(4); cellb4.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb5 = rowb.CreateCell(5); cellb5.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb6 = rowb.CreateCell(6); cellb6.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb7 = rowb.CreateCell(7); cellb7.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb8 = rowb.CreateCell(8); cellb8.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb9 = rowb.CreateCell(9); cellb9.CellStyle = style;
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 13, ds.Tables[0].Rows.Count + 13, 0, 9));
        NPOI.SS.UserModel.IRow  rowc   = sheet.CreateRow(ds.Tables[0].Rows.Count + 13);
        NPOI.SS.UserModel.ICell cellc0 = rowc.CreateCell(0); cellc0.SetCellValue("区队意见:"); cellc0.CellStyle = styleleft;
        NPOI.SS.UserModel.ICell cellc1 = rowc.CreateCell(1); cellc1.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc2 = rowc.CreateCell(2); cellc2.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc3 = rowc.CreateCell(3); cellc3.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc4 = rowc.CreateCell(4); cellc4.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc5 = rowc.CreateCell(5); cellc5.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc6 = rowc.CreateCell(6); cellc6.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc7 = rowc.CreateCell(7); cellc7.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc8 = rowc.CreateCell(8); cellc8.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc9 = rowc.CreateCell(9); cellc9.CellStyle = style;
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 14, ds.Tables[0].Rows.Count + 14, 0, 9));
        NPOI.SS.UserModel.IRow rowd = sheet.CreateRow(ds.Tables[0].Rows.Count + 14);
        rowd.Height = 2400;
        NPOI.SS.UserModel.ICell celld0 = rowd.CreateCell(0); celld0.SetCellValue("               领导签字:____________部门签字:____________报表人:____________"); celld0.CellStyle = styleleft;
        NPOI.SS.UserModel.ICell celld1 = rowd.CreateCell(1); celld1.CellStyle = style;
        NPOI.SS.UserModel.ICell celld2 = rowd.CreateCell(2); celld2.CellStyle = style;
        NPOI.SS.UserModel.ICell celld3 = rowd.CreateCell(3); celld3.CellStyle = style;
        NPOI.SS.UserModel.ICell celld4 = rowd.CreateCell(4); celld4.CellStyle = style;
        NPOI.SS.UserModel.ICell celld5 = rowd.CreateCell(5); celld5.CellStyle = style;
        NPOI.SS.UserModel.ICell celld6 = rowd.CreateCell(6); celld6.CellStyle = style;
        NPOI.SS.UserModel.ICell celld7 = rowd.CreateCell(7); celld7.CellStyle = style;
        NPOI.SS.UserModel.ICell celld8 = rowd.CreateCell(8); celld8.CellStyle = style;
        NPOI.SS.UserModel.ICell celld9 = rowd.CreateCell(9); celld9.CellStyle = style;
        // 写入到客户端
        System.IO.MemoryStream ms = new System.IO.MemoryStream();
        book.Write(ms);
        Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
        Response.BinaryWrite(ms.ToArray());
        book = null;
        ms.Close();
        ms.Dispose();
    }
Exemplo n.º 17
0
        private void btnExcel_Click(object sender, EventArgs e)
        {
            try
            {
                IWorkbook mWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new FileStream(Application.StartupPath + "\\office\\财务统计模板.xls", FileMode.Open, FileAccess.Read));
                //IWorkbook mWorkbook =
                //ISheet mSheet = mWorkbook.CreateSheet("Sheet1");
                ISheet     mSheet     = mWorkbook.GetSheet("采购明细表");
                ICellStyle styleRight = mWorkbook.CreateCellStyle();
                //ICellStyle NumberStyle = mWorkbook.CreateCellStyle();
                //IDataFormat format = mWorkbook.CreateDataFormat();
                styleRight.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Center;
                styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                //NumberStyle.DataFormat = format.GetFormat("0.00");
                ICell mCell;
                int   i, j;

                DataGridView mView = dgvDaPurchaseContract;

                //添加标头
                IRow mRow;//= mSheet.CreateRow(0);
                //for (i = 0; i <= mView.Columns.Count - 1; i++)
                //{
                //    mCell = mRow.CreateCell(i);
                //    mCell.SetCellValue(mView.Columns[i].HeaderText);
                //    mCell.CellStyle = styleRight;
                //}
                //添加内容

                for (i = 1; i <= mView.RowCount; i++)
                {
                    mRow = mSheet.CreateRow(i + 2);
                    for (j = 0; j <= mView.Columns.Count - 1; j++)
                    {
                        if (mView.Columns[j].Visible == false)
                        {
                            continue;
                        }
                        mCell = mRow.CreateCell(j);

                        if (Classes.PubFunc.IsNumber(Convert.ToString(mView.Rows[i - 1].Cells[j].Value)) == true && j != dpc单据号码.Index)
                        {
                            mCell.SetCellValue(Convert.ToDouble(mView.Rows[i - 1].Cells[j].Value));
                        }
                        else
                        {
                            mCell.SetCellValue(Convert.ToString(mView.Rows[i - 1].Cells[j].Value));
                        }
                        mCell.CellStyle = styleRight;
                    }
                }



                //创建文件
                SaveFileDialog mDialog = new SaveFileDialog();
                mDialog.AddExtension     = true;
                mDialog.DefaultExt       = "xls";
                mDialog.Filter           = "Excel Worksheets(*.xls)|*.xls";
                mDialog.InitialDirectory = System.Environment.CurrentDirectory;
                mDialog.FileName         = "财务采购统计-" + DateTime.Now.ToString("yyyyMMddhhmmss");
                if (mDialog.ShowDialog() == DialogResult.OK)
                {
                    Stream mFile = mDialog.OpenFile();
                    mWorkbook.Write(mFile);
                    mFile.Close();
                    MessageBox.Show("保存成功!", "提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }
Exemplo n.º 18
0
        protected void btnExport_Click(object sender, EventArgs e)
        {
            var userList = UserBLL.SearchList(new UserSearchInfo {
                UserName = UserName.Text, ProviderNo = ProviderNo.Text, UserType = (int)UserType.Provider
            });

            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("Sheet1");
            sheet.DefaultColumnWidth = 18;
            sheet.CreateFreezePane(0, 1, 0, 1);

            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            row.Height = 20 * 20;
            row.CreateCell(0).SetCellValue("供应商编号");
            row.CreateCell(1).SetCellValue("用户名");
            row.CreateCell(2).SetCellValue("电子邮箱");
            row.CreateCell(3).SetCellValue("公司名称");
            row.CreateCell(4).SetCellValue("对公银行开户行");
            row.CreateCell(5).SetCellValue("税务号");
            row.CreateCell(6).SetCellValue("法人代表");
            row.CreateCell(7).SetCellValue("联系人");
            row.CreateCell(8).SetCellValue("联系电话");
            row.CreateCell(9).SetCellValue("传真");
            row.CreateCell(10).SetCellValue("经营品牌");
            row.CreateCell(11).SetCellValue("经营品类");
            row.CreateCell(12).SetCellValue("结算方式");
            row.CreateCell(13).SetCellValue("结算周期");
            row.CreateCell(14).SetCellValue("物流配送");
            row.CreateCell(15).SetCellValue("售后服务");
            row.CreateCell(16).SetCellValue("退换货保障");
            row.CreateCell(17).SetCellValue("详细地址");

            //设置表头格式
            var headFont = book.CreateFont();

            headFont.Boldweight         = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            headFont.FontHeightInPoints = 10;
            var headStyle = book.CreateCellStyle();

            headStyle.SetFont(headFont);
            headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            headStyle.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            foreach (var cell in row.Cells)
            {
                cell.CellStyle = headStyle;
            }

            foreach (var user in userList)
            {
                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(userList.IndexOf(user) + 1);
                dataRow.CreateCell(0).SetCellValue(user.ProviderNo);
                dataRow.CreateCell(1).SetCellValue(user.UserName);
                dataRow.CreateCell(2).SetCellValue(user.Email);
                dataRow.CreateCell(3).SetCellValue(user.ProviderName);
                dataRow.CreateCell(4).SetCellValue(user.ProviderBankNo);
                dataRow.CreateCell(5).SetCellValue(user.ProviderTaxRegistration);
                dataRow.CreateCell(6).SetCellValue(user.ProviderCorporate);
                dataRow.CreateCell(7).SetCellValue(user.ProviderLinker);
                dataRow.CreateCell(8).SetCellValue(user.ProviderLinkerTel);
                dataRow.CreateCell(9).SetCellValue(user.ProviderFax);
                dataRow.CreateCell(10).SetCellValue(user.ProviderOperateBrand);
                dataRow.CreateCell(11).SetCellValue(user.ProviderOperateClass);
                dataRow.CreateCell(12).SetCellValue(user.ProviderAccount);
                dataRow.CreateCell(13).SetCellValue(user.ProviderAccountCycle);
                dataRow.CreateCell(14).SetCellValue(user.ProviderShipping);
                dataRow.CreateCell(15).SetCellValue(user.ProviderService);
                dataRow.CreateCell(16).SetCellValue(user.ProviderEnsure);
                dataRow.CreateCell(17).SetCellValue(RegionBLL.RegionNameList(user.RegionId) + ", " + user.ProviderAddress);
            }

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
            Response.End();
        }
Exemplo n.º 19
0
        public static void ExportFile(DataTable dt, string fileName, List <string> columnNames, int[] mergeCondition, int[] noMergeColumns, int[] wrapColumns, int [] numerberColumns)
        {
            using (var book = new NPOI.HSSF.UserModel.HSSFWorkbook())
            {
                var sheet   = book.CreateSheet();
                var headRow = sheet.CreateRow(0);

                for (int i = 0; i < columnNames.Count; i++)
                {
                    var cell = headRow.CreateCell(i);
                    cell.SetCellValue(columnNames[i]);
                    var font = book.CreateFont();
                    font.Boldweight = NPOI.HSSF.UserModel.HSSFFont.BOLDWEIGHT_BOLD;
                    cell.RichStringCellValue.ApplyFont(font);
                }
                foreach (var wrapC in wrapColumns)
                {
                    NPOI.HSSF.UserModel.HSSFCellStyle cs = book.CreateCellStyle();
                    cs.WrapText = true;
                    sheet.SetDefaultColumnStyle((short)wrapC, cs);
                }
                //开始写入内容
                int RowCount = dt.Rows.Count;     //行数
                int ColCount = columnNames.Count; //列数
                for (int rowIndex = 0; rowIndex < RowCount; rowIndex++)
                {
                    var row = sheet.CreateRow(rowIndex + 1);
                    for (int colIndex = 0; colIndex < ColCount; colIndex++)
                    {
                        var cell = row.CreateCell(colIndex);
                        if (IsWrapColumn(wrapColumns, colIndex))
                        {
                            cell.SetCellValue(dt.Rows[rowIndex][colIndex].ToString().Replace("</br>", "\n"));
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[rowIndex][colIndex].ToString());
                        }
                    }
                }
                for (int rowIndex = 0; rowIndex < RowCount; rowIndex++)
                {
                    for (int colIndex = 0; colIndex < ColCount; colIndex++)
                    {
                        if (noMergeColumns.Contains(colIndex))
                        {
                            continue;
                        }
                        var rowSpan = GetRowSpan(dt, mergeCondition, rowIndex, colIndex);
                        if (rowSpan <= 1)
                        {
                            continue;
                        }
                        sheet.AddMergedRegion(new NPOI.HSSF.Util.CellRangeAddress(rowIndex + 1, rowIndex + rowSpan, colIndex, colIndex));
                    }
                }
                ////设置每列的宽度
                //for (int colIndex = 0; colIndex < columnNames.Count; colIndex++)
                //{
                //    sheet.AutoSizeColumn(colIndex);
                //    sheet.SetColumnWidth(colIndex, sheet.GetColumnWidth(colIndex) + 2100);
                //}
                ExportFile(book, fileName);
            }
        }
Exemplo n.º 20
0
        public static MemoryStream RenderToExcel(List<Models.Beans.Contract> list)
        {   
            MemoryStream ms = new MemoryStream();

            NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");

            #region head row
            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            ICellStyle style = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.Bold;
            style.SetFont(font);
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            //row.RowStyle = style;
            row.Height = 26 * 20;

            CreateCell(row, 0, "存放位置", CellType.String, style);
            CreateCell(row, 1, "合同号", CellType.String, style);
            CreateCell(row, 2, "序号", CellType.String, style);
            CreateCell(row, 3, "项目编号", CellType.String, style);
            CreateCell(row, 4, "项目名称", CellType.String, style);
            CreateCell(row, 5, "项目负责人", CellType.String, style);
            CreateCell(row, 6, "联系方式", CellType.String, style);
            CreateCell(row, 7, "分管部门", CellType.String, style);
            CreateCell(row, 8, "分包名称", CellType.String, style);
            CreateCell(row, 9, "分包预算(万元)", CellType.String, style);
            CreateCell(row, 10, "招标编号", CellType.String, style);
            CreateCell(row, 11, "招标公司", CellType.String, style);
            CreateCell(row, 12, "开标时间", CellType.String, style);
            CreateCell(row, 13, "付款方式", CellType.String, style);
            CreateCell(row, 14, "中标公司名称", CellType.String, style);
            CreateCell(row, 15, "联系人", CellType.String, style);
            CreateCell(row, 16, "手机号码", CellType.String, style);
            CreateCell(row, 17, "中标金额(万元)", CellType.String, style);
            CreateCell(row, 18, "签合同日期", CellType.String, style);
            CreateCell(row, 19, "交货时间", CellType.String, style);
            CreateCell(row, 20, "验收情况", CellType.String, style);
            CreateCell(row, 21, "进度", CellType.String, style);
            CreateCell(row, 22, "支付全款", CellType.String, style);
            CreateCell(row, 23, "押款", CellType.String, style);
            CreateCell(row, 24, "退款", CellType.String, style);
            CreateCell(row, 25, "标签", CellType.String, style);
            #endregion

            if (list != null && list.Count > 0)
            {
                int rowidx = 0;
                foreach (Models.Beans.Contract model in list)
                {
                    #region row
                    rowidx++;
                    row = sheet.CreateRow(rowidx);
                    CreateCell(row, 0, model.contractplace);
                    CreateCell(row, 1, model.contractnum);
                    CreateCell(row, 2, model.seq);
                    CreateCell(row, 3, model.projectnum);
                    CreateCell(row, 4, model.projectname);
                    CreateCell(row, 5, model.projectmanager);
                    CreateCell(row, 6, model.tel);
                    CreateCell(row, 7, model.depart);
                    CreateCell(row, 8, model.packageName);
                    CreateCell(row, 9, model.packageBudget);
                    CreateCell(row, 10, model.tendarNum);
                    CreateCell(row, 11, model.tendarCompany);
                    CreateCell(row, 12, model.tendarStartTime);
                    CreateCell(row, 13, model.paymethod);
                    CreateCell(row, 14, model.bcompany);
                    CreateCell(row, 15, model.linker);
                    CreateCell(row, 16, model.phone);
                    CreateCell(row, 17, model.money);
                    CreateCell(row, 18, model.signingdate);
                    CreateCell(row, 19, model.deliveryTime);
                    CreateCell(row, 20, model.inspection);
                    CreateCell(row, 21, model.progress);
                    CreateCell(row, 22, model.isPayAll);
                    CreateCell(row, 23, model.isArmoured);
                    CreateCell(row, 24, model.isRefund);        
                    CreateCell(row, 25, model.contractrfid);
                    #endregion
                }
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            return ms;
        }
Exemplo n.º 21
0
        public JsonResult ExportMember(string keyword = "")
        {
            var list = _repositoryFactory.IMemberCard.Where(c => true);

            //搜索关键字过滤
            if (!string.IsNullOrEmpty(keyword))
            {
                list = list.Where(c => c.CardNo.Contains(keyword) || c.Mobile.Contains(keyword));
            }
            var data = list.OrderByDescending(m => m.CreateTime).ToList();

            if (data.Count > 0)
            {
                //NPOI导出数据
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                ISheet     sheet  = book.CreateSheet("sheet1");
                ICellStyle style1 = book.CreateCellStyle();
                style1.Alignment         = HorizontalAlignment.Center;
                style1.VerticalAlignment = VerticalAlignment.Center;
                ICellStyle style2 = book.CreateCellStyle();
                style2.Alignment         = HorizontalAlignment.Left;
                style2.VerticalAlignment = VerticalAlignment.Center;
                IRow  headerrow = sheet.CreateRow(0);
                ICell cell_1    = headerrow.CreateCell(0);
                cell_1.CellStyle = style1;
                cell_1.SetCellValue("会员卡号");
                ICell cell_2 = headerrow.CreateCell(1);
                cell_2.CellStyle = style1;
                cell_2.SetCellValue("手机号");
                ICell cell_3 = headerrow.CreateCell(2);
                cell_3.CellStyle = style1;
                cell_3.SetCellValue("姓名");
                ICell cell_4 = headerrow.CreateCell(3);
                cell_4.CellStyle = style1;
                cell_4.SetCellValue("性别");
                ICell cell_5 = headerrow.CreateCell(4);
                cell_5.CellStyle = style1;
                cell_5.SetCellValue("创建时间");
                ICell cell_6 = headerrow.CreateCell(5);
                cell_6.CellStyle = style1;
                cell_6.SetCellValue("创建人");
                ICell cell_7 = headerrow.CreateCell(6);
                cell_7.CellStyle = style1;
                cell_7.SetCellValue("状态");
                ICell cell_8 = headerrow.CreateCell(7);
                cell_8.CellStyle = style1;
                cell_8.SetCellValue("可用余额");
                ICell cell_9 = headerrow.CreateCell(8);
                cell_9.CellStyle = style1;
                cell_9.SetCellValue("累计充值");
                foreach (var item in data)
                {
                    IRow  headerrow_1 = sheet.CreateRow(data.IndexOf(item) + 1);
                    ICell cell_1_1    = headerrow_1.CreateCell(0);
                    cell_1_1.CellStyle = style1;
                    cell_1_1.SetCellValue(item.CardNo);
                    ICell cell_1_2 = headerrow_1.CreateCell(1);
                    cell_1_2.CellStyle = style1;
                    cell_1_2.SetCellValue(item.Mobile);
                    ICell cell_1_3 = headerrow_1.CreateCell(2);
                    cell_1_3.CellStyle = style1;
                    cell_1_3.SetCellValue(item.Name);
                    ICell cell_1_4 = headerrow_1.CreateCell(3);
                    cell_1_4.CellStyle = style1;
                    cell_1_4.SetCellValue(item.Sex);
                    ICell cell_1_5 = headerrow_1.CreateCell(4);
                    cell_1_5.CellStyle = style1;
                    cell_1_5.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", item.CreateTime));
                    ICell cell_1_6 = headerrow_1.CreateCell(5);
                    cell_1_6.CellStyle = style1;
                    cell_1_6.SetCellValue(item.CreateUser);
                    ICell cell_1_7 = headerrow_1.CreateCell(6);
                    cell_1_7.CellStyle = style1;
                    cell_1_7.SetCellValue(EnumHepler.GetEnumDescription(((EnumHepler.MemberCardStatus)item.Status)));
                    ICell cell_1_8 = headerrow_1.CreateCell(7);
                    cell_1_8.CellStyle = style1;
                    cell_1_8.SetCellValue(string.Format("{0:N2}", item.Banlance));
                    ICell cell_1_9 = headerrow_1.CreateCell(8);
                    cell_1_9.CellStyle = style1;
                    cell_1_9.SetCellValue(string.Format("{0:N2}", item.TotalMoney));
                }
                sheet.SetColumnWidth(1, 30 * 150);
                sheet.SetColumnWidth(3, 30 * 250);
                string fileName = "会员卡列表_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
                string filePath = HttpContext.Server.MapPath("/Upload/Export/" + fileName);
                using (FileStream fs = System.IO.File.OpenWrite(filePath))
                {
                    book.Write(fs);//向打开的这个xls文件中写入并保存。
                }
                return(Json(new TipMessage()
                {
                    Status = true, MsgText = "导出成功!", Url = Url.Action("DownLoadFile", "FileHandler", new { path = filePath, content = "application/ms-excel" })
                }, JsonRequestBehavior.DenyGet));
            }
            else
            {
                return(Json(new TipMessage()
                {
                    Status = false, MsgText = "暂无会员卡记录!"
                }, JsonRequestBehavior.DenyGet));
            }
        }
Exemplo n.º 22
0
        public FileResult ExportData(string state, string key)
        {
            string _title = "运行管理.xls";

            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            ICellStyle cellstyle            = book.CreateCellStyle();

            cellstyle.VerticalAlignment = VerticalAlignment.Center;
            cellstyle.Alignment         = HorizontalAlignment.Center;
            SetCellRangeAddress(sheet1, 0, 1, 0, 0);
            SetCellRangeAddress(sheet1, 0, 1, 1, 1);
            SetCellRangeAddress(sheet1, 0, 1, 2, 2);
            SetCellRangeAddress(sheet1, 0, 1, 3, 3);
            SetCellRangeAddress(sheet1, 0, 1, 4, 4);
            SetCellRangeAddress(sheet1, 0, 1, 5, 5);
            SetCellRangeAddress(sheet1, 0, 0, 6, 8);
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            NPOI.SS.UserModel.IRow row2 = sheet1.CreateRow(1);
            SetStyle(cellstyle, row1, 0, "盒子编号");
            SetStyle(cellstyle, row1, 1, "状态");
            SetStyle(cellstyle, row1, 2, "省\\直辖市");
            SetStyle(cellstyle, row1, 3, "领用人");
            SetStyle(cellstyle, row1, 4, "学校");
            SetStyle(cellstyle, row1, 5, "首次回执");
            SetStyle(cellstyle, row1, 6, "版本");
            SetStyle(cellstyle, row2, 6, "英语");
            SetStyle(cellstyle, row2, 7, "数学");
            SetStyle(cellstyle, row2, 8, "语文");

            List <VM_BoxRunStat_Index_Grid> _list = new List <VM_BoxRunStat_Index_Grid>();

            using (var db = new box_omsEntities())
            {
                var query = db.box_good.OrderBy(o => o.Code).AsQueryable();

                if (!string.IsNullOrWhiteSpace(key))
                {
                    if (System.Text.RegularExpressions.Regex.IsMatch(key, @"^[0-9]+$"))
                    {
                        int _id = Convert.ToInt32(key);
                        query = query.Where(w => w.BoxId == _id);
                    }
                    else
                    {
                        query = query.Where(w => w.SchoolName.Contains(key) || w.UseUserName.Contains(key));
                    }
                }

                if (!string.IsNullOrWhiteSpace(state))
                {
                    int _state = Convert.ToInt32(state);
                    query = query.Where(w => w.State == _state);
                }
                _list = query.Select(s => new VM_BoxRunStat_Index_Grid
                {
                    BoxId        = s.BoxId,
                    SchoolName   = s.SchoolName,
                    FirstRunTime = s.FirstRunTime,
                    UseUserName  = s.UseUserName,
                    State        = s.State
                }).ToList();
            }
            if (_list.Count > 0)
            {
                for (int i = 0; i < _list.Count; i++)
                {
                    NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 2);
                    string _state = string.Empty;
                    switch (_list[i].State)
                    {
                    case 1:
                        _state = "库存";
                        break;

                    case 2:
                        _state = "已售";
                        break;

                    case 3:
                        _state = "领用";
                        break;
                    }
                    string _FirstRunTime = _list[i].FirstRunTime == null ? "" : ((DateTime)_list[i].FirstRunTime).ToString("yyyy-MM-dd HH:mm:ss");
                    rowtemp.CreateCell(0).SetCellValue(_list[i].BoxId);
                    rowtemp.CreateCell(1).SetCellValue(_state);
                    rowtemp.CreateCell(2).SetCellValue(BoxOms.Web.BLL.BoxGoodBLL.BackPrName(_list[i].BoxId));
                    rowtemp.CreateCell(3).SetCellValue(_list[i].UseUserName);
                    rowtemp.CreateCell(4).SetCellValue(_list[i].SchoolName);
                    rowtemp.CreateCell(5).SetCellValue(_FirstRunTime);
                    rowtemp.CreateCell(6).SetCellValue(BoxOms.Web.BLL.BoxGoodBLL.BackEditionName(_list[i].BoxId, 3));
                    rowtemp.CreateCell(7).SetCellValue(BoxOms.Web.BLL.BoxGoodBLL.BackEditionName(_list[i].BoxId, 2));
                    rowtemp.CreateCell(8).SetCellValue(BoxOms.Web.BLL.BoxGoodBLL.BackEditionName(_list[i].BoxId, 1));
                }
            }

            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return(File(ms, "application/vnd.ms-excel", _title));
        }
Exemplo n.º 23
0
        protected void ExportButton_Click(object sender, EventArgs e)
        {
            //if (Cookies.Admin.GetAdminName(true).ToLower() != "admin") {
            //    //只有admin才有权限导出会员数据
            //    ScriptHelper.Alert("没有权限");
            //}
            //else
            //{
            UserSearchInfo userSearch = new UserSearchInfo();

            userSearch.UserName          = RequestHelper.GetQueryString <string>("UserName");
            userSearch.Mobile            = RequestHelper.GetQueryString <string>("Mobile");
            userSearch.Status            = RequestHelper.GetQueryString <int>("Status");
            userSearch.StartRegisterDate = RequestHelper.GetQueryString <DateTime>("StartRegisterDate");
            userSearch.EndRegisterDate   = ShopCommon.SearchEndDate(RequestHelper.GetQueryString <DateTime>("EndRegisterDate"));
            UserName.Text          = userSearch.UserName;
            Mobile.Text            = userSearch.Mobile;
            StartRegisterDate.Text = RequestHelper.GetQueryString <string>("StartRegisterDate");
            EndRegisterDate.Text   = RequestHelper.GetQueryString <string>("EndRegisterDate");

            status = userSearch.Status;

            var userList = UserBLL.SearchList(new UserSearchInfo
            {
                UserName = userSearch.UserName,
                CardNo   = userSearch.CardNo,
                Status   = userSearch.Status
            });
            var data = userList.Take(1000).ToList();

            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("Sheet1");
            sheet.DefaultColumnWidth = 18;
            sheet.CreateFreezePane(0, 1, 0, 1);

            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            row.Height = 20 * 20;
            row.CreateCell(0).SetCellValue("用户名");
            row.CreateCell(1).SetCellValue("性别");
            row.CreateCell(2).SetCellValue("手机");
            row.CreateCell(3).SetCellValue("邮箱");
            row.CreateCell(4).SetCellValue("固定电话");
            row.CreateCell(5).SetCellValue("QQ");
            row.CreateCell(6).SetCellValue("生日");
            row.CreateCell(7).SetCellValue("所在地");
            row.CreateCell(8).SetCellValue("注册时间");
            row.CreateCell(9).SetCellValue("登录次数");
            row.CreateCell(10).SetCellValue("最近登录");
            row.CreateCell(11).SetCellValue("会员状态");

            //设置表头格式
            var headFont = book.CreateFont();

            headFont.Boldweight         = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            headFont.FontHeightInPoints = 10;
            var headStyle = book.CreateCellStyle();

            headStyle.SetFont(headFont);
            headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            headStyle.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            foreach (var cell in row.Cells)
            {
                cell.CellStyle = headStyle;
            }
            foreach (var entity in data)
            {
                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(data.IndexOf(entity) + 1);
                dataRow.CreateCell(0).SetCellValue(entity.UserName);
                dataRow.CreateCell(1).SetCellValue(entity.Sex == 1 ? "男" : entity.Sex == 2 ? "女" : "保密");
                dataRow.CreateCell(2).SetCellValue(entity.Mobile);
                dataRow.CreateCell(3).SetCellValue(entity.Mobile);
                dataRow.CreateCell(4).SetCellValue(entity.Tel);
                dataRow.CreateCell(5).SetCellValue(entity.QQ);
                dataRow.CreateCell(6).SetCellValue(entity.Birthday);
                dataRow.CreateCell(7).SetCellValue(RegionBLL.ReadCityName(entity.RegionId) + entity.Address);
                dataRow.CreateCell(8).SetCellValue(entity.RegisterDate.ToString());
                dataRow.CreateCell(9).SetCellValue(entity.LoginTimes);
                dataRow.CreateCell(10).SetCellValue(entity.LastLoginDate.ToString());
                dataRow.CreateCell(11).SetCellValue(entity.Status == 1 ? "未验证" : entity.Status == 2 ? "正常" : "冻结");
                var style = book.CreateCellStyle();
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                foreach (var cell in dataRow.Cells)
                {
                    cell.CellStyle = style;
                }
            }

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
            Response.End();
            //}
        }
Exemplo n.º 24
0
        protected void btnExport_Click(object sender, EventArgs e)
        {
            int year = Common.St.ToInt32(selYear.Value);
            var list = DAL.WorkPlanRule.Get();


            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_3.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
            NPOI.SS.UserModel.ISheet         sheet = book.GetSheet("项目月测试次数");

            NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
            style.BorderBottom        = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop           = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft          = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight         = NPOI.SS.UserModel.BorderStyle.Thin;
            style.WrapText            = true;
            style.VerticalAlignment   = NPOI.SS.UserModel.VerticalAlignment.Center;
            style.Alignment           = NPOI.SS.UserModel.HorizontalAlignment.Center;
            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;
            style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;
            style.FillPattern         = NPOI.SS.UserModel.FillPattern.AltBars;

            NPOI.SS.UserModel.ICellStyle style2 = book.CreateCellStyle();
            style2.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.WrapText          = true;
            style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            style2.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Center;
            // 内容
            int i = 0;
            int a1, a2, a3, a4, a11 = 0, a22 = 0, a33 = 0, a44 = 0;
            var userlist = DAL.UserRule.Get();

            for (int j = 1; j <= 12; j++)
            {
                a3 = list.Where(a => a.State == 2 && a.PublishTime.Year == year && a.PublishTime.Month == j).Count();                           //上线次数
                a4 = list.Where(a => a.State == 2 && a.PublishTime.Year == year && a.PublishTime.Month == j).GroupBy(a => a.ProjectId).Count(); //上线项目数
                string t = year + "-" + (j + 1) + "-1";
                if (j == 12)
                {
                    t = (year + 1) + "-1-1";
                }
                a1 = list.Where(a => (a.State == 1 && a.RealStartTime < DateTime.Parse(t)) || (a.State == 2 && (!(a.RealStartTime >= DateTime.Parse(t) || a.RealEndTime < DateTime.Parse(year + "-" + j + "-1"))))).Count(); //工单数 项目数

                if (year == DateTime.Today.Year && j > DateTime.Today.Month)
                {
                    a1 = 0;
                    a2 = 0;
                }
                else
                {
                    a2 = userlist.Where(a => a.Status == 1 || a.LeaveTime.Year * 12 + a.LeaveTime.Month > year * 12 + j).Count(); //测试用户数
                }

                //第一行
                NPOI.SS.UserModel.IRow  row0   = sheet.CreateRow(i++);
                NPOI.SS.UserModel.ICell cell00 = row0.CreateCell(0);
                cell00.CellStyle = style;
                cell00.SetCellValue(GetMonthName(j));
                NPOI.SS.UserModel.ICell cell01 = row0.CreateCell(1);
                cell01.CellStyle = style;
                NPOI.SS.UserModel.ICell cell02 = row0.CreateCell(2);
                cell02.CellStyle = style;
                NPOI.SS.UserModel.ICell cell03 = row0.CreateCell(3);
                cell03.CellStyle = style;
                NPOI.SS.UserModel.ICell cell04 = row0.CreateCell(4);
                cell04.CellStyle = style;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i - 1, 0, 4));
                //第二行
                NPOI.SS.UserModel.IRow  row1   = sheet.CreateRow(i++);
                NPOI.SS.UserModel.ICell cell10 = row1.CreateCell(0);
                cell10.CellStyle = style;
                cell10.SetCellValue("工单数");
                NPOI.SS.UserModel.ICell cell11 = row1.CreateCell(1);
                cell11.CellStyle = style;
                cell11.SetCellValue("项目数");
                NPOI.SS.UserModel.ICell cell12 = row1.CreateCell(2);
                cell12.CellStyle = style;
                cell12.SetCellValue("测试人数");
                NPOI.SS.UserModel.ICell cell13 = row1.CreateCell(3);
                cell13.CellStyle = style;
                cell13.SetCellValue("上线次数");
                NPOI.SS.UserModel.ICell cell14 = row1.CreateCell(4);
                cell14.CellStyle = style;
                cell14.SetCellValue("上线项目数");
                //第三行
                NPOI.SS.UserModel.IRow  row2   = sheet.CreateRow(i++);
                NPOI.SS.UserModel.ICell cell20 = row2.CreateCell(0);
                cell20.CellStyle = style2;
                cell20.SetCellValue(a1);
                NPOI.SS.UserModel.ICell cell21 = row2.CreateCell(1);
                cell21.CellStyle = style2;
                cell21.SetCellValue(a1);
                NPOI.SS.UserModel.ICell cell22 = row2.CreateCell(2);
                cell22.CellStyle = style2;
                cell22.SetCellValue(a2);
                NPOI.SS.UserModel.ICell cell23 = row2.CreateCell(3);
                cell23.CellStyle = style2;
                cell23.SetCellValue(a3);
                NPOI.SS.UserModel.ICell cell24 = row2.CreateCell(4);
                cell24.CellStyle = style2;
                cell24.SetCellValue(a4);
                i++;
                a11 += a1; a22 += a2; a33 += a3; a44 += a4;
            }

            //第一行
            NPOI.SS.UserModel.IRow  row4   = sheet.CreateRow(i++);
            NPOI.SS.UserModel.ICell cell40 = row4.CreateCell(0);
            cell40.CellStyle = style;
            cell40.SetCellValue(year + "年总和统计");
            NPOI.SS.UserModel.ICell cell41 = row4.CreateCell(1);
            cell41.CellStyle = style;
            NPOI.SS.UserModel.ICell cell42 = row4.CreateCell(2);
            cell42.CellStyle = style;
            NPOI.SS.UserModel.ICell cell43 = row4.CreateCell(3);
            cell43.CellStyle = style;
            NPOI.SS.UserModel.ICell cell44 = row4.CreateCell(4);
            cell44.CellStyle = style;
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i - 1, 0, 4));
            //第二行
            NPOI.SS.UserModel.IRow  row5   = sheet.CreateRow(i++);
            NPOI.SS.UserModel.ICell cell50 = row5.CreateCell(0);
            cell50.CellStyle = style;
            cell50.SetCellValue("工单数");
            NPOI.SS.UserModel.ICell cell51 = row5.CreateCell(1);
            cell51.CellStyle = style;
            cell51.SetCellValue("项目数");
            NPOI.SS.UserModel.ICell cell52 = row5.CreateCell(2);
            cell52.CellStyle = style;
            cell52.SetCellValue("测试人数");
            NPOI.SS.UserModel.ICell cell53 = row5.CreateCell(3);
            cell53.CellStyle = style;
            cell53.SetCellValue("上线次数");
            NPOI.SS.UserModel.ICell cell54 = row5.CreateCell(4);
            cell54.CellStyle = style;
            cell54.SetCellValue("上线项目数");
            //第三行

            NPOI.SS.UserModel.IRow  row6   = sheet.CreateRow(i++);
            NPOI.SS.UserModel.ICell cell60 = row6.CreateCell(0);
            cell60.CellStyle = style2;
            cell60.SetCellValue(a11);
            NPOI.SS.UserModel.ICell cell61 = row6.CreateCell(1);
            cell61.CellStyle = style2;
            cell61.SetCellValue(a11);
            NPOI.SS.UserModel.ICell cell62 = row6.CreateCell(2);
            cell62.CellStyle = style2;
            cell62.SetCellValue(a22);
            NPOI.SS.UserModel.ICell cell63 = row6.CreateCell(3);
            cell63.CellStyle = style2;
            cell63.SetCellValue(a33);
            NPOI.SS.UserModel.ICell cell64 = row6.CreateCell(4);
            cell64.CellStyle = style2;
            cell64.SetCellValue(a44);


            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("项目月测试次数", System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }
    private void GenerateReport(ArrayList empList, DateTime PeriodFrom, DateTime PeriodTo, string SortBy)
    {
        DataTable dataTable = new DataTable();

        dataTable.Columns.Add("EmpID", typeof(int));
        dataTable.Columns.Add("EmpNo", typeof(string));
        dataTable.Columns.Add("EmpName", typeof(string));
        dataTable.Columns.Add("EmpAlias", typeof(string));
        dataTable.Columns.Add("Company", typeof(string));

        DBFilter  hierarchyLevelFilter    = new DBFilter();
        Hashtable hierarchyLevelHashTable = new Hashtable();

        hierarchyLevelFilter.add("HLevelSeqNo", true);
        ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);

        foreach (EHierarchyLevel hlevel in hierarchyLevelList)
        {
            dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
            hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
        }
        dataTable.Columns.Add("Position", typeof(string));

        dataTable.Columns.Add("TrainingSeminarID", typeof(int));
        dataTable.Columns.Add("TrainingCourseName", typeof(string));
        dataTable.Columns.Add("TrainingSeminarDesc", typeof(string));
        dataTable.Columns.Add("TrainingSeminarDateFrom", typeof(DateTime));
        dataTable.Columns.Add("TrainingSeminarDateTo", typeof(DateTime));
        dataTable.Columns.Add("TrainingSeminarDuration", typeof(double));
        dataTable.Columns.Add("TrainingSeminarDurationUnit", typeof(string));
        dataTable.Columns.Add("TrainingSeminarTrainer", typeof(string));


        foreach (EEmpPersonalInfo empInfo in empList)
        {
            if (EEmpPersonalInfo.db.select(dbConn, empInfo))
            {
                EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AppUtils.ServerDateTime().Date, empInfo.EmpID);

                ECompany  company          = new ECompany();
                EPosition position         = new EPosition();
                ArrayList empHierarchyList = new ArrayList();
                if (empPos != null)
                {
                    company.CompanyID = empPos.CompanyID;
                    ECompany.db.select(dbConn, company);
                    //row["Company"] = company.CompanyCode;
                    DBFilter empHierarchyFilter = new DBFilter();
                    empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
                    empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
                    //foreach (EEmpHierarchy empHierarchy in empHierarchyList)
                    //{
                    //    EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
                    //    if (hierarchyLevel != null)
                    //    {
                    //        EHierarchyElement hierarchyElement = new EHierarchyElement();
                    //        hierarchyElement.HElementID = empHierarchy.HElementID;
                    //        if (EHierarchyElement.db.select(dbConn, hierarchyElement))
                    //            row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
                    //    }
                    //}

                    position.PositionID = empPos.PositionID;
                    EPosition.db.select(dbConn, position);
                }

                DBFilter empTrainingSeminar = new DBFilter();
                empTrainingSeminar.add(new Match("EmpID", empInfo.EmpID));

                OR orTrainingCourse = null;
                foreach (ListItem item in TrainingCourseList.Items)
                {
                    if (item.Selected)
                    {
                        if (orTrainingCourse == null)
                        {
                            orTrainingCourse = new OR();
                        }
                        orTrainingCourse.add(new Match("te.TrainingCourseID", item.Value));
                    }
                }
                DBFilter trainingSeminarFilter = new DBFilter();
                if (!PeriodFrom.Ticks.Equals(0))
                {
                    trainingSeminarFilter.add(new Match("TrainingSeminarDateFrom", ">=", PeriodFrom));
                }
                if (!PeriodTo.Ticks.Equals(0))
                {
                    trainingSeminarFilter.add(new Match("TrainingSeminarDateTo", "<=", PeriodTo));
                }
                if (orTrainingCourse != null)
                {
                    trainingSeminarFilter.add(orTrainingCourse);
                }
                empTrainingSeminar.add(new IN("TrainingSeminarID", "Select TrainingSeminarID from " + ETrainingSeminar.db.dbclass.tableName + " te", trainingSeminarFilter));
                ArrayList empTrainingSeminarList = EEmpTrainingEnroll.db.select(dbConn, empTrainingSeminar);
                foreach (EEmpTrainingEnroll empTrainingEnroll in empTrainingSeminarList)
                {
                    ETrainingSeminar trainingSeminar = new ETrainingSeminar();
                    trainingSeminar.TrainingSeminarID = empTrainingEnroll.TrainingSeminarID;
                    if (ETrainingSeminar.db.select(dbConn, trainingSeminar))
                    {
                        DataRow row = dataTable.NewRow();
                        row["EmpID"]    = empInfo.EmpID;
                        row["EmpNo"]    = empInfo.EmpNo;
                        row["EmpName"]  = empInfo.EmpEngFullName;
                        row["EmpAlias"] = empInfo.EmpAlias;
                        row["Company"]  = company.CompanyCode;
                        foreach (EEmpHierarchy empHierarchy in empHierarchyList)
                        {
                            EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
                            if (hierarchyLevel != null)
                            {
                                EHierarchyElement hierarchyElement = new EHierarchyElement();
                                hierarchyElement.HElementID = empHierarchy.HElementID;
                                if (EHierarchyElement.db.select(dbConn, hierarchyElement))
                                {
                                    row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
                                }
                            }
                        }
                        row["Position"] = position.PositionDesc;

                        row["TrainingSeminarID"] = trainingSeminar.TrainingSeminarID;
                        ETrainingCourse trainingCourse = new ETrainingCourse();
                        trainingCourse.TrainingCourseID = trainingSeminar.TrainingCourseID;
                        if (ETrainingCourse.db.select(dbConn, trainingCourse))
                        {
                            row["TrainingCourseName"] = trainingCourse.TrainingCourseName;
                        }
                        else
                        {
                            row["TrainingCourseName"] = string.Empty;
                        }

                        row["TrainingSeminarDesc"]     = trainingSeminar.TrainingSeminarDesc == null ? string.Empty : trainingSeminar.TrainingSeminarDesc;
                        row["TrainingSeminarDateFrom"] = trainingSeminar.TrainingSeminarDateFrom;
                        row["TrainingSeminarDateTo"]   = trainingSeminar.TrainingSeminarDateTo;
                        row["TrainingSeminarDuration"] = trainingSeminar.TrainingSeminarDuration;
                        if (trainingSeminar.TrainingSeminarDurationUnit.Equals("H"))
                        {
                            row["TrainingSeminarDurationUnit"] = "Hour(s)";
                        }
                        else
                        {
                            row["TrainingSeminarDurationUnit"] = trainingSeminar.TrainingSeminarDurationUnit;
                        }

                        row["TrainingSeminarTrainer"] = trainingSeminar.TrainingSeminarTrainer;

                        dataTable.Rows.Add(row);
                    }
                }
            }
        }

        //org.in2bits.MyXls.XlsDocument document = new org.in2bits.MyXls.XlsDocument();
        //org.in2bits.MyXls.Worksheet worksheet = document.Workbook.Worksheets.Add("training report");

        NPOI.HSSF.UserModel.HSSFWorkbook workbook  = new NPOI.HSSF.UserModel.HSSFWorkbook();
        NPOI.HSSF.UserModel.HSSFSheet    worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("training report");

        NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
        boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;

        NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        reportHeaderStyle.SetFont(boldFont);

        NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        columnHeaderStyleCenter.SetFont(boldFont);
        columnHeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;

        //NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        //numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");

        int rowCount = 0;

        //worksheet.Cells.Add(rowCount, (ushort)1, "Training Report").Font.Bold = true;
        NPOI.HSSF.UserModel.HSSFCell reportHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)worksheet.CreateRow(rowCount).CreateCell(0);
        reportHeaderCell.SetCellValue("Training Report");
        reportHeaderCell.CellStyle = reportHeaderStyle;
        rowCount++;

        if (!PeriodFrom.Ticks.Equals(0) && !PeriodTo.Ticks.Equals(0))
        {
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
            worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
            rowCount++;
        }
        else if (!PeriodTo.Ticks.Equals(0))
        {
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
            worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
            rowCount++;
        }
        else if (!PeriodFrom.Ticks.Equals(0))
        {
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "From: " + PeriodFrom.ToString("dd/MM/yyyy"));
            worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("From: " + PeriodFrom.ToString("dd/MM/yyyy"));
            rowCount++;
        }

        if (SortBy.Equals("Date", StringComparison.CurrentCultureIgnoreCase))
        {
            DataView dataView = new DataView(dataTable);
            dataView.Sort = "TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName, EmpNo";
            DataTable sortedTable = dataView.ToTable();
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "Training Date").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)2, "Course Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)3, "Description").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
            //worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
            //worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
            //worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
            //worksheet.Cells.Merge(rowCount, rowCount, 4, 5);
            NPOI.HSSF.UserModel.HSSFRow  columnHeaderRow  = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
            NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
            columnHeaderCell.SetCellValue("Training Date");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
            columnHeaderCell.SetCellValue("Course Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
            columnHeaderCell.SetCellValue("Description");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
            columnHeaderCell.SetCellValue("Duration");
            columnHeaderCell.CellStyle = columnHeaderStyleCenter;
            worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
            columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
            columnHeaderCell.SetCellValue("Trainer");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
            columnHeaderCell.SetCellValue("Employee No.");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
            columnHeaderCell.SetCellValue("Employee Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
            columnHeaderCell.SetCellValue("Alias");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
            columnHeaderCell.SetCellValue("Position");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
            columnHeaderCell.SetCellValue("Company");
            columnHeaderCell.CellStyle = reportHeaderStyle;

            int colCount = 10;
            foreach (EHierarchyLevel hlevel in hierarchyLevelList)
            {
                colCount++;
                //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
                columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
                columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
                columnHeaderCell.CellStyle = reportHeaderStyle;
            }

            int currentTrainingSeminarID = 0;
            foreach (DataRow row in sortedTable.Rows)
            {
                rowCount++;
                NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);

                if (!currentTrainingSeminarID.Equals((int)row["TrainingSeminarID"]))
                {
                    if (row["TrainingSeminarDateFrom"] != DBNull.Value)
                    {
                        DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
                        DateTime trainingTo   = ((DateTime)row["TrainingSeminarDateTo"]);
                        if (trainingFrom.Equals(trainingTo))
                        {
                            //worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy"));
                            detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
                        }
                        else
                        {
                            //worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                            detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                        }
                    }
                }
                currentTrainingSeminarID = ((int)row["TrainingSeminarID"]);

                //worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingCourseName"]);
                //worksheet.Cells.Add(rowCount, (ushort)3, row["TrainingSeminarDesc"]);
                //worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
                //worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
                //worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
                //worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
                //worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
                //worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
                //worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
                //worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);

                detailRow.CreateCell(1).SetCellValue(row["TrainingCourseName"].ToString());
                detailRow.CreateCell(2).SetCellValue(row["TrainingSeminarDesc"].ToString());
                detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
                //detailRow.GetCell(3).CellStyle = numericStyle;
                detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
                detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
                detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
                detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
                detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
                detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
                detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());

                colCount = 10;
                foreach (EHierarchyLevel hlevel in hierarchyLevelList)
                {
                    colCount++;
                    if (row[hlevel.HLevelDesc] != DBNull.Value)
                    {
                        //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
                        detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
                    }
                }
            }
        }
        else if (SortBy.Equals("Position", StringComparison.CurrentCultureIgnoreCase))
        {
            DataView dataView = new DataView(dataTable);
            dataView.Sort = "Position, EmpNo, TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName ";
            DataTable sortedTable = dataView.ToTable();
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "Position").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)2, "Employee No.").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)3, "Employee Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)4, "Alias").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)5, "Company").Font.Bold = true;
            NPOI.HSSF.UserModel.HSSFRow  columnHeaderRow  = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
            NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
            columnHeaderCell.SetCellValue("Position");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
            columnHeaderCell.SetCellValue("Employee No.");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
            columnHeaderCell.SetCellValue("Employee Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
            columnHeaderCell.SetCellValue("Alias");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(4);
            columnHeaderCell.SetCellValue("Company");
            columnHeaderCell.CellStyle = reportHeaderStyle;

            int colCount = 4;
            foreach (EHierarchyLevel hlevel in hierarchyLevelList)
            {
                colCount++;
                //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
                columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
                columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
                columnHeaderCell.CellStyle = reportHeaderStyle;
            }

            //worksheet.Cells.Add(rowCount, (ushort)colCount + 1, "Course Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)colCount + 2, "Description").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, "Training Date").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)colCount + 4, "Duration").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)colCount + 5, string.Empty);
            //worksheet.Cells.Merge(rowCount, rowCount, colCount + 4, colCount + 5);
            //worksheet.Rows[rowCount].GetCell((ushort)(colCount + 4)).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
            //worksheet.Cells.Add(rowCount, (ushort)colCount + 6, "Trainer").Font.Bold = true;

            columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 1);
            columnHeaderCell.SetCellValue("Course Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 2);
            columnHeaderCell.SetCellValue("Description");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 3);
            columnHeaderCell.SetCellValue("Training Date");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 4);
            columnHeaderCell.SetCellValue("Duration");
            columnHeaderCell.CellStyle = columnHeaderStyleCenter;
            worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, colCount + 4, colCount + 5));
            columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 6);
            columnHeaderCell.SetCellValue("Trainer");
            columnHeaderCell.CellStyle = reportHeaderStyle;

            int currentEmpID = 0;
            foreach (DataRow row in sortedTable.Rows)
            {
                rowCount++;
                NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);

                if (!currentEmpID.Equals((int)row["EmpID"]))
                {
                    //worksheet.Cells.Add(rowCount, (ushort)1, row["Position"]);
                    //worksheet.Cells.Add(rowCount, (ushort)2, row["EmpNo"]);
                    //worksheet.Cells.Add(rowCount, (ushort)3, row["EmpName"]);
                    //worksheet.Cells.Add(rowCount, (ushort)4, row["EmpAlias"]);
                    //worksheet.Cells.Add(rowCount, (ushort)5, row["Company"]);
                    detailRow.CreateCell(0).SetCellValue(row["Position"].ToString());
                    detailRow.CreateCell(1).SetCellValue(row["EmpNo"].ToString());
                    detailRow.CreateCell(2).SetCellValue(row["EmpName"].ToString());
                    detailRow.CreateCell(3).SetCellValue(row["EmpAlias"].ToString());
                    detailRow.CreateCell(4).SetCellValue(row["Company"].ToString());


                    colCount = 4;
                    foreach (EHierarchyLevel hlevel in hierarchyLevelList)
                    {
                        colCount++;
                        if (row[hlevel.HLevelDesc] != DBNull.Value)
                        {
                            //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
                            detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
                        }
                    }
                }
                currentEmpID = ((int)row["EmpID"]);

                //worksheet.Cells.Add(rowCount, (ushort)colCount + 1, row["TrainingCourseName"]);
                //worksheet.Cells.Add(rowCount, (ushort)colCount + 2, row["TrainingSeminarDesc"]);
                detailRow.CreateCell(colCount + 1).SetCellValue(row["TrainingCourseName"].ToString());
                detailRow.CreateCell(colCount + 2).SetCellValue(row["TrainingSeminarDesc"].ToString());
                if (row["TrainingSeminarDateFrom"] != DBNull.Value)
                {
                    DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
                    DateTime trainingTo   = ((DateTime)row["TrainingSeminarDateTo"]);
                    if (trainingFrom.Equals(trainingTo))
                    {
                        //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy"));
                        detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
                    }
                    else
                    {
                        //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                        detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                    }
                }

                //worksheet.Cells.Add(rowCount, (ushort)colCount + 4, row["TrainingSeminarDuration"]);
                //worksheet.Cells.Add(rowCount, (ushort)colCount + 5, row["TrainingSeminarDurationUnit"]);
                //worksheet.Cells.Add(rowCount, (ushort)colCount + 6, row["TrainingSeminarTrainer"]);
                detailRow.CreateCell(colCount + 4).SetCellValue((double)row["TrainingSeminarDuration"]);
                //detailRow.GetCell(colCount + 4).CellStyle = numericStyle;
                detailRow.CreateCell(colCount + 5).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
                detailRow.CreateCell(colCount + 6).SetCellValue(row["TrainingSeminarTrainer"].ToString());
            }
        }
        else if (SortBy.Equals("Course", StringComparison.CurrentCultureIgnoreCase))
        {
            DataView dataView = new DataView(dataTable);
            dataView.Sort = "TrainingCourseName, TrainingSeminarDateFrom, TrainingSeminarDateTo, EmpNo";
            DataTable sortedTable = dataView.ToTable();
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "Course Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)2, "Description").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)3, "Training Date").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
            //worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
            //worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
            //worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
            //worksheet.Cells.Merge(rowCount, rowCount, 4, 5);

            NPOI.HSSF.UserModel.HSSFRow  columnHeaderRow  = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
            NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
            columnHeaderCell.SetCellValue("Course Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
            columnHeaderCell.SetCellValue("Description");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
            columnHeaderCell.SetCellValue("Training Date");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
            columnHeaderCell.SetCellValue("Duration");
            columnHeaderCell.CellStyle = columnHeaderStyleCenter;
            worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
            columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
            columnHeaderCell.SetCellValue("Trainer");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
            columnHeaderCell.SetCellValue("Employee No.");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
            columnHeaderCell.SetCellValue("Employee Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
            columnHeaderCell.SetCellValue("Alias");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
            columnHeaderCell.SetCellValue("Position");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
            columnHeaderCell.SetCellValue("Company");
            columnHeaderCell.CellStyle = reportHeaderStyle;

            int colCount = 10;
            foreach (EHierarchyLevel hlevel in hierarchyLevelList)
            {
                colCount++;
                //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
                columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
                columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
                columnHeaderCell.CellStyle = reportHeaderStyle;
            }

            foreach (DataRow row in sortedTable.Rows)
            {
                rowCount++;

                NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
                //worksheet.Cells.Add(rowCount, (ushort)1, row["TrainingCourseName"]);
                //worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingSeminarDesc"]);
                detailRow.CreateCell(0).SetCellValue(row["TrainingCourseName"].ToString());
                detailRow.CreateCell(1).SetCellValue(row["TrainingSeminarDesc"].ToString());

                if (row["TrainingSeminarDateFrom"] != DBNull.Value)
                {
                    DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
                    DateTime trainingTo   = ((DateTime)row["TrainingSeminarDateTo"]);
                    if (trainingFrom.Equals(trainingTo))
                    {
                        //worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy"));
                        detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
                    }
                    else
                    {
                        //worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                        detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                    }
                }

                //worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
                //worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
                //worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
                //worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
                //worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
                //worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
                //worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
                //worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);
                detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
                //detailRow.GetCell(3).CellStyle = numericStyle;
                detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
                detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
                detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
                detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
                detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
                detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
                detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());

                colCount = 10;
                foreach (EHierarchyLevel hlevel in hierarchyLevelList)
                {
                    colCount++;
                    if (row[hlevel.HLevelDesc] != DBNull.Value)
                    {
                        //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
                        detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
                    }
                }
            }
        }
        string exportFileName = System.IO.Path.GetTempFileName();

        System.IO.File.Delete(exportFileName);
        exportFileName += ".xls";
        //document.FileName = exportFileName;
        //document.Save();
        System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
        workbook.Write(file);
        file.Close();
        string filename = "TrainingReport_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls";

        WebUtils.TransmitFile(Response, exportFileName, filename, true);
        return;
    }
Exemplo n.º 26
0
    private void GenerateExcelReport(DataTable tmpDataTable, string exportFileName)
    {
        int columnCount  = 0;
        int lastRowIndex = 0;

        // Set column style
        NPOI.HSSF.UserModel.HSSFWorkbook   workbook      = new NPOI.HSSF.UserModel.HSSFWorkbook();
        NPOI.HSSF.UserModel.HSSFDataFormat format        = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
        NPOI.HSSF.UserModel.HSSFCellStyle  dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
        dateCellStyle.Alignment  = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
        NPOI.HSSF.UserModel.HSSFSheet     worksheet    = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("AverageCostCentreExport");
        NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        numericStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00");
        numericStyle.Alignment  = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
        NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;

        // Set column width
        worksheet.SetColumnWidth(0, 40 * 256);
        worksheet.SetColumnWidth(2, 20 * 256);
        worksheet.SetColumnWidth(5, 15 * 256);
        worksheet.SetColumnWidth(7, 15 * 256);
        worksheet.SetColumnWidth(8, 15 * 256);
        worksheet.SetColumnWidth(9, 15 * 256);
        worksheet.SetColumnWidth(10, 12 * 256);

        // Set column title
        NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex);
        headerRow.CreateCell(0).SetCellValue("Average Cost Centre Export");
        headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 1);
        headerRow.CreateCell(0).SetCellValue(peroid);
        headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 3);

        foreach (DataColumn headercolumn in tmpDataTable.Columns)
        {
            NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount);
            cell.SetCellValue(headercolumn.ColumnName);
            if (columnCount == 11)
            {
                cell.CellStyle = style;
            }
            columnCount++;
        }

        // Set value for every row
        foreach (DataRow row in tmpDataTable.Rows)
        {
            NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 4);

            detailRow.CreateCell(0).SetCellValue(row[FIELD_COMPANY].ToString());
            detailRow.CreateCell(1).SetCellValue(row[FIELD_DIVISION].ToString());
            detailRow.CreateCell(2).SetCellValue(row[FIELD_DEPARTMENT].ToString());
            detailRow.CreateCell(3).SetCellValue(row[FIELD_SECTION].ToString());
            detailRow.CreateCell(4).SetCellValue(row[FIELD_EMP_NO].ToString());
            detailRow.CreateCell(5).SetCellValue(row[FIELD_EMP_NAME].ToString());
            detailRow.CreateCell(6).SetCellValue(row[FIELD_ALIAS].ToString());
            detailRow.CreateCell(7).SetCellValue(row[FIELD_POSITION].ToString());

            NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8);
            cell.SetCellValue((DateTime)row[FIELD_FROM]);
            cell.CellStyle = dateCellStyle;

            cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9);
            cell.SetCellValue((DateTime)row[FIELD_TO]);
            cell.CellStyle = dateCellStyle;

            detailRow.CreateCell(10).SetCellValue(row[FIELD_COST_CENTER].ToString());
            cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11);
            cell.SetCellValue((double)row[FIELD_PERCENTAGE]);
            cell.CellStyle = numericStyle;

            lastRowIndex++;
        }

        System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
        workbook.Write(file);
        file.Close();
    }
Exemplo n.º 27
0
        protected void btnExport_Click(object sender, EventArgs e)
        {
            int year = Common.St.ToInt32(selYear.Value);
            var m    = 12;

            if (year == DateTime.Today.Year)
            {
                m = DateTime.Today.Month;
            }
            var list = DAL.WorkPlanRule.Get().Where(a => a.NeederId > 0 && a.Needer.Status == 1).GroupBy(a => a.NeederId).Select(a =>
            {
                return(new
                {
                    Name = a.First().Needer.RealName,
                    M1 = m < 1 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-2-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-2-1") || b.RealEndTime < DateTime.Parse(year + "-1-1"))))).Count(),
                    M2 = m < 2 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-3-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-3-1") || b.RealEndTime < DateTime.Parse(year + "-2-1"))))).Count(),
                    M3 = m < 3 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-4-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-4-1") || b.RealEndTime < DateTime.Parse(year + "-3-1"))))).Count(),
                    M4 = m < 4 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-5-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-5-1") || b.RealEndTime < DateTime.Parse(year + "-4-1"))))).Count(),
                    M5 = m < 5 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-6-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-6-1") || b.RealEndTime < DateTime.Parse(year + "-5-1"))))).Count(),
                    M6 = m < 6 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-7-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-7-1") || b.RealEndTime < DateTime.Parse(year + "-6-1"))))).Count(),
                    M7 = m < 7 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-8-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-8-1") || b.RealEndTime < DateTime.Parse(year + "-7-1"))))).Count(),
                    M8 = m < 8 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-9-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-9-1") || b.RealEndTime < DateTime.Parse(year + "-8-1"))))).Count(),
                    M9 = m < 9 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-10-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-10-1") || b.RealEndTime < DateTime.Parse(year + "-9-1"))))).Count(),
                    M10 = m < 10 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-11-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-11-1") || b.RealEndTime < DateTime.Parse(year + "-10-1"))))).Count(),
                    M11 = m < 11 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-12-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-12-1") || b.RealEndTime < DateTime.Parse(year + "-11-1"))))).Count(),
                    M12 = m < 12 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse((year + 1) + "-1-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse((year + 1) + "-1-1") || b.RealEndTime < DateTime.Parse(year + "-12-1"))))).Count()
                });
            });


            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_4.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
            NPOI.SS.UserModel.ISheet         sheet = book.GetSheet("测试人员负责项目数统计");

            NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
            style.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            style.WrapText          = true;
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            style.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Center;
            // 内容
            int i = 1;

            foreach (var o in list)
            {
                NPOI.SS.UserModel.IRow  row   = sheet.CreateRow(i);
                NPOI.SS.UserModel.ICell cell0 = row.CreateCell(0);
                cell0.CellStyle = style;
                cell0.SetCellValue(o.Name);
                NPOI.SS.UserModel.ICell cell1 = row.CreateCell(1);
                cell1.CellStyle = style;
                cell1.SetCellValue(o.M1);
                NPOI.SS.UserModel.ICell cell2 = row.CreateCell(2);
                cell2.CellStyle = style;
                cell2.SetCellValue(o.M2);
                NPOI.SS.UserModel.ICell cell3 = row.CreateCell(3);
                cell3.CellStyle = style;
                cell3.SetCellValue(o.M3);
                NPOI.SS.UserModel.ICell cell4 = row.CreateCell(4);
                cell4.CellStyle = style;
                cell4.SetCellValue(o.M4);
                NPOI.SS.UserModel.ICell cell5 = row.CreateCell(5);
                cell5.CellStyle = style;
                cell5.SetCellValue(o.M5);
                NPOI.SS.UserModel.ICell cell6 = row.CreateCell(6);
                cell6.CellStyle = style;
                cell6.SetCellValue(o.M6);
                NPOI.SS.UserModel.ICell cell7 = row.CreateCell(7);
                cell7.CellStyle = style;
                cell7.SetCellValue(o.M7);
                NPOI.SS.UserModel.ICell cell8 = row.CreateCell(8);
                cell8.CellStyle = style;
                cell8.SetCellValue(o.M8);
                NPOI.SS.UserModel.ICell cell9 = row.CreateCell(9);
                cell9.CellStyle = style;
                cell9.SetCellValue(o.M9);
                NPOI.SS.UserModel.ICell cell10 = row.CreateCell(10);
                cell10.CellStyle = style;
                cell10.SetCellValue(o.M10);
                NPOI.SS.UserModel.ICell cell11 = row.CreateCell(11);
                cell11.CellStyle = style;
                cell11.SetCellValue(o.M11);
                NPOI.SS.UserModel.ICell cell12 = row.CreateCell(12);
                cell12.CellStyle = style;
                cell12.SetCellValue(o.M12);
                i++;
            }



            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("测试人员负责项目数统计", System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }
Exemplo n.º 28
0
        /// <summary>
        /// 导出问卷数据
        /// </summary>
        /// <param name="list"></param>
        public string CreateExcel()
        {
            list = (from r in _db.korea_record
                    join emp in _db.korea_employeeMK on new { EId = r.eId.Value } equals new { EId = emp.eId }
                    join ans in
                    (
                        (from a in _db.korea_answer
                         group a by new
            {
                a.rId
            } into g
                         select new
            {
                arid = g.Key.rId.Value,
                answer1 = g.Max(p => (p.qId == 1 ? p.aContent : null)),
                answer2 = g.Max(p => (p.qId == 2 ? p.aContent : null)),
                answer3 = g.Max(p => (p.qId == 3 ? p.aContent : null)),
                answer4 = g.Max(p => (p.qId == 4 ? p.aContent : null)),
                answer5 = g.Max(p => (p.qId == 5 ? p.aContent : null)),
                answer6 = g.Max(p => (p.qId == 6 ? p.aContent : null)),
                answer7 = g.Max(p => (p.qId == 7 ? p.aContent : null)),
                answer8 = g.Max(p => (p.qId == 8 ? p.aContent : null)),
                answer9 = g.Max(p => (p.qId == 9 ? p.aContent : null)),
                answer10 = g.Max(p => (p.qId == 10 ? p.aContent : null)),
                answer11 = g.Max(p => (p.qId == 11 ? p.aContent : null)),
                answer12 = g.Max(p => (p.qId == 12 ? p.aContent : null))
            }))on new { RId = r.rId } equals new { RId = ans.arid }
                    orderby
                    r.rId
                    select new RecordModel
            {
                AnswerId = r.rId,
                AnswerName = emp.eName,
                AnswerNumber = emp.eNumber,
                AnswerPhone = emp.ePhone,
                AnswerDuration = r.rDuration.Value,
                AnswerTime = r.rEndtime.Value,
                AnswerAnswer1 = ans.answer1,
                AnswerAnswer2 = ans.answer2,
                AnswerAnswer3 = ans.answer3,
                AnswerAnswer4 = ans.answer4,
                AnswerAnswer5 = ans.answer5,
                AnswerAnswer6 = ans.answer6,
                AnswerAnswer7 = ans.answer7,
                AnswerAnswer8 = ans.answer8,
                AnswerAnswer9 = ans.answer9,
                AnswerAnswer10 = ans.answer10,
                AnswerAnswer11 = ans.answer11,
                AnswerAnswer12 = ans.answer12,
            }
                    ).ToList();

            if (list != null && list.Count > 0)  //
            {
                try
                {
                    NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
                    NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("问卷数据下载");
                    //
                    NPOI.SS.UserModel.IRow rowHeader = sheet.CreateRow(0);
                    sheet.SetColumnWidth(0, 12 * 256);
                    sheet.SetColumnWidth(1, 10 * 256);
                    sheet.SetColumnWidth(2, 15 * 256);
                    sheet.SetColumnWidth(3, 18 * 256);
                    sheet.SetColumnWidth(4, 20 * 256);
                    sheet.SetColumnWidth(5, 25 * 256);
                    ICellStyle      style       = book.CreateCellStyle();
                    HSSFColor       color       = new HSSFColor.BLACK();
                    FillPatternType fillPattern = FillPatternType.SOLID_FOREGROUND;//灰色背景
                    HSSFColor       backGround  = new HSSFColor.GREY_25_PERCENT();
                    IFont           font        = ExportData.GetFontStyle(book, "宋体", color, 11);
                    //设置单元格的样式:水平对齐居中
                    style = ExportData.GetCellStyle(book, font, fillPattern, backGround, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);


                    ICell cell1 = rowHeader.CreateCell(0);
                    cell1.SetCellValue("姓名");
                    //将新的样式赋给单元格
                    cell1.CellStyle = style;

                    ICell cell2 = rowHeader.CreateCell(1);
                    cell2.SetCellValue("编号");
                    cell2.CellStyle = style;

                    ICell cell3 = rowHeader.CreateCell(2);
                    cell3.SetCellValue("手机号码");
                    cell3.CellStyle = style;

                    ICell cell4 = rowHeader.CreateCell(3);
                    cell4.SetCellValue("第1题");
                    cell4.CellStyle = style;

                    ICell cell5 = rowHeader.CreateCell(4);
                    cell5.SetCellValue("第2题");
                    cell5.CellStyle = style;

                    ICell cell6 = rowHeader.CreateCell(5);
                    cell6.SetCellValue("第3题");
                    cell6.CellStyle = style;

                    ICell cell7 = rowHeader.CreateCell(6);
                    cell7.SetCellValue("第4题");
                    cell7.CellStyle = style;

                    ICell cell8 = rowHeader.CreateCell(7);
                    cell8.SetCellValue("第5题");
                    cell8.CellStyle = style;

                    ICell cell9 = rowHeader.CreateCell(8);
                    cell9.SetCellValue("第6题");
                    cell9.CellStyle = style;

                    ICell cell10 = rowHeader.CreateCell(9);
                    cell10.SetCellValue("第7题");
                    cell10.CellStyle = style;
                    ICell cell11 = rowHeader.CreateCell(10);
                    cell11.SetCellValue("第8题");
                    cell11.CellStyle = style;

                    ICell cell12 = rowHeader.CreateCell(11);
                    cell12.SetCellValue("第9题");
                    cell12.CellStyle = style;

                    ICell cell13 = rowHeader.CreateCell(12);
                    cell13.SetCellValue("第10题");
                    cell13.CellStyle = style;

                    ICell cell14 = rowHeader.CreateCell(13);
                    cell14.SetCellValue("第11题");
                    cell14.CellStyle = style;

                    ICell cell15 = rowHeader.CreateCell(14);
                    cell15.SetCellValue("第12题");
                    cell15.CellStyle = style;

                    ICellStyle cellStyle = book.CreateCellStyle();
                    cellStyle.Alignment         = HorizontalAlignment.CENTER;
                    cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                    IFont rowfont = book.CreateFont();
                    rowfont.FontName           = "宋体";
                    rowfont.FontHeightInPoints = (short)11;
                    rowfont.Color = color.GetIndex();
                    cellStyle.SetFont(rowfont);
                    //
                    NPOI.SS.UserModel.IRow row = null;
                    for (int i = 0; i < list.Count; i++)
                    {
                        RecordModel model = list[i];
                        row = sheet.CreateRow(i + 1);
                        ICell rowcell1 = row.CreateCell(0);
                        rowcell1.SetCellValue(model.AnswerName);
                        rowcell1.CellStyle = cellStyle;
                        ICell rowcell2 = row.CreateCell(1);
                        rowcell2.SetCellValue(model.AnswerNumber);
                        rowcell2.CellStyle = cellStyle;

                        ICell rowcell3 = row.CreateCell(2);
                        rowcell3.SetCellValue(model.AnswerPhone);
                        rowcell3.CellStyle = cellStyle;

                        ICell rowcell4 = row.CreateCell(3);
                        rowcell4.SetCellValue(model.AnswerAnswer1);
                        rowcell4.CellStyle = cellStyle;

                        ICell rowcell5 = row.CreateCell(4);
                        rowcell5.SetCellValue(model.AnswerAnswer2);
                        rowcell5.CellStyle = cellStyle;

                        ICell rowcell6 = row.CreateCell(5);
                        rowcell6.SetCellValue(model.AnswerAnswer3);
                        rowcell6.CellStyle = cellStyle;


                        ICell rowcell7 = row.CreateCell(6);
                        rowcell7.SetCellValue(model.AnswerAnswer4);
                        rowcell7.CellStyle = cellStyle;

                        ICell rowcell8 = row.CreateCell(7);
                        rowcell8.SetCellValue(model.AnswerAnswer5);
                        rowcell8.CellStyle = cellStyle;

                        ICell rowcell9 = row.CreateCell(8);
                        rowcell9.SetCellValue(model.AnswerAnswer6);
                        rowcell9.CellStyle = cellStyle;

                        ICell rowcell10 = row.CreateCell(9);
                        rowcell10.SetCellValue(model.AnswerAnswer7);
                        rowcell10.CellStyle = cellStyle;

                        ICell rowcell11 = row.CreateCell(10);
                        rowcell11.SetCellValue(model.AnswerAnswer8);
                        rowcell11.CellStyle = cellStyle;
                        ICell rowcell12 = row.CreateCell(11);
                        rowcell12.SetCellValue(model.AnswerAnswer9);
                        rowcell12.CellStyle = cellStyle;
                        ICell rowcell13 = row.CreateCell(12);
                        rowcell13.SetCellValue(model.AnswerAnswer10);
                        rowcell13.CellStyle = cellStyle;
                        ICell rowcell14 = row.CreateCell(13);
                        rowcell14.SetCellValue(model.AnswerAnswer11);
                        rowcell14.CellStyle = cellStyle;

                        ICell rowcell15 = row.CreateCell(14);
                        rowcell15.SetCellValue(model.AnswerAnswer12);
                        rowcell15.CellStyle = cellStyle;
                    }
                    //
                    using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
                    {
                        book.Write(ms);

                        //string path = @"C:\Users\DNS\Desktop\导出参会人";
                        //string path = System.Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\数据导出Excel";
                        // path = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\数据导出Excel";
                        string        path     = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Data\\";
                        DirectoryInfo excelDir = new DirectoryInfo(path);
                        //如果路径不存在就创建
                        if (!Directory.Exists(path))
                        {
                            Directory.CreateDirectory(path);
                        }
                        string fileName = "mk_q" + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                        //string strDate = DateTime.Now.ToString("yyyy-MM-dd-HH ");

                        path = excelDir.FullName + fileName;
                        if (File.Exists(path))
                        {
                            File.Delete(path);
                        }
                        using (Stream localFile = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                        {
                            //ms.ToArray()转换为字节数组就是想要的图片源字节
                            localFile.Write(ms.ToArray(), 0, (int)ms.Length);
                        }
                        book = null;
                        ms.Close();
                        ms.Dispose();
                        //
                        string webAddress = System.Configuration.ConfigurationManager.AppSettings["mkDataPath"].ToString();

                        return(webAddress + fileName);
                    }
                }
                catch (Exception)
                {
                    //Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导出格式错误');</script>");
                    return("Error1");
                }
            }
            else
            {
                return("Error2");
            }
        }
Exemplo n.º 29
0
        protected void btnExport_Click(object sender, EventArgs e)
        {
            OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString);

            conn.Open();

            Dal.Models.UserInfo user = (Dal.Models.UserInfo)Session["UserInfo"];
            string strActivityType   = Session["ActivityType"].ToString();

            Dal.Models.Activity activity = BLL.Activity.GetActivity(strActivityType, conn);
            if (activity == null)
            {
                Response.Redirect("~/View/Mutual/HomePage.aspx");
                conn.Close();
                return;
            }

            Dal.Models.Prize             prize         = BLL.Prize.GetPrize(Convert.ToInt32(hfPrizeID.Value), conn);
            List <Dal.Models.VoteResult> lstVoteResult = BLL.Vote.GetVoteResult(prize.PrizeID.Value, prize.MultipleVoteRound ?? 1, conn);

            DataTable dtExpert = Dal.DataTableExtensions.ToDataTable(lstVoteResult);

            string root            = System.Web.HttpContext.Current.Request.PhysicalApplicationPath;
            string TempletFileName = root + "Content/Template/投票.xls";                                           //模板文件
            string ReportFileName  = Server.MapPath("~/Content/Temp/ExcelTemp/" + prize.PrizeName + "投票结果.xls"); //导出文件

            try
            {
                NPOI.HSSF.UserModel.HSSFWorkbook wk    = new NPOI.HSSF.UserModel.HSSFWorkbook();
                NPOI.SS.UserModel.ISheet         ws    = wk.CreateSheet(prize.PrizeName + "投票结果");
                NPOI.SS.UserModel.IRow           row   = null;
                NPOI.SS.UserModel.ICellStyle     style = wk.CreateCellStyle();

                style.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
                style.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Center;
                style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

                row = ws.CreateRow(0);

                row.HeightInPoints = 35;
                SetNewCell(row, 0, "排序", style);
                SetNewCell(row, 1, "项目编码", style);
                SetNewCell(row, 2, "项目名称", style);
                SetNewCell(row, 3, "申报单位", style);
                SetNewCell(row, 4, "申报奖项", style);
                SetNewCell(row, 5, "奖项类别", style);
                SetNewCell(row, 6, "投票方式", style);

                if (prize.PrizeTypeCode == "0302" || prize.PrizeTypeCode == "0303" ||
                    ((prize.VoteType == "2202" || prize.VoteType == "2203") && prize.MultipleVoteRound == 1))
                {
                    SetNewCell(row, 7, "推荐", style);
                    SetNewCell(row, 8, "不推荐", style);
                    SetNewCell(row, 9, "缓评", style);
                    SetNewCell(row, 10, "转出", style);
                }
                else if (prize.VoteType == "2202" && prize.MultipleVoteRound == 2)
                {
                    SetNewCell(row, 7, "一等奖", style);
                    SetNewCell(row, 8, "二等奖", style);
                    SetNewCell(row, 9, "三等奖", style);
                }
                else if (prize.VoteType == "2201" && (prize.PrizeTypeCode == "0301" || prize.PrizeTypeCode == "0304"))
                {
                    SetNewCell(row, 7, "一等奖", style);
                    SetNewCell(row, 8, "二等奖", style);
                    SetNewCell(row, 9, "三等奖", style);
                    SetNewCell(row, 10, "不推荐", style);
                    SetNewCell(row, 11, "缓评", style);
                    SetNewCell(row, 12, "转出", style);
                }
                else
                {
                    if (prize.MultipleVoteRound == 2)
                    {
                        SetNewCell(row, 7, "一等奖、二等奖", style);
                        SetNewCell(row, 8, "三等奖", style);
                    }
                    else
                    {
                        SetNewCell(row, 7, "一等奖", style);
                        SetNewCell(row, 8, "二等奖", style);
                    }
                }

                for (int i = 0; i < dtExpert.Rows.Count; i++)
                {
                    row = ws.CreateRow(i + 1);
                    SetNewCell(row, 0, dtExpert.Rows[i]["Ordinal"].ToString(), style);
                    SetNewCell(row, 1, dtExpert.Rows[i]["DeclarationCode"].ToString(), style);
                    SetNewCell(row, 2, dtExpert.Rows[i]["DeclarationName"].ToString(), style);
                    SetNewCell(row, 3, dtExpert.Rows[i]["OrganizationName"].ToString(), style);
                    SetNewCell(row, 4, dtExpert.Rows[i]["PrizeName"].ToString(), style);
                    SetNewCell(row, 5, dtExpert.Rows[i]["PrizeType"].ToString(), style);
                    SetNewCell(row, 6, dtExpert.Rows[i]["VoteTypeText"].ToString(), style);

                    if (prize.PrizeTypeCode == "0302" || prize.PrizeTypeCode == "0303" ||
                        ((prize.VoteType == "2202" || prize.VoteType == "2203") && prize.MultipleVoteRound == 1))
                    {
                        SetNewCell(row, 7, dtExpert.Rows[i]["Recommended"].ToString(), style);
                        SetNewCell(row, 8, dtExpert.Rows[i]["NotRecommended"].ToString(), style);
                        SetNewCell(row, 9, dtExpert.Rows[i]["SlowRating"].ToString(), style);
                        SetNewCell(row, 10, dtExpert.Rows[i]["TurnOut"].ToString(), style);
                    }
                    else if (prize.VoteType == "2202" && prize.MultipleVoteRound == 2)
                    {
                        SetNewCell(row, 7, dtExpert.Rows[i]["FirstPrize"].ToString(), style);
                        SetNewCell(row, 8, dtExpert.Rows[i]["SecondPrize"].ToString(), style);
                        SetNewCell(row, 9, dtExpert.Rows[i]["ThirdPrize"].ToString(), style);
                    }
                    else if (prize.VoteType == "2201" && (prize.PrizeTypeCode == "0301" || prize.PrizeTypeCode == "0304"))
                    {
                        SetNewCell(row, 7, dtExpert.Rows[i]["FirstPrize"].ToString(), style);
                        SetNewCell(row, 8, dtExpert.Rows[i]["SecondPrize"].ToString(), style);
                        SetNewCell(row, 9, dtExpert.Rows[i]["ThirdPrize"].ToString(), style);
                        SetNewCell(row, 10, dtExpert.Rows[i]["NotRecommended"].ToString(), style);
                        SetNewCell(row, 11, dtExpert.Rows[i]["SlowRating"].ToString(), style);
                        SetNewCell(row, 12, dtExpert.Rows[i]["TurnOut"].ToString(), style);
                    }
                    else
                    {
                        SetNewCell(row, 7, dtExpert.Rows[i]["Recommended"].ToString(), style);
                        SetNewCell(row, 8, dtExpert.Rows[i]["NotRecommended"].ToString(), style);
                    }
                }

                ws.ForceFormulaRecalculation = true;

                //先删除上一个临时文件
                if (File.Exists(ReportFileName))
                {
                    FileInfo fi = new FileInfo(ReportFileName);
                    if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
                    {
                        fi.Attributes = FileAttributes.Normal;
                    }
                    File.Delete(ReportFileName);
                }

                //创建临时文件写入数据
                using (FileStream filess = File.OpenWrite(ReportFileName))
                {
                    wk.Write(filess);
                }
            }
            catch (Exception ex)
            {
                Response.Write("<script>alert('" + ex.Message + "');</script>");
                return;
            }

            System.IO.FileInfo filet = new System.IO.FileInfo(ReportFileName);
            Response.Clear();
            Response.Charset         = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(prize.PrizeName + "投票结果.xls"));
            Response.AddHeader("Content-Length", filet.Length.ToString());
            Response.ContentType = "application/ms-excel";
            Response.WriteFile(filet.FullName);
            Response.End();
            conn.Close();
        }
        public byte[] ExportXls(List <GroupOperation> list, GroupByEnum groupBy)
        {
            NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet    sheet    = workbook.CreateSheet("Plan 1");

            int rowNumer = 0;

            NPOI.SS.UserModel.IRow  row = sheet.CreateRow(rowNumer);
            NPOI.SS.UserModel.ICell cell;
            NPOI.SS.UserModel.IFont hFont = workbook.CreateFont();

            hFont.FontHeightInPoints = 12;
            hFont.FontName           = "Arial";

            NPOI.SS.UserModel.ICellStyle styleHeader = workbook.CreateCellStyle();
            styleHeader.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            styleHeader.FillPattern         = NPOI.SS.UserModel.FillPattern.SolidForeground;
            styleHeader.Alignment           = NPOI.SS.UserModel.HorizontalAlignment.Center;
            styleHeader.SetFont(hFont);

            NPOI.SS.UserModel.ICellStyle styleDisabled = workbook.CreateCellStyle();
            styleDisabled.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            styleDisabled.FillPattern         = NPOI.SS.UserModel.FillPattern.SolidForeground;
            styleDisabled.Alignment           = NPOI.SS.UserModel.HorizontalAlignment.Center;

            var listColumns = new List <ExportColumnInfo>();

            listColumns.Add(new ExportColumnInfo()
            {
                Name = groupBy.GetDescription()
            });
            listColumns.Add(new ExportColumnInfo()
            {
                Name = "Quantidade"
            });
            listColumns.Add(new ExportColumnInfo()
            {
                Name = "Preço Médio"
            });

            for (int i = 0; i < listColumns.Count; i++)
            {
                cell = row.CreateCell(i);
                cell.SetCellValue(listColumns[i].Name);
                cell.CellStyle = styleHeader;
            }

            //---- row
            foreach (var item in list)
            {
                rowNumer++;

                row = sheet.CreateRow(rowNumer);
                row.CreateCell(0).SetCellValue(item.AccountNumber ?? item.Active ?? item.OperationType);
                row.CreateCell(1).SetCellValue(item.Quantity);
                row.CreateCell(2).SetCellValue((double)item.AveragePrice);
            }

            for (int i = 0; i < listColumns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }

            byte[] byteArray;
            using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
            {
                workbook.Write(stream);
                byteArray = stream.ToArray();
            }

            return(byteArray);
        }
Exemplo n.º 31
0
        /// <summary>
        /// 返回与EasyUI的datagrid相适应的分页数据
        /// <para>此方法将直接返回分页结果</para>
        /// </summary>
        /// <typeparam name="T">结果集类型</typeparam>
        /// <param name="result">分页结果集</param>
        /// <returns>执行结果</returns>
        //protected ActionResult PagerDirectly<T>(PagedResult<T> result)
        //{
        //    return Json(new { total = result.TotalItems, rows = result.Items }, JsonRequestBehavior.AllowGet);
        //}


        ///// <summary>
        ///// 根据存储过程返回查询数据
        ///// </summary>
        ///// <param name="procedureName"></param>
        ///// <param name="filters"></param>
        ///// <returns></returns>
        //public IList<IDictionary<string, object>> GetDataByProcedure(string procedureName, List<MyFilter> filters = null)
        //{
        //    filters = filters == null ? new List<MyFilter>() : filters;
        //    var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["LandSystem"].ConnectionString);
        //    var cmd = new OracleCommand(procedureName, conn);

        //    var paramsList = ModelQuery.Default.CreateSQLQuery(string.Format("SELECT T.ARGUMENT_NAME FROM user_arguments T WHERE T.OBJECT_NAME='{0}' ORDER BY T.POSITION ASC", procedureName)).List<string>();

        //    cmd.CommandType = CommandType.StoredProcedure;

        //    //var paramers = new HashSet<DbParameter>();

        //    foreach (var item in paramsList)
        //    {
        //        var filter = filters.Where(x => x.property.ToUpper() == item).FirstOrDefault();
        //        if (filter == null)
        //        {
        //            continue;
        //        }
        //        if (OracelTypeDic.ContainsKey(filter.dataType))
        //        {
        //            var param = new OracleParameter()
        //            {
        //                OracleDbType = OracelTypeDic[filter.dataType],
        //                ParameterName = filter.property,
        //                Direction = ParameterDirection.Input
        //            };
        //            if (filter.value == null)
        //            {
        //                param.Value = DBNull.Value;
        //            }
        //            else if (filter.dataType == "date")
        //            {
        //                param.Value = DateTime.Parse(filter.value);
        //            }
        //            else
        //            {
        //                param.Value = filter.value;
        //            }
        //            cmd.Parameters.Add(param);
        //        }
        //    }

        //    var paramer1 = new OracleParameter()
        //    {
        //        OracleDbType = OracleDbType.RefCursor,
        //        ParameterName = "DATATABLE",
        //        Direction = ParameterDirection.Output
        //    };

        //    cmd.Parameters.Add(paramer1);

        //    var da = new OracleDataAdapter(cmd);

        //    da.SelectCommand = cmd;

        //    DataSet ds = new DataSet("dt");

        //    da.Fill(ds, "dt");

        //    return ds.Tables[0].ToIDictionaryList();
        //}
        ///// <summary>
        ///// 根据存储过程返回查询数据
        ///// </summary>
        ///// <param name="procedureName"></param>
        ///// <param name="filters"></param>
        ///// <returns></returns>
        //public IList<IDictionary<string, object>> GetPagedByProcedure(string procedureName, int page, int rows, out int total, List<MyFilter> filters = null)
        //{
        //    filters = filters == null ? new List<MyFilter>() : filters;

        //    filters.Add(new MyFilter() { property = "PAGE", value = page.ToString(), dataType = "number" });
        //    filters.Add(new MyFilter() { property = "ROWS", value = rows.ToString(), dataType = "number" });

        //    var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["LandSystem"].ConnectionString);
        //    var cmd = new OracleCommand(procedureName, conn);

        //    var paramsList = ModelQuery.Default.CreateSQLQuery(string.Format("SELECT T.ARGUMENT_NAME FROM user_arguments T WHERE T.OBJECT_NAME='{0}' ORDER BY T.POSITION ASC", procedureName)).List<string>();

        //    cmd.CommandType = CommandType.StoredProcedure;

        //    foreach (var item in paramsList)
        //    {
        //        var filter = filters.Where(x => x.property.ToUpper() == item).FirstOrDefault();
        //        if (filter == null)
        //        {
        //            continue;
        //        }
        //        if (OracelTypeDic.ContainsKey(filter.dataType))
        //        {
        //            var param = new OracleParameter()
        //            {
        //                OracleDbType = OracelTypeDic[filter.dataType],
        //                ParameterName = filter.property,
        //                Direction = ParameterDirection.Input
        //            };
        //            if (filter.value == null)
        //            {
        //                param.Value = DBNull.Value;
        //            }
        //            else if (filter.dataType == "date")
        //            {
        //                param.Value = DateTime.Parse(filter.value);
        //            }
        //            else
        //            {
        //                param.Value = filter.value;
        //            }
        //            cmd.Parameters.Add(param);
        //        }
        //    }

        //    var paramer1 = new OracleParameter()
        //    {
        //        OracleDbType = OracleDbType.RefCursor,
        //        ParameterName = "DATATABLE",
        //        Direction = ParameterDirection.Output
        //    };
        //    cmd.Parameters.Add(paramer1);

        //    var paramer2 = new OracleParameter()
        //    {
        //        OracleDbType = OracleDbType.Int64,
        //        ParameterName = "TOTAL",
        //        Direction = ParameterDirection.Output
        //    };
        //    cmd.Parameters.Add(paramer2);

        //    DataSet ds = new DataSet("dt");
        //    total = 0;
        //    try
        //    {
        //        conn.Open();

        //        cmd.ExecuteNonQuery();

        //        var da = new OracleDataAdapter();

        //        da.Fill(ds, "dt", paramer1.Value as Oracle.ManagedDataAccess.Types.OracleRefCursor);
        //        total = int.Parse(paramer2.Value.ToString());
        //    }
        //    catch (Exception ex)
        //    {

        //    }
        //    finally
        //    {
        //        conn.Close();
        //    }

        //    return ds.Tables[0].ToIDictionaryList();
        //}

        ///// <summary>
        ///// OracleDbType类型转换
        ///// </summary>
        //private static Dictionary<string, OracleDbType> OracelTypeDic = new Dictionary<string, OracleDbType>
        //{
        //  { "varchar", OracleDbType.Varchar2 },
        //  { "nvarchar", OracleDbType.NVarchar2 },
        //  { "number", OracleDbType.Int64 },
        //  { "date", OracleDbType.Date },
        //  { "long", OracleDbType.Long }
        //};

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="data">数据集</param>
        /// <param name="columns">列</param>
        /// <param name="sheetName">导出的Excel文件名称</param>
        /// <returns></returns>
        public FileResult ExportExcel(List <Dictionary <string, object> > data, List <Column> columns, string sheetName = "导出Excel")
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            //获取list数据
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            columns = columns.Where(x => x.hidden != true && !string.IsNullOrEmpty(x.title)).ToList();

            var headerStyle = book.CreateCellStyle();
            var headerFont  = book.CreateFont();

            headerFont.IsBold             = true;
            headerFont.FontHeightInPoints = 12;
            headerFont.FontName           = "宋体";
            headerStyle.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            headerStyle.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            headerStyle.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            headerStyle.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            headerStyle.SetFont(headerFont);

            for (var i = 0; i < columns.Count; i++)
            {
                var cell = row1.CreateCell(i);
                cell.SetCellValue(columns[i].title);
                sheet1.SetColumnWidth(i, columns[i].width * 40);
                cell.CellStyle = headerStyle;
            }

            var contentStyle = book.CreateCellStyle();

            contentStyle.CloneStyleFrom(headerStyle);
            var contentFont = book.CreateFont();

            contentFont.FontHeightInPoints = 12;
            contentFont.FontName           = "宋体";
            contentStyle.SetFont(contentFont);

            for (int i = 0; i < data.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                for (var j = 0; j < columns.Count; j++)
                {
                    //var property = data[i].GetType().GetProperty(columns[j].field);
                    var value = "";
                    if (data[i].ContainsKey(columns[j].field) && data[i][columns[j].field] != null)
                    {
                        value = data[i][columns[j].field].ToString();
                    }
                    var cell = rowtemp.CreateCell(j);
                    cell.SetCellValue(value);

                    cell.CellStyle = contentStyle;
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return(File(ms, "application/vnd.ms-excel", sheetName + ".xls"));
        }
Exemplo n.º 32
0
        protected void ExportButton_Click(object sender, EventArgs e)
        {
            ProductSearchInfo productSearch = new ProductSearchInfo();
            OrderSearchInfo   orderSearch   = new OrderSearchInfo();

            productSearch.IsSale     = (int)BoolType.True;
            productSearch.Name       = ShopCommon.ConvertToT <string>(Name.Text);
            productSearch.ClassId    = ShopCommon.ConvertToT <string>(ClassID.Text);
            productSearch.BrandId    = ShopCommon.ConvertToT <int>(BrandID.Text);
            orderSearch.StartAddDate = ShopCommon.ConvertToT <DateTime>(StartAddDate.Text);
            orderSearch.EndAddDate   = ShopCommon.SearchEndDate(ShopCommon.ConvertToT <DateTime>(EndAddDate.Text));
            orderSearch.UserName     = ShopCommon.ConvertToT <string>(UserName.Text);
            orderSearch.OrderNumber  = ShopCommon.ConvertToT <string>(OrderNumber.Text);
            var data = OrderDetailBLL.StatisticsSaleDetail(1, 1000, orderSearch, productSearch, ref Count);

            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("Sheet1");
            sheet.DefaultColumnWidth = 18;
            sheet.CreateFreezePane(0, 1, 0, 1);

            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            row.Height = 20 * 20;
            row.CreateCell(0).SetCellValue("时间");
            row.CreateCell(1).SetCellValue("单号");
            row.CreateCell(2).SetCellValue("商品名称");
            row.CreateCell(3).SetCellValue("数量");
            row.CreateCell(4).SetCellValue("金额");
            row.CreateCell(5).SetCellValue("用户名");

            //设置表头格式
            var headFont = book.CreateFont();

            headFont.Boldweight         = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            headFont.FontHeightInPoints = 10;
            var headStyle = book.CreateCellStyle();

            headStyle.SetFont(headFont);
            headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            headStyle.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
            foreach (var cell in row.Cells)
            {
                cell.CellStyle = headStyle;
            }

            foreach (DataRow dr in data.Rows)
            {
                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(data.Rows.IndexOf(dr) + 1);
                dataRow.CreateCell(0).SetCellValue(Convert.ToString(dr["AddDate"]));
                dataRow.CreateCell(1).SetCellValue(Convert.ToString(dr["OrderNumber"]));
                dataRow.CreateCell(2).SetCellValue(Convert.ToString(dr["Name"]));
                dataRow.CreateCell(3).SetCellValue(Convert.ToString(dr["BuyCount"]));
                dataRow.CreateCell(4).SetCellValue(Convert.ToString(dr["Money"]));
                dataRow.CreateCell(5).SetCellValue(Convert.ToString(dr["UserName"]));

                var style = book.CreateCellStyle();
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                foreach (var cell in dataRow.Cells)
                {
                    cell.CellStyle = style;
                }
            }

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
            Response.End();
        }
Exemplo n.º 33
0
        public static MemoryStream RenderToExcel(List<Bean.ArchiveBean> list)
        {   
            MemoryStream ms = new MemoryStream();

            NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");

            #region head row
            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            ICellStyle style = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.Bold;
            style.SetFont(font);
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            //row.RowStyle = style;
            row.Height = 26 * 20;

            CreateCell(row, 0, "序号", CellType.String, style);
            CreateCell(row, 1, "责任人", CellType.String, style);
            CreateCell(row, 2, "文件题目", CellType.String, style);
            CreateCell(row, 3, "页数", CellType.String, style);
            CreateCell(row, 4, "编号", CellType.String, style);
            CreateCell(row, 5, "备注", CellType.String, style);         
            #endregion

            if (list != null && list.Count > 0)
            {
                int rowidx = 0;
                foreach (Bean.ArchiveBean model in list)
                {
                    #region row
                    rowidx++;
                    row = sheet.CreateRow(rowidx);
                    CreateCell(row, 0, model.idx);
                    CreateCell(row, 1, model.manager);
                    CreateCell(row, 2, model.title);
                    CreateCell(row, 3, model.pages);
                    CreateCell(row, 4, model.number);
                    CreateCell(row, 5, model.remark);
                    
               
                    #endregion
                }
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            return ms;
        }