Exemple #1
0
        void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
        {
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");

            // 添加表头
            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            int index = 0;

            foreach (DataColumn item in dt.Columns)
            {
                NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue(item.Caption);
                index++;
            }

            // 添加数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                index = 0;
                row   = sheet.CreateRow(i + 1);
                foreach (DataColumn item in dt.Columns)
                {
                    NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                    cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                    cell.SetCellValue(dt.Rows[i][item].ToString());
                    index++;
                }
            }
        }
Exemple #2
0
        public static void FullSheet(NPOI.SS.UserModel.ISheet iSheet, System.Data.DataTable dataTable, bool firstRowIsColumnHead)
        {
            if (iSheet == null)
            {
                throw new AggregateException("参数NPOI.SS.UserModel.ISheet对象为null。");
            }
            NPOI.SS.UserModel.IRow iRow = iSheet.CreateRow(0);
            for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
            {
                NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex);
                iCell.SetCellValue(dataTable.Columns[dataTableColumnIndex].ColumnName);
            }
            int startRowIndex = 0;

            if (firstRowIsColumnHead)
            {
                startRowIndex = 1;
            }
            for (int dataTableRowIndex = 0; dataTableRowIndex < dataTable.Rows.Count; dataTableRowIndex++)
            {
                iRow = iSheet.CreateRow(dataTableRowIndex + startRowIndex);
                for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
                {
                    NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex);
                    SetCellValue(iCell, dataTable.Rows[dataTableRowIndex][dataTableColumnIndex]);
                }
            }
        }
Exemple #3
0
 static void DoMergeExcelSheet(NPOI.SS.UserModel.ISheet sheet)
 {
     string[] heads = new string[] { "姓名", "复训带教", "复训检查", "复训翻译" };
     //增加列头
     //设置列头直接进行单元格合并
     NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
     row.Height = 1024;
     for (int i = 0; i < heads.Length; i++)
     {
         int cellIndex = i > 0 ? 2 * i - 1 : i;
         NPOI.SS.UserModel.ICell cell = row.CreateCell(cellIndex);
         cell.SetCellValue(heads[i]);
     }
     for (int i = 0; i < heads.Length; i++)
     {
         if (i == 0)
         {
             sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0));
         }
         else
         {
             sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 2 * i - 1, 2 * i));
         }
     }
 }
Exemple #4
0
 public static void SetCellValue(NPOI.SS.UserModel.ICell iCell, object cellValue)
 {
     if (iCell == null)
     {
         return;
     }
     if (cellValue != null)
     {
         if (cellValue.GetType() == typeof(DBNull))
         {
             iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank);
         }
         else if (cellValue.GetType() == typeof(bool))
         {
             iCell.SetCellValue((bool)cellValue);
         }
         else if (cellValue.GetType() == typeof(string))
         {
             if (cellValue.ToString() == "#NUM!")
             {
                 iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank);
             }
             else
             {
                 iCell.SetCellValue((string)cellValue);
             }
         }
         else if (cellValue.GetType() == typeof(DateTime))
         {
             NPOI.SS.UserModel.ICellStyle  iCellStyle  = iCell.Sheet.Workbook.CreateCellStyle();
             NPOI.SS.UserModel.IDataFormat iDataFormat = iCell.Sheet.Workbook.CreateDataFormat();
             iCellStyle.DataFormat = iDataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");
             iCell.CellStyle       = iCellStyle;
             iCell.SetCellValue((DateTime)cellValue);
         }
         else if (cellValue.GetType() == typeof(int) || cellValue.GetType() == typeof(double) || cellValue.GetType() == typeof(float))
         {
             iCell.SetCellValue((double)cellValue);
         }
         else
         {
             iCell.SetCellValue(cellValue.ToString());
         }
     }
 }
        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="response"></param>
        /// <param name="dt"></param>
        /// <param name="fileName"></param>
        /// <param name="sheetname"></param>
        public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(sheetname);
            NPOI.SS.UserModel.IRow           row   = sheet.CreateRow(0);
            NPOI.SS.UserModel.ICell          cell  = null;
            NPOI.SS.UserModel.IFont          font  = book.CreateFont();
            NPOI.SS.UserModel.ICellStyle     style = book.CreateCellStyle();
            font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            font.FontName   = "微软雅黑";

            style.SetFont(font);

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                cell           = row.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string strColDataType = dt.Columns[j].DataType.ToString();
                    if (strColDataType.Equals("System.Int32"))
                    {
                        int intValue = 0;
                        int.TryParse(dt.Rows[i][j].ToString(), out intValue);
                        row2.CreateCell(j).SetCellValue(intValue);
                    }
                    else if (strColDataType.Equals("System.String"))
                    {
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                    else if (strColDataType.Equals("System.Double"))
                    {
                        double dblValue = 0;
                        double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
                        row2.CreateCell(j).SetCellValue(dblValue);
                    }
                }
            }
            //写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls"));
            response.ContentType = "application/vnd.ms-excel";
            //response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }
Exemple #6
0
        /// <summary>
        /// 读取模板,根据List(list(string))"创建一个Excel的WorkBook并返回
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="columnName">表头</param>
        /// <param name="columnWidth">列宽度*256</param>
        /// <param name="ModUrl">模板地址</param>
        /// <param name="startRowIndex">起始行,首行=0,默认0</param>
        /// <param name="RemoveOtherRows">是否移除起始行后的数据,默认=是</param>
        /// <param name="SheetName">表名称</param>
        /// <returns>XSSFWorkbook对象</returns>
        public XSSFWorkbook CreateExcelByList_Mod(System.Collections.Generic.List <System.Collections.Generic.List <object> > dt, string[] columnName, int[] columnWidth, string ModUrl, int startRowIndex = 0, bool RemoveOtherRows = true, string SheetName = "Sheet1")
        {
            startRowIndex = startRowIndex >= 0?startRowIndex:0;

            string ExcelModePath = NodeBase + ModUrl;

            if (!File.Exists(ExcelModePath))
            {
                return(null);
            }

            //创建一个工作博
            XSSFWorkbook wk = new XSSFWorkbook();

            using (FileStream fileExcelMod = new FileStream(ExcelModePath, FileMode.Open, FileAccess.Read))
            {
                wk = new XSSFWorkbook(fileExcelMod);
                fileExcelMod.Close();
            }
            if (wk == null)
            {
                return(null);
            }

            //创建一个Sheet
            ISheet tb = wk.GetSheetAt(0);

            #region 移除起始行后的所有行
            if (RemoveOtherRows && tb.LastRowNum > startRowIndex + 1)
            {
                for (int rmr = tb.LastRowNum - 1; rmr > startRowIndex; rmr--)
                {
                    tb.ShiftRows(rmr, rmr + 1, -1);
                }
            }
            #endregion

            //设置单元格宽度

            //创建表头(在第0行)

            #region 根据List<List<object>>内容创建Excel内容
            for (int i = 0; i < dt.Count; i++)
            {
                IRow rows = tb.CreateRow(i + startRowIndex);
                for (int j = 0; j < dt[i].Count; j++)
                {
                    NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
                    cell.SetCellValue(dt[i][j].ToStr());
                    SetBorder(cell);
                }
            }
            #endregion
            return(wk);
        }
