コード例 #1
0
    //需要记录行数
    ExcelRow GetExcelRow(IRow row, int rowNum)
    {
        ExcelRow excelRow = new ExcelRow();

        excelRow.row = rowNum;
        if (row == null)
        {
            return(excelRow);
        }

        excelRow.row      = row.RowNum;
        excelRow.cellList = new List <ExcelCell>();
        for (int i = row.FirstCellNum; i < row.LastCellNum; i++)
        {
            excelRow.AddCell(GetExcelCell(row.GetCell(i), i));
        }
        return(excelRow);
    }
コード例 #2
0
        /// <summary>
        /// 数据导出
        /// </summary>
        /// <param name="searchModel">查询参数</param>
        /// <param name="fileFormat">文件格式,2003/2007</param>
        /// <returns>用于下载的Excel文件内容</returns>
        public ActionResult Export(SysSearchModel searchModel, int?fileFormat)
        {
            searchModel.GroupType = "Custom";
            var           response = GetStatisData(searchModel);
            bool          xlsx     = (fileFormat ?? 2007) == 2007;
            ExcelExporter ee       = new ExcelExporter("材料动态分析表", xlsx);

            ee.SetColumnTitles("序号, 地区, 报告总数, 不合格数量, 不合格数量占比, 生产厂家总数, 不合格生产商家数量, 不合格生产厂家占比 ");
            if (response.IsValid)
            {
                var bucks = response.Aggs.Terms("Custom").Buckets;
                //countByArea 地区报告总数   unQualifyCount 地区报告不合格总数  countUnitByArea 地区厂家总数   unQualifyUnitCountByArea 地区厂家不合格总数
                Dictionary <string, int> countByArea, unQualifyCount, countUnitByArea, unQualifyUnitCountByArea;
                GetSearchResult(bucks, out countByArea, out unQualifyCount, out countUnitByArea, out unQualifyUnitCountByArea);
                var index = 1;
                foreach (var item in countByArea)
                {
                    var unQualifyCountRow    = 0;
                    var unitCountByArea      = 0;
                    var unqualifyUnitCount   = 0;
                    var isUnqualify          = unQualifyCount.TryGetValue(item.Key, out unQualifyCountRow); //isUnqualify 标识是否有不合格报告
                    var isUnitCount          = countUnitByArea.TryGetValue(item.Key, out unitCountByArea);
                    var isUnqualifyUnitCount = unQualifyUnitCountByArea.TryGetValue(item.Key, out unqualifyUnitCount);

                    ExcelRow row = ee.AddRow();
                    row.AddCell(index++);
                    row.AddCell(item.Key);
                    row.AddCell(item.Value);
                    row.AddCell(isUnqualify ? unQualifyCountRow : 0);
                    row.AddCell(item.Value == 0 ? "0" : Math.Round(((unQualifyCountRow * 1.00 / item.Value) * 100), 2).ToString() + "%");
                    row.AddCell(unitCountByArea.ToString());
                    row.AddCell(unqualifyUnitCount.ToString());
                    row.AddCell(unitCountByArea == 0 ? "0" : Math.Round(((unqualifyUnitCount * 1.00 / unitCountByArea) * 100), 2).ToString() + "%");
                }
            }
            // 改动4:返回字节流
            return(File(ee.GetAsBytes(), ee.MIME, ee.FileName));
        }
コード例 #3
0
ファイル: Excel.cs プロジェクト: 294797392/DotNEToolkit
        /// <summary>
        /// 把ExcelSheet对象保存成Excel文件
        /// </summary>
        /// <param name="excelSheet"></param>
        /// <param name="filePath"></param>
        /// <param name="version"></param>
        /// <returns></returns>
        public static int Write <TRow>(string filePath, IList <TRow> dataList, ExcelVersions version = ExcelVersions.Xls) where TRow : IExcelRow
        {
            List <PropertyAttribute <ExcelColumnAttribute> > properties = Reflections.GetPropertyAttribute <ExcelColumnAttribute>(typeof(TRow));

            if (properties.Count == 0)
            {
                return(DotNETCode.SUCCESS);
            }

            ExcelSheet sheet = new ExcelSheet();

            // 先写第一行,相当于是标题
            ExcelRow titleRow = new ExcelRow();
            IEnumerable <ExcelColumnAttribute> columns = properties.Select(v => v.Attribute);

            foreach (ExcelColumnAttribute column in columns)
            {
                titleRow.AddCell(column.Name, ExcelCellTypes.String);
            }
            sheet.AddRow(titleRow);

            foreach (TRow row in dataList)
            {
                ExcelRow excelRow = new ExcelRow();

                foreach (PropertyAttribute <ExcelColumnAttribute> property in properties)
                {
                    // 当前要写入的列
                    object value = property.Property.GetValue(row, null);

                    excelRow.AddCell(value, property.Attribute.Type);
                }

                sheet.AddRow(excelRow);
            }

            return(QuickWrite(sheet, filePath, version));
        }
コード例 #4
0
ファイル: Excel.cs プロジェクト: 294797392/DotNEToolkit
        public static int QuickRead(string filePath, ReadOptions options, out ExcelSheet excelSheet)
        {
            excelSheet = null;

            if (!File.Exists(filePath))
            {
                return(DotNETCode.FILE_NOT_FOUND);
            }

            using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                string    extention = Path.GetExtension(filePath);
                IWorkbook workbook  = OpenRead(extention, fs);

                ISheet sheet = workbook.GetSheetAt(0);

                // 还剩leftRow行没读取
                int leftRow = sheet.PhysicalNumberOfRows;

                excelSheet      = new ExcelSheet();
                excelSheet.Name = sheet.SheetName;
                int currentRow = 0;

                // 要注意处理空行
                while (leftRow > 0)
                {
                    IRow row = sheet.GetRow(currentRow);
                    if (row == null)
                    {
                        // 该行是空行,不计算在内
                        currentRow++;
                        continue;
                    }

                    int numCell = row.PhysicalNumberOfCells;

                    ExcelRow excelRow = new ExcelRow();

                    for (int cellnum = 0; cellnum < numCell; cellnum++)
                    {
                        ICell cell = row.GetCell(cellnum);
                        if (cell == null || cell.CellType == CellType.Blank)
                        {
                            // 空列,根据选项进行处理
                            if (options.HasFlag(ReadOptions.IgnoreEmptyCell))
                            {
                            }
                            else if (options.HasFlag(ReadOptions.KeepEmptyCell))
                            {
                                excelRow.AddCell(null, ExcelCellTypes.Null);
                            }
                            continue;
                        }

                        switch (cell.CellType)
                        {
                        case CellType.Numeric:
                        {
                            excelRow.AddCell(cell.NumericCellValue, ExcelCellTypes.Numberic);
                            break;
                        }

                        case CellType.String:
                        {
                            excelRow.AddCell(cell.StringCellValue, ExcelCellTypes.String);
                            break;
                        }

                        case CellType.Blank:
                        {
                            // 空列,在107行进行了处理
                            break;
                        }

                        default:
                            logger.ErrorFormat("不支持的Cell数据类型, {0}", cell.CellType);
                            return(DotNETCode.NOT_SUPPORTED);
                        }
                    }

                    excelSheet.AddRow(excelRow);

                    leftRow--;
                    currentRow++;
                }

                fs.Close();
            }

            return(DotNETCode.SUCCESS);
        }
コード例 #5
0
        public ActionResult Export(int?fileFormat)
        {
            var           reportUploadResult = GetReportUploadStatis();
            bool          xlsx = (fileFormat ?? 2007) == 2007;
            ExcelExporter ee   = new ExcelExporter("报告上传统计", xlsx);

            ee.SetColumnTitles("序号, 检测机构,最新上传时间,未上传间隔 ");
            if (reportUploadResult.IsValid)
            {
                var bucks    = reportUploadResult.Aggs.Terms(aggeKey).Buckets;
                int userId   = HttpContext.User.Identity.GetUserId <Int32>();
                var allInsts = GetCurrentEliminateIsUerInsts();
                int index    = 1;
                Dictionary <string, DateTime> dicExist = new Dictionary <string, DateTime>();
                foreach (var item in bucks)
                {
                    var tbuck = item.TopHits(tBuckKey);
                    if (tbuck != null && tbuck.Total > 0)
                    {
                        var lastItem = tbuck.Documents <es_t_bp_item>().First();
                        if (lastItem != null && lastItem.UPLOADTIME.HasValue)
                        {
                            dicExist[item.Key] = lastItem.UPLOADTIME.Value;
                        }
                    }
                }

                foreach (var custom in allInsts)
                {
                    //if (!checkUnitService.IsCanUploadUnit(custom.Key))
                    //{
                    //    continue;
                    //}

                    var currentDt = DateTime.Now;
                    if (dicExist.ContainsKey(custom.Key))
                    {
                        ExcelRow row = ee.AddRow();
                        row.AddCell(index.ToString());
                        row.AddCell(custom.Value);

                        var lastDt = dicExist[custom.Key];
                        var dtSpan = currentDt - lastDt;
                        row.AddCell(lastDt.ToString("yyyy-MM-dd HH:mm"));
                        row.AddCell("{0}天{1}小时{2}分".Fmt(dtSpan.Days, dtSpan.Hours, dtSpan.Minutes));
                        index++;
                    }
                    else
                    {
                        ExcelRow row = ee.AddRow();
                        row.AddCell(index.ToString());
                        row.AddCell(custom.Value);

                        var lastDt = DateTime.Now.AddYears(-1);
                        var dtSpan = currentDt - lastDt;
                        row.AddCell(lastDt.ToString("yyyy-MM-dd HH:mm"));
                        row.AddCell("{0}天{1}小时{2}分".Fmt(dtSpan.Days, dtSpan.Hours, dtSpan.Minutes));
                        index++;
                    }
                }
            }
            // 改动4:返回字节流
            return(File(ee.GetAsBytes(), ee.MIME, ee.FileName));
        }