Exemple #7
0
 private void SetNewCell(NPOI.SS.UserModel.IRow row, int iIdx, string strValue, NPOI.SS.UserModel.ICellStyle style = null)
 {
     NPOI.SS.UserModel.ICell cell = row.CreateCell(iIdx, NPOI.SS.UserModel.CellType.String);
     cell.SetCellValue(strValue);
     if (style != null)
     {
         cell.CellStyle = style;
     }
     else
     {
         cell.CellStyle.BorderBottom = 0;
     }
 }
        /// <summary>
        /// 设置样式
        /// </summary>
        private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style)
        {
            NPOI.SS.UserModel.ISheet sheet   = book.CreateSheet(sheetname);
            NPOI.SS.UserModel.IRow   row     = sheet.CreateRow(0);
            NPOI.SS.UserModel.ICell  cell    = null;
            NPOI.SS.UserModel.ICell  newCell = null;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                cell           = row.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string strColDataType = dt.Columns[j].DataType.ToString();
                    if (strColDataType.Equals("System.Int32"))
                    {
                        int intValue = 0;
                        int.TryParse(dt.Rows[i][j].ToString(), out intValue);
                        row2.CreateCell(j).SetCellValue(intValue);
                    }
                    else if (strColDataType.Equals("System.String"))
                    {
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                    else if (strColDataType.Equals("System.Double"))
                    {
                        double dblValue = 0;
                        double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
                        row2.CreateCell(j).SetCellValue(dblValue);
                    }
                    else if (strColDataType.Equals("System.DateTime"))
                    {
                        DateTime dateV;
                        DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV);
                        newCell = row2.CreateCell(j);
                        newCell.SetCellValue(dateV);

                        //格式化显示
                        HSSFCellStyle  cellStyle = (HSSFCellStyle)book.CreateCellStyle();
                        HSSFDataFormat format    = (HSSFDataFormat)book.CreateDataFormat();
                        cellStyle.DataFormat = format.GetFormat("yyyy-m-d");
                        newCell.CellStyle    = cellStyle;
                    }
                }
            }
        }
Exemple #9
0
        /// <summary>
        /// 根据List(list(string))"创建一个Excel的WorkBook并返回
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="columnName">表头</param>
        /// <param name="columnWidth">列宽度*256</param>
        /// <param name="SheetName">表名称</param>
        /// <returns>XSSFWorkbook对象</returns>
        public XSSFWorkbook CreateExcelByList(System.Collections.Generic.List <System.Collections.Generic.List <object> > dt, string[] columnName, int[] columnWidth, string SheetName = "Sheet1")
        {
            //创建一个工作博
            XSSFWorkbook wk = new XSSFWorkbook();
            //创建以个Sheet
            ISheet tb = wk.CreateSheet(SheetName);

            //设置单元格宽度
            for (int i = 0; i < columnWidth.Length; i++)
            {
                tb.SetColumnWidth(i, columnWidth[i] * 256);
            }
            //创建表头(在第0行)
            IRow row = tb.CreateRow(0);

            #region 表头根据参数
            for (int i = 0; i < columnName.Length; i++)
            {
                //创建单元格
                NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
                cell.SetCellValue(columnName[i]);
                //背景
                //cell.CellStyle.FillForegroundColor = NPOI.XSSF.Util.XSSFColor.DarkTeal.Index;
                //cell.CellStyle.FillPattern = FillPattern.SolidForeground;
                //边框颜色
                //cell.CellStyle.TopBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
                //cell.CellStyle.LeftBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
                //cell.CellStyle.BottomBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
                //cell.CellStyle.RightBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;

                cell.CellStyle.IsLocked = true;
                SetBorder(cell);
            }
            #endregion
            #region 根据List<List<object>>内容创建Excel内容
            for (int i = 0; i < dt.Count; i++)
            {
                IRow rows = tb.CreateRow(i + 1);
                for (int j = 0; j < dt[i].Count; j++)
                {
                    NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
                    cell.SetCellValue(dt[i][j].ToStr());
                    SetBorder(cell);
                }
            }
            #endregion
            return(wk);
        }
        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();
        }
        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();
        }
Exemple #12
0
        /// <summary>
        /// 根据DataTable创建一个Excel的WorkBook并返回
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="columnName">表头</param>
        /// <param name="SheetName">表名称</param>
        /// <returns>XSSFWorkbook对象</returns>
        public XSSFWorkbook CreateExcelByDataTable(DataTable dt, string[] columnName, string SheetName = "Sheet1")
        {
            //创建一个工作博
            XSSFWorkbook wk = new XSSFWorkbook();
            //创建以个Sheet
            ISheet tb = wk.CreateSheet(SheetName);
            //创建表头(在第0行)
            IRow row = tb.CreateRow(0);

            #region 表头根据参数
            for (int i = 0; i < columnName.Length; i++)
            {
                //创建单元格
                NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
                cell.SetCellValue(columnName[i]);
                cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
                SetBorder(cell);
            }
            #endregion
            #region 根据DataTable内容创建Excel内容
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow rows = tb.CreateRow(i);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (j >= columnName.Length)
                    {
                        break;
                    }
                    NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                    SetBorder(cell);
                }
            }
            #endregion
            return(wk);
        }
Exemple #13
0
        /// <summary>
        /// 根据DataTable创建一个Excel的WorkBook并返回
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <returns>XSSFWorkbook对象</returns>
        public XSSFWorkbook CreateExcelByDataTable(DataTable dt)
        {
            //创建一个工作博
            XSSFWorkbook wk = new XSSFWorkbook();
            //创建以个Sheet
            ISheet tb = wk.CreateSheet("Sheet1");

            //创建表头(在第0行)
            IRow row = tb.CreateRow(0);

            #region 根据Datable表头创建Excel表头
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                //创建单元格
                NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
                //cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkRed.Index;
                //cell.CellStyle.FillForegroundColor= NPOI.HSSF.Util.HSSFColor.Yellow.Index;
                cell.SetCellValue(dt.Columns[i].ColumnName);
                tb.AutoSizeColumn(i);//自动调整宽度,貌似对中文支持不好
                SetBorder(cell);
            }
            #endregion
            #region 根据DataTable内容创建Excel内容
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow rows = tb.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                    SetBorder(cell);
                }
            }
            #endregion
            return(wk);
        }
Exemple #14
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);
        }
    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();
    }
Exemple #16
0
        private void explort()
        {
            SaveFileDialog sflg = new SaveFileDialog();

            sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
            {
                return;
            }
            //this.gridView1.ExportToXls(sflg.FileName);
            //NPOI.xs book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.IWorkbook book = null;
            if (sflg.FilterIndex == 1)
            {
                book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            }
            else
            {
                book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            }

            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_001");

            // 添加表头
            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            int index = 0;

            foreach (DataGridViewColumn item in this.dataGridView2.Columns)
            {
                if (item.Visible)
                {
                    NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                    cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                    cell.SetCellValue(item.HeaderText);
                    index++;
                }
            }

            // 添加数据

            for (int i = 0; i < this.dataGridView2.Rows.Count; i++)
            {
                index = 0;
                row   = sheet.CreateRow(i + 1);
                foreach (DataGridViewColumn item in this.dataGridView2.Columns)
                {
                    if (item.Visible)
                    {
                        if (dataGridView2.Rows[i].Cells[item.HeaderText].Value != null)
                        {
                            NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cell.SetCellValue(dataGridView2.Rows[i].Cells[item.HeaderText].Value.ToString().Trim());
                        }
                        index++;
                    }
                }
            }
            // 写入
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            book = null;

            using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }

            ms.Close();
            ms.Dispose();
        }
Exemple #17
0
        private void sb_save_Click(object sender, EventArgs e)
        {
            SaveFileDialog sflg = new SaveFileDialog();

            sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
            {
                return;
            }
            int aa = 0;

            NPOI.SS.UserModel.IWorkbook book = null;
            if (sflg.FilterIndex == 1)
            {
                book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            }
            else
            {
                book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            }
            foreach (XtraTabPage trp in this.xtraTabControl1.TabPages)
            {
                XtraTabPage xinka = trp;
                try
                {
                    this.active_nepCalaTable = xinka.Controls[0] as NepCalaTable;
                }
                catch (Exception err)
                {
                    MessageBox.Show("出现了错误: " + err.Message);
                }
                aa += 1;

                //this.gridView1.ExportToXls(sflg.FileName);
                //NPOI.xs book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                //NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(string.Format("test_{0}", aa));
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(xinka.Text);
                // 添加表头
                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
                int      index             = 0;
                GridView ac    = this.active_nepCalaTable.GC;
                int      count = ac.Columns.Count;
                for (int i = 0; i < count; i++)
                {
                    string Caption = ac.Columns[i].Caption;
                    NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                    cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                    cell.SetCellValue(Caption);
                    index++;
                }

                // 添加数据

                DataTable dt = this.active_nepCalaTable.DDT;
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    index = 0;
                    row   = sheet.CreateRow(j + 1);
                    for (int k = 0; k < count; k++)
                    {
                        NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                        cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                        //cell.SetCellValue(this.gridView1.GetRowCellValue(i, item).ToString());
                        cell.SetCellValue(dt.Rows[j][k].ToString());
                        index++;
                    }
                }
                NPOI.SS.UserModel.IRow r1 = sheet.GetRow(0);
                // 第一排
                NPOI.SS.UserModel.ICell cell1 = r1.CreateCell(12);
                cell1.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell1.SetCellValue("激发点距");
                NPOI.SS.UserModel.ICell cell11 = r1.CreateCell(13);
                cell11.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell11.SetCellValue(this.active_nepCalaTable.jifa_dianju.Text);

                // 第二排
                r1 = sheet.GetRow(1);
                NPOI.SS.UserModel.ICell cell2 = r1.CreateCell(12);
                cell2.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell2.SetCellValue("接收点距");
                NPOI.SS.UserModel.ICell cell21 = r1.CreateCell(13);
                cell21.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell21.SetCellValue(this.active_nepCalaTable.jieshou_dianju.Text);



                // 第三排
                r1 = sheet.GetRow(2);
                NPOI.SS.UserModel.ICell cell3 = r1.CreateCell(12);
                cell3.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell3.SetCellValue("接收点数");
                NPOI.SS.UserModel.ICell cell31 = r1.CreateCell(13);
                cell31.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell31.SetCellValue(this.active_nepCalaTable.jieshou_dianshu.Text);



                // 第四排
                r1 = sheet.GetRow(3);
                NPOI.SS.UserModel.ICell cell4 = r1.CreateCell(12);
                cell4.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell4.SetCellValue("激发线距");
                NPOI.SS.UserModel.ICell cell41 = r1.CreateCell(13);
                cell41.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell41.SetCellValue(this.active_nepCalaTable.jifa_xianju.Text);



                // 第五排
                r1 = sheet.GetRow(4);
                NPOI.SS.UserModel.ICell cell5 = r1.CreateCell(12);
                cell5.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell5.SetCellValue("接受线距");
                NPOI.SS.UserModel.ICell cell51 = r1.CreateCell(13);
                cell51.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell51.SetCellValue(this.active_nepCalaTable.jieshou_xianju.Text);

                // 第六排
                r1 = sheet.GetRow(5);
                NPOI.SS.UserModel.ICell cell6 = r1.CreateCell(12);
                cell6.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell6.SetCellValue("纵向滚动距离");
                NPOI.SS.UserModel.ICell cell61 = r1.CreateCell(13);
                cell61.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell61.SetCellValue(this.active_nepCalaTable.muban_zong.Text);

                // 第7排
                r1 = sheet.GetRow(6);
                NPOI.SS.UserModel.ICell cell7 = r1.CreateCell(12);
                cell7.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell7.SetCellValue("模板炮");
                NPOI.SS.UserModel.ICell cell71 = r1.CreateCell(13);
                cell71.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell71.SetCellValue(this.active_nepCalaTable.mobanpao.Text);


                // 第8排
                r1 = sheet.GetRow(7);
                NPOI.SS.UserModel.ICell cell8 = r1.CreateCell(12);
                cell8.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell8.SetCellValue("接收线数");
                NPOI.SS.UserModel.ICell cell81 = r1.CreateCell(13);
                cell81.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell81.SetCellValue(this.active_nepCalaTable.jieshou_xianshu.Text);



                // 第9排
                r1 = sheet.GetRow(8);
                NPOI.SS.UserModel.ICell cell9 = r1.CreateCell(12);
                cell9.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell9.SetCellValue("横向滚动距离");
                NPOI.SS.UserModel.ICell cell91 = r1.CreateCell(13);
                cell91.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell91.SetCellValue(this.active_nepCalaTable.muban_heng.Text);



                // 第10排
                r1 = sheet.GetRow(9);
                NPOI.SS.UserModel.ICell cell10 = r1.CreateCell(12);
                cell10.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell10.SetCellValue("布设接受线数");
                NPOI.SS.UserModel.ICell cell101 = r1.CreateCell(13);
                cell101.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell101.SetCellValue(this.active_nepCalaTable.bushe_jieshouxianshu.Text);



                // 第11排
                r1 = sheet.GetRow(10);
                NPOI.SS.UserModel.ICell cell1_1 = r1.CreateCell(12);
                cell1_1.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell1_1.SetCellValue("布设激发线数");
                NPOI.SS.UserModel.ICell cell111 = r1.CreateCell(13);
                cell111.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell111.SetCellValue(this.active_nepCalaTable.bushe_jifaxianshu.Text);


                // 写入
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                book.Write(ms);
                //book = null;

                using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }

                ms.Close();
                ms.Dispose();
            }
            book = null;
            MessageBox.Show("保存成功了");
        }
        public void DownlodExcel()
        {
            SaveFileDialog sflg = new SaveFileDialog();

            sflg.Filter   = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            sflg.FileName = "采购单细节导入模板表.xls";
            if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
            {
                return;
            }

            NPOI.SS.UserModel.IWorkbook book = null;
            if (sflg.FilterIndex == 1)
            {
                book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            }

            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("采购细节表");

            // 添加表头
            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);

            NPOI.SS.UserModel.ICell cell = row.CreateCell(0);
            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
            cell.SetCellValue("品名");

            #region 医疗器械(这里不用)
            //if ((int)this.toolStripComboBox1.ComboBox.SelectedValue == (int)PurchaseDrugTypes.医疗器械)
            //{
            //    cell = row.CreateCell(1);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
            //    cell.SetCellValue("型号");

            //    cell = row.CreateCell(2);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
            //    cell.SetCellValue("规格");

            //    cell = row.CreateCell(3);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
            //    cell.SetCellValue("单位");

            //    cell = row.CreateCell(4);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.STRING);
            //    cell.SetCellValue("生产厂家");

            //    cell = row.CreateCell(5);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
            //    cell.SetCellValue("数量");

            //    cell = row.CreateCell(6);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
            //    cell.SetCellValue("单价");

            //    cell = row.CreateCell(7);
            //    cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
            //    cell.SetCellValue("税率(%)");
            //}
            //else
            #endregion
            {
                cell = row.CreateCell(1);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue("剂型");

                cell = row.CreateCell(2);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue("规格");

                cell = row.CreateCell(3);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue("单位");

                cell = row.CreateCell(4);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue("生产厂家");

                cell = row.CreateCell(5);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue("产地");

                cell = row.CreateCell(6);
                cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
                cell.SetCellValue("数量");

                cell = row.CreateCell(7);
                cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
                cell.SetCellValue("单价");

                cell = row.CreateCell(8);
                cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
                cell.SetCellValue("税率(%)");
            }

            // 写入
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            book = null;

            using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }

            ms.Close();
            ms.Dispose();
        }