コード例 #6
0
        public ActionResult Export(SysSearchModel model, int?fileFormat)
        {
            ISearchResponse <es_t_bp_item> response = GetSearchResult(model);
            var           allItems          = itemNameService.GetAllItemName();
            var           allInsts          = checkUnitService.GetAllCheckUnit();
            var           reportConclusions = sysDictService.GetDictsByKey("ReportConclusionCode");
            bool          xlsx = (fileFormat ?? 2007) == 2007;
            ExcelExporter ee   = new ExcelExporter("监督抽检", xlsx);

            ee.SetColumnTitles("序号, 工程名称, 合格, 机构名称, 检测项目, 报告编号, 样品编号, 委托日期, 检测日期, 数据状态");
            int pos   = model.posStart.HasValue ? model.posStart.Value : 0;
            int index = pos;

            foreach (var item in response.Documents)
            {
                ExcelRow row = ee.AddRow();

                row.AddCell(index + 1);
                row.AddCell(item.PROJECTNAME);
                row.AddCell(SysDictUtility.GetKeyFromDic(reportConclusions, item.CONCLUSIONCODE, "/"));
                row.AddCell(checkUnitService.GetCheckUnitByIdFromAll(allInsts, item.CUSTOMID));
                if (item.ITEMCHNAME.IsNullOrEmpty())
                {
                    row.AddCell(itemNameService.GetItemCNNameFromAll(allItems, item.REPORTJXLB, item.ITEMNAME));
                }
                else
                {
                    row.AddCell(item.ITEMCHNAME);
                }
                row.AddCell(item.REPORTNUM);
                row.AddCell(item.SAMPLENUM);
                row.AddCell(item.ENTRUSTDATE.HasValue ? item.ENTRUSTDATE.Value.ToString("yyyy-MM-dd") : "/");
                row.AddCell(item.CHECKDATE.HasValue ? item.CHECKDATE.Value.ToString("yyyy-MM-dd") : "/");
                row.AddCell(reportService.GetReportDataStatus(item.SAMPLEDISPOSEPHASEORIGIN));
                index++;
            }
            return(File(ee.GetAsBytes(), ee.MIME, ee.FileName));
        }