Exemple #19
0
        private async void executeExportCommand(object obj)
        {
            IsRunning = true;
            await Task.Run(() =>
            {
                try
                {
                    reader.CheckPermission();

                    var filePath = Path.Combine(reader.GetFolderPath(), "SmsExport-" + DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xlsx");
                    var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
                    var sheet    = workbook.CreateSheet("Sheet1");
                    //第一行
                    {
                        var row        = sheet.CreateRow(0);
                        var cColumnInd = 0;

                        row.CreateCell(cColumnInd).SetCellValue("编号");
                        cColumnInd++;
                        row.CreateCell(cColumnInd).SetCellValue("号码");
                        cColumnInd++;
                        row.CreateCell(cColumnInd).SetCellValue("时间");
                        cColumnInd++;
                        row.CreateCell(cColumnInd).SetCellValue("类型");
                        cColumnInd++;
                        row.CreateCell(cColumnInd).SetCellValue("内容");
                        cColumnInd++;
                    }
                    //当前行号
                    var cRowInd = 1;

                    var totalCount = reader.GetCount(StartIndex);
                    var enumerator = reader.GetEnumerator(StartIndex);
                    var index      = 0;
                    while (enumerator.MoveNext())
                    {
                        index++;
                        var item    = enumerator.Current;
                        var typeStr = "";
                        switch (item.ItemType)
                        {
                        case SmsItem.SmsItemType.Send:
                            typeStr = "发送";
                            break;

                        case SmsItem.SmsItemType.Recv:
                            typeStr = "接收";
                            break;

                        default:
                            continue;
                        }
                        {
                            var cColumnInd = 0;

                            var row = sheet.CreateRow(cRowInd);
                            NPOI.SS.UserModel.ICell cell = null;

                            cell = row.CreateCell(cColumnInd);
                            cell.SetCellValue(item.Id);
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cColumnInd++;

                            cell = row.CreateCell(cColumnInd);
                            cell.SetCellValue(item.Address);
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cColumnInd++;

                            cell = row.CreateCell(cColumnInd);
                            cell.SetCellValue(item.Date.ToString("yyyy-MM-dd HH:mm:ss"));
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cColumnInd++;

                            cell = row.CreateCell(cColumnInd);
                            cell.SetCellValue(typeStr);
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cColumnInd++;

                            cell = row.CreateCell(cColumnInd);
                            cell.SetCellValue(item.Body);
                            cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                            cColumnInd++;
                        }
                        cRowInd++;
                        Progress = index * 1D / totalCount;
                        Message  = $"读取短信[{index} / {totalCount}]中...";
                    }
                    Message = $"正在保存到文件...";
                    using (var stream = File.OpenWrite(filePath))
                        workbook.Write(stream);
                    Message = $"已保存到{filePath}";
                }
                catch (Exception ex)
                {
                    Message = "错误," + ex;
                }
            });

            IsRunning = false;
        }
Exemple #20
0
        /// <summary>
        /// 插入到excel的cell
        /// </summary>
        /// <param name="fileName">excel文件名</param>
        /// <param name="dst">目标 (行,列,值)</param>
        /// <param name="sheetName">默认sheetName,可以不写</param>
        public static void WriteToExcelCell(string fileName, List <Tuple <int, int, object, string> > customCell, string sheetName = "sheet1")
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(sheetName);
            foreach (var item in customCell)
            {
                int    r                   = item.Item1;
                int    c                   = item.Item2;
                object content             = item.Item3;
                NPOI.SS.UserModel.IRow row = null;
                if (sheet.GetRow(r) == null)
                {
                    row = sheet.CreateRow(r);
                }
                else
                {
                    row = sheet.GetRow(r);
                }
                NPOI.SS.UserModel.ICell cell = row.CreateCell(c);
                cell.SetCellValue(content.ToString());
                string objType = item.Item4.ToString();
                #region 类型转换
                try
                {
                    switch (objType)
                    {
                    case "System.String":    //字符串类型
                        cell.SetCellValue(content.ToString());
                        break;

                    case "System.DateTime":    //日期类型
                        DateTime dateV;
                        DateTime.TryParse(content.ToString(), out dateV);
                        string strtime = dateV.ToString("yyyy-MM-dd HH:mm:ss");
                        if (strtime.Substring(11, 8) == "00:00:00")
                        {
                            strtime = dateV.ToString("yyyy-MM-dd");
                        }
                        cell.SetCellValue(strtime);
                        break;

                    case "System.Boolean":    //布尔型
                        bool boolV = false;
                        bool.TryParse(content.ToString(), out boolV);
                        cell.SetCellValue(boolV);
                        break;

                    case "System.Int16":    //整型
                        cell.SetCellValue(Convert.ToInt16(content));
                        break;

                    case "System.Int32":
                        cell.SetCellValue(Convert.ToInt32(content));
                        break;

                    case "System.Int64":
                        cell.SetCellValue(Convert.ToInt64(content));
                        break;

                    case "System.Byte":
                        cell.SetCellValue(Convert.ToInt32(content));
                        break;

                    case "System.Decimal":    //浮点型
                        cell.SetCellValue(Convert.ToDouble(content));
                        break;

                    case "System.Double":
                        cell.SetCellValue(Convert.ToDouble(content));
                        break;

                    case "System.DBNull":    //空值处理
                        cell.SetCellValue("");
                        break;

                    default:
                        cell.SetCellValue(content.ToString());
                        break;
                    }
                }
                catch
                {
                    cell.SetCellValue(content.ToString());
                }
                #endregion
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            byte[] b = ms.ToArray();
            File.WriteAllBytes(fileName, b);
            book = null;
            ms.Close();
            ms.Dispose();
        }
Exemple #21
0
        protected void btnExport_Click(object sender, EventArgs e)
        {
            var list = DAL.WorkPlanRule.Get().Where(a => a.PublishTime.Year == Common.St.ToInt32(selYear.Value)).Select(a =>
            {
                int pid      = 0, id = 0;
                string pname = "", name = "";
                GetIDAndName(a.Project, ref pid, ref id, ref pname, ref name);
                return(new { ID_0 = pid, ID_1 = id, Name_0 = pname, Name_1 = name, M = a.PublishTime.Month, PublishTime = a.PublishTime, Name_3 = a.Project.Name });
            }).GroupBy(a => a.ID_1).Select(a =>
            {
                return(new
                {
                    PName = a.First().Name_0,
                    Name = a.First().Name_1,
                    M1 = a.Where(b => b.M == 1).Count(),
                    M2 = a.Where(b => b.M == 2).Count(),
                    M3 = a.Where(b => b.M == 3).Count(),
                    M4 = a.Where(b => b.M == 4).Count(),
                    M5 = a.Where(b => b.M == 5).Count(),
                    M6 = a.Where(b => b.M == 6).Count(),
                    M7 = a.Where(b => b.M == 7).Count(),
                    M8 = a.Where(b => b.M == 8).Count(),
                    M9 = a.Where(b => b.M == 9).Count(),
                    M10 = a.Where(b => b.M == 10).Count(),
                    M11 = a.Where(b => b.M == 11).Count(),
                    M12 = a.Where(b => b.M == 12).Count(),
                    S1 = string.Join("\n", a.Where(b => b.M == 1).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
                    S2 = string.Join("\n", a.Where(b => b.M == 2).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
                    S3 = string.Join("\n", a.Where(b => b.M == 3).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
                    S4 = string.Join("\n", a.Where(b => b.M == 4).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
                    S5 = string.Join("\n", a.Where(b => b.M == 5).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
                    S6 = string.Join("\n", a.Where(b => b.M == 6).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
                    S7 = string.Join("\n", a.Where(b => b.M == 7).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
                    S8 = string.Join("\n", a.Where(b => b.M == 8).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
                    S9 = string.Join("\n", a.Where(b => b.M == 9).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
                    S10 = string.Join("\n", a.Where(b => b.M == 10).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
                    S11 = string.Join("\n", a.Where(b => b.M == 11).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray()),
                    S12 = string.Join("\n", a.Where(b => b.M == 12).Select(b => b.PublishTime.ToString("M.d") + "(" + b.Name_3 + ")").ToArray())
                });
            }).OrderBy(a => a.PName).Where(a => (txtProjectParent.Value.Trim() == "" || a.PName.IndexOf(txtProjectParent.Value.Trim()) >= 0) && (txtProject.Value.Trim() == "" || a.Name.IndexOf(txtProject.Value.Trim()) >= 0));

            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_project.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
            NPOI.SS.UserModel.ISheet         sheet = book.GetSheet("科教组项目");


            // 内容
            int i = 1;

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



            NPOI.SS.UserModel.IRow row3 = sheet.CreateRow(i);
            row3.CreateCell(0).SetCellValue("总计");
            row3.CreateCell(1).SetCellValue(list.Sum(a => a.M1 + a.M2 + a.M3 + a.M4 + a.M5 + a.M6 + a.M7 + a.M8 + a.M9 + a.M10 + a.M11 + a.M12));
            row3.CreateCell(2).SetCellValue(list.Sum(a => a.M1));
            row3.CreateCell(3).SetCellValue(list.Sum(a => a.M2));
            row3.CreateCell(4).SetCellValue(list.Sum(a => a.M3));
            row3.CreateCell(5).SetCellValue(list.Sum(a => a.M4));
            row3.CreateCell(6).SetCellValue(list.Sum(a => a.M5));
            row3.CreateCell(7).SetCellValue(list.Sum(a => a.M6));
            row3.CreateCell(8).SetCellValue(list.Sum(a => a.M7));
            row3.CreateCell(9).SetCellValue(list.Sum(a => a.M8));
            row3.CreateCell(10).SetCellValue(list.Sum(a => a.M9));
            row3.CreateCell(11).SetCellValue(list.Sum(a => a.M10));
            row3.CreateCell(12).SetCellValue(list.Sum(a => a.M11));
            row3.CreateCell(13).SetCellValue(list.Sum(a => a.M12));



            NPOI.SS.UserModel.ISheet sheet2 = book.GetSheet("上线明细表");


            // 内容
            int j = 1;

            foreach (var o in list)
            {
                NPOI.SS.UserModel.IRow  row2  = sheet2.CreateRow(j);
                NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0);
                cell0.SetCellValue(o.Name);
                NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1);
                cell1.SetCellValue(o.S1);
                NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2);
                cell2.SetCellValue(o.S2);
                NPOI.SS.UserModel.ICell cell3 = row2.CreateCell(3);
                cell3.SetCellValue(o.S3);
                NPOI.SS.UserModel.ICell cell4 = row2.CreateCell(4);
                cell4.SetCellValue(o.S4);
                NPOI.SS.UserModel.ICell cell5 = row2.CreateCell(5);
                cell5.SetCellValue(o.S5);
                NPOI.SS.UserModel.ICell cell6 = row2.CreateCell(6);
                cell6.SetCellValue(o.S6);
                NPOI.SS.UserModel.ICell cell7 = row2.CreateCell(7);
                cell7.SetCellValue(o.S7);
                NPOI.SS.UserModel.ICell cell8 = row2.CreateCell(8);
                cell8.SetCellValue(o.S8);
                NPOI.SS.UserModel.ICell cell9 = row2.CreateCell(9);
                cell9.SetCellValue(o.S9);
                NPOI.SS.UserModel.ICell cell10 = row2.CreateCell(10);
                cell10.SetCellValue(o.S10);
                NPOI.SS.UserModel.ICell cell11 = row2.CreateCell(11);
                cell11.SetCellValue(o.S11);
                NPOI.SS.UserModel.ICell cell12 = row2.CreateCell(12);
                cell12.SetCellValue(o.S12);
                j++;
            }

            // 写入到客户端
            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(selYear.Value + "年项目上线频度表"), System.Text.Encoding.UTF8));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }
        protected void btnExport_Click(object sender, EventArgs e)
        {
            var list = DAL.ProjectRule.Get().Where(a => txtSearch.Value == "" || a.Name.ToLower().IndexOf(txtSearch.Value.ToLower()) >= 0 || (a.Url.Length > 0 && a.Url.ToLower().IndexOf(txtSearch.Value.ToLower()) >= 0) || (a.TestUrl.Length > 0 && a.TestUrl.ToLower().IndexOf(txtSearch.Value.ToLower()) >= 0));

            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("项目列表");

            NPOI.SS.UserModel.IRow  row   = sheet.CreateRow(0);
            NPOI.SS.UserModel.ICell cell0 = row.CreateCell(0);
            cell0.SetCellValue("名称");
            NPOI.SS.UserModel.ICell cell1 = row.CreateCell(1);
            cell1.SetCellValue("域名");
            NPOI.SS.UserModel.ICell cell2 = row.CreateCell(2);
            cell2.SetCellValue("是否显示");
            NPOI.SS.UserModel.ICell cell3 = row.CreateCell(3);
            cell3.SetCellValue("测试地址");
            NPOI.SS.UserModel.ICell cell4 = row.CreateCell(4);
            cell4.SetCellValue("网站文件名");
            NPOI.SS.UserModel.ICell cell5 = row.CreateCell(5);
            cell5.SetCellValue("数据库");
            NPOI.SS.UserModel.ICell cell6 = row.CreateCell(6);
            cell6.SetCellValue("测试用户名");
            NPOI.SS.UserModel.ICell cell7 = row.CreateCell(7);
            cell7.SetCellValue("密码");
            NPOI.SS.UserModel.ICell cell8 = row.CreateCell(8);
            cell8.SetCellValue("备注");
            // 内容
            int i = 1;

            foreach (var o in list)
            {
                NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);

                NPOI.SS.UserModel.ICell cell00 = row2.CreateCell(0);
                cell00.SetCellValue(o.Name);
                NPOI.SS.UserModel.ICell cell01 = row2.CreateCell(1);
                cell01.SetCellValue(o.Url);
                NPOI.SS.UserModel.ICell cell02 = row2.CreateCell(2);
                cell02.SetCellValue(o.IsShow == 1?"是":"否");
                NPOI.SS.UserModel.ICell cell03 = row2.CreateCell(3);
                cell03.SetCellValue(o.TestUrl);
                NPOI.SS.UserModel.ICell cell04 = row2.CreateCell(4);
                cell04.SetCellValue(o.SiteFileName);
                NPOI.SS.UserModel.ICell cell05 = row2.CreateCell(5);
                cell05.SetCellValue(o.DatabaseName);
                NPOI.SS.UserModel.ICell cell06 = row2.CreateCell(6);
                cell06.SetCellValue(o.TestUserName);
                NPOI.SS.UserModel.ICell cell07 = row2.CreateCell(7);
                cell07.SetCellValue(o.TestPassword);
                NPOI.SS.UserModel.ICell cell08 = row2.CreateCell(8);
                cell08.SetCellValue(o.Remark);



                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();
        }
Exemple #23
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();
        }
Exemple #24
0
        public void Object2Cell(System.Reflection.FieldInfo prop, object obj, NPOI.SS.UserModel.ICell cell)
#endif
        {
            //如果是一个公式计算结果的Cell,不能往里面生写
            if (cell.CellType == NPOI.SS.UserModel.CellType.Formula)
            {
                return;
            }
            var type = obj.GetType();

            if (type == typeof(sbyte))
            {
                var value = (sbyte)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type == typeof(Int16))
            {
                var value = (Int16)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type == typeof(Int32))
            {
                var value = (Int32)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type == typeof(Int64))
            {
                var value = (Int64)obj;
                cell.SetCellValue(value);
                return;
            }
            if (type == typeof(byte))
            {
                var value = (byte)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type == typeof(UInt16))
            {
                var value = (UInt16)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type == typeof(UInt32))
            {
                var value = (UInt32)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type == typeof(UInt64))
            {
                var value = (UInt64)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type == typeof(double))
            {
                var value = (double)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type == typeof(float))
            {
                var value = (float)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type == typeof(string))
            {
                var value = (string)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type == typeof(bool))
            {
                var value = (bool)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type == typeof(DateTime))
            {
                var value = (DateTime)obj;
                cell.SetCellValue(value);
                return;
            }
            else if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(List <>))
            {
                var argType = type.GenericTypeArguments[0];
                //var atts = argType.GetCustomAttributes(typeof(EngineNS.IO.Serializer.ExcelSheetAttribute), true);
                //if (atts == null || atts.Length == 0)
                //    return;
                //var sheetAttr = atts[0] as EngineNS.IO.Serializer.ExcelSheetAttribute;

                string sheetName = "LST_" + prop.Name;
                int    index     = -1;
                var    link      = cell.Hyperlink as HSSFHyperlink;
                if (link != null && link.Address.Contains($"{sheetName}!A"))
                {
                    var suffix = link.Address.Substring($"{sheetName}!A".Length);
                    index = System.Convert.ToInt32(suffix);
                    if (index > 0)
                    {
                        index--;
                    }
                }
                var sheet = this.GetSheetSure($"{sheetName}");
                //var header = InitSheet(sheet, type);
                var row = GetRowSure(sheet, index);
                FillObjectList2Row(obj as System.Collections.IList, row, argType);

                if (link == null)
                {
                    link = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
                }
                link.Address = $"{sheetName}!A{row.RowNum + 1}";
                cell.SetCellValue(link.Address);
                cell.Hyperlink = link;

                var target = row.GetCell(0);
                if (target != null)
                {
                    var linkBack = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
                    linkBack.Address = $"{cell.Sheet.SheetName}!{GetColumnString(cell.ColumnIndex)}{cell.RowIndex + 1}";
                    target.Hyperlink = linkBack;
                    target.SetCellValue(linkBack.Address);
                }
            }
            else
            {
                var atts = type.GetCustomAttributes(typeof(EngineNS.IO.Serializer.ExcelSheetAttribute), true);
                if (atts == null || atts.Length == 0)
                {
                    return;
                }

                var sheetAttr = atts[0] as EngineNS.IO.Serializer.ExcelSheetAttribute;
                var sheet     = this.GetSheetSure(sheetAttr.SheetName);
                var header    = InitSheet(sheet, type);

                string sheetName = sheetAttr.SheetName;
                int    index     = -1;
                var    link      = cell.Hyperlink as HSSFHyperlink;
                if (link != null && link.Address.Contains($"{sheetName}!A"))
                {
                    var suffix = link.Address.Substring($"{sheetName}!A".Length);
                    index = System.Convert.ToInt32(suffix);
                    if (index > 0)
                    {
                        index--;
                    }
                }
                var row = GetRowSure(sheet, index);
                FillObject2Row(obj, row, header, type);

                if (link == null)
                {
                    link = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
                }
                link.Address = $"{sheetName}!A{row.RowNum + 1}";
                cell.SetCellValue(link.Address);
                cell.Hyperlink = link;

                var target = GetCellSure(row, 0, typeof(string));
                if (target != null)
                {
                    var linkBack = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
                    linkBack.Address = $"{cell.Sheet.SheetName}!{GetColumnString(cell.ColumnIndex)}{cell.RowIndex+1}";
                    target.Hyperlink = linkBack;
                    target.SetCellValue(linkBack.Address);
                }
            }
        }
Exemple #25
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();
        }
        /// <summary>
        /// 输出数据表格
        /// </summary>
        /// <param name="workbook">工作文档</param>
        /// <param name="normalStyle">普通样式(用于表格内容)</param>
        /// <param name="boldStyle">粗体样式(用于表格头部)</param>
        /// <param name="table">表格数据</param>
        public static void writeSheet(NPOI.XSSF.UserModel.XSSFWorkbook workbook, NPOI.SS.UserModel.ICellStyle normalStyle, NPOI.SS.UserModel.ICellStyle boldStyle, DataTable table)
        {
            //创建Sheet页
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();

            //行号
            int rowIndex = 0;

            //是否需要输出表头
            bool isNeedCreateHeader = true;

            //输出数据到Excel
            foreach (DataRow rowData in table.Rows)
            {
                //忽略空数据行
                if (rowData.ItemArray == null || rowData.ItemArray.Length != table.Columns.Count)
                {
                    continue;
                }

                //列号
                int colIndex = 0;

                //Excel行
                NPOI.SS.UserModel.IRow row = null;

                //是否需要输入表头
                if (isNeedCreateHeader)
                {
                    isNeedCreateHeader = false;

                    //创建行
                    row = sheet.CreateRow(rowIndex);
                    //输出列名到Excel
                    colIndex = 0;
                    foreach (DataColumn kvp in table.Columns)
                    {
                        //列名
                        //创建列
                        NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex);
                        //设置样式
                        cell.CellStyle = boldStyle;
                        //设置数据
                        cell.SetCellValue(kvp.ColumnName);
                        colIndex++;
                    }
                    rowIndex++;
                }

                //创建行
                row = sheet.CreateRow(rowIndex);
                //输出列值到Excel
                colIndex = 0;
                foreach (object val in rowData.ItemArray)
                {
                    //列值
                    //创建列
                    NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex);
                    //设置样式
                    cell.CellStyle = normalStyle;
                    //设置数据
                    //判断是否为空
                    if (val != null)
                    {
                        //不为空
                        //判断是否为RTF内容
                        if (val.GetType().Name.Equals(typeof(NPOI.XSSF.UserModel.XSSFRichTextString).Name))
                        {
                            //RTF内容
                            cell.SetCellValue((NPOI.XSSF.UserModel.XSSFRichTextString)val);
                        }
                        else
                        {
                            //文本内容
                            cell.SetCellValue(val.ToString());
                        }
                    }
                    else
                    {
                        //为空
                        cell.SetCellValue(string.Empty);
                    }
                    colIndex++;
                }
                rowIndex++;
            }

            //Excel列宽自动适应
            if (table.Rows.Count >= 1 && sheet.GetRow(0) != null)
            {
                for (int k = 0; k < sheet.GetRow(0).Cells.Count; k++)
                {
                    sheet.AutoSizeColumn(k);
                }
            }
        }
Exemple #27
0
        public void Export(Stream ms, DataTable dt, string postfix)
        {
            //var fileName = m_Handler.GetFileName();
            NPOI.SS.UserModel.IWorkbook book = null;
            if (postfix == ".xls")
            {
                book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            }
            else if (postfix == ".xlsx")
            {
                book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            }
            else
            {
                throw new FinanceException(FinanceResult.INCORRECT_STATE, "无效的文件名");
            }
            m_Handler.Encode(ref dt);

            //WriteExcel(ref book, dt);
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");

            // 添加表头
            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            int index = 0;

            foreach (DataColumn item in dt.Columns)
            {
                NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                cell.SetCellValue(item.Caption);
                index++;
            }

            // 添加数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                index = 0;
                row   = sheet.CreateRow(i + 1);
                foreach (DataColumn item in dt.Columns)
                {
                    NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
                    cell.SetCellType(NPOI.SS.UserModel.CellType.String);
                    cell.SetCellValue(dt.Rows[i][item].ToString());
                    index++;
                }
            }
            //// 写入
            //MemoryStream ms = new MemoryStream();
            book.Write(ms);
            book = null;

            //using (FileStream fs = new FileStream("E:\\Temp\\test.xls", FileMode.Create, FileAccess.Write))
            //{
            //    byte[] data = ms.ToArray();
            //    fs.Write(data, 0, data.Length);
            //    fs.Flush();
            //}

            //ms.Close();
            //ms.Dispose();
        }
 private void SetNewCell(NPOI.SS.UserModel.IRow row, int iIdx, string strValue, NPOI.SS.UserModel.ICellStyle style)
 {
     NPOI.SS.UserModel.ICell cell = row.CreateCell(iIdx, NPOI.SS.UserModel.CellType.String);
     cell.SetCellValue(strValue);
     cell.CellStyle = style;
 }
Exemple #29
0
        public static void CreateExcelColorFile(string fileName)
        {
            System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);

            // 创建Workbook。
            NPOI.SS.UserModel.IWorkbook workbook = null;// new NPOI.XSSF.UserModel.XSSFWorkbook();
            if (fileInfo.Extension == ".xlsx")
            {
                workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
            }
            else if (fileInfo.Extension == ".xls")
            {
                workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
            }
            else
            {
                return;
            }
            // 创建Sheet
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("ExcelColor");
            // 设置列宽。
            sheet.SetColumnWidth(0, 15 * 256);
            sheet.SetColumnWidth(1, 15 * 256);
            sheet.SetColumnWidth(2, 20 * 256);
            sheet.SetColumnWidth(3, 20 * 256);
            sheet.SetColumnWidth(4, 15 * 256);
            // 创建标题。
            int rowIndex = 0;

            NPOI.SS.UserModel.ICell cell = sheet.CreateRow(rowIndex).CreateCell(0);
            cell.SetCellValue("Excel颜色");
            cell.CellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Arial", 16, System.Drawing.FontStyle.Bold));
            // 空一行
            rowIndex++;
            // 创建标题行
            rowIndex++;
            NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
            row.Height = 30 * 20;
            NPOI.SS.UserModel.ICellStyle columnHeadCellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center);
            cell = row.CreateCell(0);
            cell.SetCellValue("索引");
            cell.CellStyle = columnHeadCellStyle;

            cell = row.CreateCell(1);
            cell.SetCellValue("索引(NPOI)");
            cell.CellStyle = columnHeadCellStyle;

            cell = row.CreateCell(2);
            cell.SetCellValue("名称");
            cell.CellStyle = columnHeadCellStyle;
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 2, 3));

            cell = row.CreateCell(4);
            cell.SetCellValue("十六进制代码");
            cell.CellStyle = columnHeadCellStyle;

            ExcelColor.KnownColors.ForEach(x =>
            {
                rowIndex++;
                NPOI.SS.UserModel.IRow contentRow = sheet.CreateRow(rowIndex);
                contentRow.Height = 30 * 20;
                NPOI.SS.UserModel.ICellStyle contentCellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center);
                NPOI.SS.UserModel.ICell contentCell           = contentRow.CreateCell(0);
                contentCell.SetCellValue(x.Index);
                contentCell.CellStyle = contentCellStyle;

                contentCell = contentRow.CreateCell(1);
                contentCell.SetCellValue(x.IndexNPOI + "(NPOI)");
                contentCell.CellStyle = contentCellStyle;

                contentCell = contentRow.CreateCell(2);
                contentCell.SetCellValue(x.Name);
                contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Right, NPOI.SS.UserModel.VerticalAlignment.Center);

                contentCell = contentRow.CreateCell(3);
                contentCell.SetCellValue(x.Description);
                contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Left, NPOI.SS.UserModel.VerticalAlignment.Center);

                contentCell = contentRow.CreateCell(4);
                contentCell.SetCellValue(x.HexString);
                contentCell.CellStyle = contentCellStyle;
            });
            // 保存到文件。
            System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite);
            workbook.Write(fileStream);
            fileStream.Close();
            workbook.Close();
        }
            /// <summary>
            /// 设置单元格值,支持合并单元格
            /// </summary>
            /// <param name="firstRow"></param>
            /// <param name="lastRow"></param>
            /// <param name="firstCol"></param>
            /// <param name="lastCol"></param>
            /// <param name="value"></param>
            /// <param name="sheet"></param>
            public void SetCellValue(int firstRow, int lastRow, int firstCol, int lastCol, object value, NPOI.SS.UserModel.ISheet sheet)
            {
                NPOI.SS.UserModel.IRow  row  = null;
                NPOI.SS.UserModel.ICell cell = null;

                for (int r = firstRow; r <= lastRow; r++)
                {
                    row = sheet.GetRow(r);
                    if (row == null)
                    {
                        row = sheet.CreateRow(r);
                    }
                    for (int c = firstCol; c <= lastCol; c++)
                    {
                        cell = row.GetCell(c);
                        if (cell == null)
                        {
                            cell = row.CreateCell(c);
                        }
                    }
                }
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol));

                row  = sheet.GetRow(firstRow);
                cell = row.GetCell(firstCol);
                if (value is bool)
                {
                    cell.SetCellValue((bool)value);
                }
                if (value is string)
                {
                    string svalue = (string)value;
                    if (svalue != null && svalue != "" && svalue.Substring(0, 1) == "=")
                    {
                        cell.SetCellFormula(svalue.Substring(1));
                    }
                    else
                    {
                        cell.SetCellValue((string)svalue);
                    }
                }
                if (value is NPOI.SS.UserModel.IRichTextString)
                {
                    cell.SetCellValue((NPOI.SS.UserModel.IRichTextString)value);
                }
                if (value is DateTime)
                {
                    if (this.DateTimeCellStyle != null)
                    {
                        cell.CellStyle = this.DateTimeCellStyle;
                    }
                    cell.SetCellValue((DateTime)value);
                }
                if (value is double)
                {
                    cell.SetCellValue((double)value);
                }
                if (value is int)
                {
                    cell.SetCellValue((int)value);
                }
            }