コード例 #7
0
        static void Main(string[] args)
        {
            List <ExcelTable> l = new List <ExcelTable>();

            for (int k = 0; k < 4; k++)
            {
                ExcelTable table = new ExcelTable();
                table.Landscape = true;
                //table.Title = "疑似黑广播信号出现情况" + (k + 1);
                table.ColumnCount = 10;
                int[] columnswidth = new int[table.ColumnCount];

                ExcelTitle title = new ExcelTitle();
                title.TableTitle = "疑似黑广播信号出现情况" + (k + 1);
                table.Title      = title;

                ExcelHeader header = new ExcelHeader();

                ExcelRow headerrow = (ExcelRow)table.CreateRow();
                headerrow.Height = 20;
                ExcelCell headercell = (ExcelCell)headerrow.CreateCell();
                headercell.Value   = "表格描述信息";
                headercell.Colspan = table.ColumnCount;
                headerrow.AddCell(headercell);

                List <Row> rows = new List <Row>();
                rows.Add(new ExcelRow());
                rows.Add(headerrow);
                header.Rows  = rows;
                table.Header = header;

                ExcelFooter footer = new ExcelFooter();
                //ExcelRow footerrow = (ExcelRow)table.CreateRow();
                //footerrow.Height = 200;
                //ExcelCell footercell = (ExcelCell)footerrow.CreateCell();
                //byte[] b = System.IO.File.ReadAllBytes(@"C:\Users\HRDS-ZENGPEIFENG\Pictures\th2X7ZWG1D.jpg");
                //footercell.Value = Convert.ToBase64String(b);
                //footercell.Colspan = table.ColumnCount;
                //footercell.CellType = NPOIHelper.NPOI.Common.CellTypes.Image;
                //footerrow.AddCell(footercell);

                ExcelRow  footerrow2  = (ExcelRow)table.CreateRow();
                ExcelCell footercell2 = (ExcelCell)footerrow2.CreateCell();
                footercell2.Colspan  = table.ColumnCount;
                footercell2.CellType = NPOIHelper.NPOI.Common.CellTypes.String;
                footercell2.Value    = "表格描述信息";
                footerrow2.AddCell(footercell2);

                //footercell2 = (ExcelCell)footerrow2.CreateCell();
                //footercell2.CellType = NPOIHelper.NPOI.Common.CellTypes.String;
                //footercell2.Value = "表格描述信息2";
                //footerrow2.AddCell(footercell2);
                footerrow2.HaveRowBreak = true;

                List <Row> footerrows = new List <Row>();
                footerrows.Add(new ExcelRow());
                //footerrows.Add(footerrow);
                footerrows.Add(footerrow2);
                footer.Rows  = footerrows;
                table.Footer = footer;

                ExcelTableHeader tableheader = new ExcelTableHeader();
                ExcelTableBody   tablebody   = new ExcelTableBody();

                //throws.Add(new )

                for (int r = 0; r < table.ColumnCount; r++)
                {
                    ExcelRow row;
                    if (r == 0 || r == 1)
                    {
                        row        = (ExcelRow)table.CreateRow();
                        row.Height = 28;
                    }
                    else
                    {
                        row = (ExcelRow)table.CreateRow();
                    }

                    for (int i = 0; i < table.ColumnCount; i++)
                    {
                        ExcelCell cell = (ExcelCell)row.CreateCell();
                        if (r == 4 && i == 5)
                        {
                            cell.IsBasedOnDefaultStyle = true;
                            cell.Style = "font-color:#03A8F4;border-color:#03A8F4;font-size:50;";
                        }
                        //if (i == 1)
                        //{
                        //    cell.CellType = NPOIHelper.NPOI.Common.CellTypes.Numeric;
                        //    cell.Value = r * i;
                        //    cell.Style = "border:thin;font-color:red;font-weight:normal;text-align:left;";
                        //}
                        //else
                        //{
                        cell.CellType = NPOIHelper.NPOI.Common.CellTypes.String;
                        if (r == 0 || r == 1)
                        {
                            cell.Value      = "标题" + i + "\r\n(单位)";
                            columnswidth[i] = 12;
                        }
                        else
                        {
                            cell.Value = "行:" + r + " 列:" + i;
                        }
                        //}

                        //cell.FontColor = HSSFColor.BlueGrey.Index;
                        row.AddCell(cell);
                    }
                    if (r == 0 || r == 1)
                    {
                        tableheader.AddRow(row);
                    }
                    else
                    {
                        tablebody.AddRow(row);
                    }
                }
                table.TableHeader  = tableheader;
                table.TableBody    = tablebody;
                table.ColumnWidths = columnswidth;
                l.Add(table);
            }
            ExcelHelper  excelhelper = new ExcelHelper(l);
            MemoryStream s           = excelhelper.RenderToXls();
            bool         issaved     = excelhelper.SaveToFile(s, "d:/test.xls");

            //issaved = false;
            if (issaved)
            {
                IPrint proxy = null;
                RemotingConfiguration.Configure(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile, false);
                proxy = (IPrint)Activator.GetObject(typeof(IPrint), "tcp://172.39.8.173:1235/Print/PrintURL");
                proxy.ExcelPrint("d:/test.xls", "疑似黑广播信号出现情况1", new PrintCallBackHandler());

                //excelhelper.ExcelPrint("d:/test.xls", "疑似黑广播信号出现情况1");
            }
        }