Examples with DataFormat org.apache.poi.ss.usermodel.DataFormat used on opensource projects

Example 16 with DataFormat

use of org.apache.poi.ss.usermodel.DataFormat in project hale by halestudio.

the class XLSCellStyles method getHeaderStyle.

/**
 * @param workbook the workbook of the cell
 * @return the header cell style
 */
public static CellStyle getHeaderStyle(Workbook workbook) {
    CellStyle headerStyle = workbook.createCellStyle();
    Font headerFont = workbook.createFont();
    DataFormat df = workbook.createDataFormat();
    // use bold font
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);
    // set a medium border
    headerStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    // set cell data format to text
    headerStyle.setDataFormat(df.getFormat("@"));
    return headerStyle;
}

Example 17 with DataFormat

use of org.apache.poi.ss.usermodel.DataFormat in project pentaho-kettle by pentaho.

the class ExcelWriterStep method setDataFormat.

/**
 * Set specified cell format
 *
 * @param excelFieldFormat the specified format
 * @param cell             the cell to set up format
 */
private void setDataFormat(String excelFieldFormat, Cell cell) {
    if (log.isDebug()) {
        logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.SetDataFormat", excelFieldFormat, CellReference.convertNumToColString(cell.getColumnIndex()), cell.getRowIndex()));
    }
    DataFormat format = data.wb.createDataFormat();
    short formatIndex = format.getFormat(excelFieldFormat);
    CellStyle style = data.wb.createCellStyle();
    style.cloneStyleFrom(cell.getCellStyle());
    style.setDataFormat(formatIndex);
    cell.setCellStyle(style);
}

Example 18 with DataFormat

use of org.apache.poi.ss.usermodel.DataFormat in project pentaho-kettle by pentaho.

the class ExcelWriterStep_StyleFormatTest method createStepData.

/**
 * Setup the data necessary for Excel Writer step
 *
 * @param fileType
 * @throws KettleException
 */
private void createStepData(String fileType) throws KettleException {
    stepData = new ExcelWriterStepData();
    stepData.inputRowMeta = step.getInputRowMeta().clone();
    stepData.outputRowMeta = step.getInputRowMeta().clone();
    // we don't run transformation so ExcelWriterStep.processRow() doesn't get executed
    // we populate the ExcelWriterStepData with bare minimum required values
    CellReference cellRef = new CellReference(stepMeta.getStartingCell());
    stepData.startingRow = cellRef.getRow();
    stepData.startingCol = cellRef.getCol();
    stepData.posX = stepData.startingCol;
    stepData.posY = stepData.startingRow;
    int numOfFields = stepData.inputRowMeta.size();
    stepData.fieldnrs = new int[numOfFields];
    stepData.linkfieldnrs = new int[numOfFields];
    stepData.commentfieldnrs = new int[numOfFields];
    for (int i = 0; i < numOfFields; i++) {
        stepData.fieldnrs[i] = i;
        stepData.linkfieldnrs[i] = -1;
        stepData.commentfieldnrs[i] = -1;
    }
    // we avoid reading/writing Excel files, so ExcelWriterStep.prepareNextOutputFile() doesn't get executed
    // create Excel workbook object
    stepData.wb = stepMeta.getExtension().equalsIgnoreCase("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook();
    stepData.sheet = stepData.wb.createSheet();
    stepData.file = null;
    stepData.clearStyleCache(numOfFields);
    // we avoid reading template file from disk
    // so set beforehand cells with custom style and formatting
    DataFormat format = stepData.wb.createDataFormat();
    Row xlsRow = stepData.sheet.createRow(0);
    // Cell F1 has custom style applied, used as template
    Cell cell = xlsRow.createCell(5);
    CellStyle cellStyle = stepData.wb.createCellStyle();
    cellStyle.setBorderRight(BorderStyle.THICK);
    cellStyle.setFillPattern(FillPatternType.FINE_DOTS);
    cell.setCellStyle(cellStyle);
    // Cell G1 has same style, but also a custom data format
    cellStyle = stepData.wb.createCellStyle();
    cellStyle.cloneStyleFrom(cell.getCellStyle());
    cell = xlsRow.createCell(6);
    cellStyle.setDataFormat(format.getFormat("##0,000.0"));
    cell.setCellStyle(cellStyle);
}

Example 19 with DataFormat

use of org.apache.poi.ss.usermodel.DataFormat in project sinsim by WilsonHu.

the class MachineOrderController method exportToFinaceExcel.

@PostMapping("/exportToFinaceExcel")
public Result exportToFinaceExcel(Integer id, Integer contract_id, String order_num, String contract_num, String status, String sellman, String customer, String marketGroupName, // 这个是查询创建日期
String query_start_time, // 这个是查询创建日期
String query_finish_time, // 这个是查询审核日期
String queryStartTimeSign, // 这个是查询审核日期
String queryFinishTimeSign, String machine_name, // 订单签核的当前步骤
String oderSignCurrentStep, String searchDepartment, @RequestParam(defaultValue = "true") Boolean is_fuzzy) {
    List<MachineOrderDetail> list = machineOrderService.selectOrder(id, contract_id, order_num, contract_num, status, sellman, customer, marketGroupName, query_start_time, query_finish_time, queryStartTimeSign, queryFinishTimeSign, machine_name, oderSignCurrentStep, searchDepartment, is_fuzzy);
    HSSFWorkbook wb = null;
    FileOutputStream out = null;
    String downloadPath = "";
    /*
            返回给docker外部下载
                */
    String downloadPathForNginx = "";
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
    String dateString;
    try {
        // 生成一个空的Excel文件
        wb = new HSSFWorkbook();
        Sheet sheet1 = wb.createSheet("账务报表");
        // 设置标题行格式
        HSSFCellStyle headcellstyle = wb.createCellStyle();
        HSSFFont headfont = wb.createFont();
        headfont.setFontHeightInPoints((short) 10);
        // 粗体显示
        headfont.setBold(true);
        headcellstyle.setFont(headfont);
        Row row;
        // 新创建一行，行号为row+1
        row = sheet1.createRow(0);
        int columnSum = 35;
        for (int c = 0; c < columnSum; c++) {
            // 列头
            // 创建一个单元格，列号为col+1
            row.createCell(c);
            sheet1.setColumnWidth(c, 4500);
            sheet1.getRow(0).getCell(c).setCellStyle(headcellstyle);
        }
        // 第一行为标题
        sheet1.getRow(0).getCell(0).setCellValue("客户");
        sheet1.getRow(0).getCell(1).setCellValue("合同号");
        sheet1.getRow(0).getCell(2).setCellValue("订单号");
        // sheet1.getRow(0).getCell(3).setCellValue("铭牌号");
        sheet1.getRow(0).getCell(3).setCellValue("机器信息");
        sheet1.getRow(0).getCell(4).setCellValue("台数");
        sheet1.getRow(0).getCell(5).setCellValue("单价");
        sheet1.getRow(0).getCell(6).setCellValue("装置");
        sheet1.getRow(0).getCell(7).setCellValue("装置总价");
        sheet1.getRow(0).getCell(8).setCellValue("优惠金额");
        sheet1.getRow(0).getCell(9).setCellValue("订单总价");
        sheet1.getRow(0).getCell(10).setCellValue("币种");
        sheet1.getRow(0).getCell(11).setCellValue("销售员");
        // 销售费
        sheet1.getRow(0).getCell(12).setCellValue("业务费");
        sheet1.getRow(0).getCell(13).setCellValue("保修费");
        sheet1.getRow(0).getCell(14).setCellValue("保修人员");
        sheet1.getRow(0).getCell(15).setCellValue("付款方式");
        sheet1.getRow(0).getCell(16).setCellValue("定金率");
        sheet1.getRow(0).getCell(17).setCellValue("毛利");
        sheet1.getRow(0).getCell(18).setCellValue("包装方式");
        sheet1.getRow(0).getCell(19).setCellValue("机架长度");
        sheet1.getRow(0).getCell(20).setCellValue("针数");
        sheet1.getRow(0).getCell(21).setCellValue("头数");
        sheet1.getRow(0).getCell(22).setCellValue("头距");
        sheet1.getRow(0).getCell(23).setCellValue("X行程");
        sheet1.getRow(0).getCell(24).setCellValue("Y行程");
        sheet1.getRow(0).getCell(25).setCellValue("电脑");
        sheet1.getRow(0).getCell(26).setCellValue("剪线方式");
        sheet1.getRow(0).getCell(27).setCellValue("换色方式");
        sheet1.getRow(0).getCell(28).setCellValue("加油系统");
        sheet1.getRow(0).getCell(29).setCellValue("夹线器");
        sheet1.getRow(0).getCell(30).setCellValue("跳跃方式");
        sheet1.getRow(0).getCell(31).setCellValue("旋梭");
        sheet1.getRow(0).getCell(32).setCellValue("面线夹持");
        sheet1.getRow(0).getCell(33).setCellValue("订单类型");
        sheet1.getRow(0).getCell(34).setCellValue("填表日期");
        DataFormat dataFormat = wb.createDataFormat();
        CellStyle cellStyle;
        HSSFCellStyle wrapStyle = wb.createCellStyle();
        wrapStyle.setWrapText(true);
        // 第二行开始，填入值
        for (int i = 0; i < list.size(); i++) {
            int r = i + 1;
            MachineOrderDetail mod = list.get(i);
            OrderDetail od = mod.getOrderDetail();
            // 新创建一行
            row = sheet1.createRow(r);
            for (int c = 0; c < columnSum; c++) {
                // 创建列单元格
                row.createCell(c);
            }
            // 客户
            sheet1.getRow(r).getCell(0).setCellValue(mod.getCustomer());
            // 合同号
            sheet1.getRow(r).getCell(1).setCellValue(mod.getContractNum());
            // 
            sheet1.getRow(r).getCell(2).setCellValue(mod.getOrderNum());
            sheet1.getRow(r).getCell(0).setCellStyle(wrapStyle);
            sheet1.getRow(r).getCell(1).setCellStyle(wrapStyle);
            sheet1.getRow(r).getCell(2).setCellStyle(wrapStyle);
            // sheet1.getRow(r).getCell(3).setCellValue(mod.getNameplate());//
            MachineType mt = mod.getMachineType();
            if (mt != null) {
                // 机器信息
                String machineInfo = mt.getName() + "/" + mod.getNeedleNum() + "/" + mod.getHeadNum() + "/" + mod.getHeadDistance() + "/" + mod.getxDistance() + "/" + mod.getyDistance() + "/" + mod.getElectricTrim() + "/" + mod.getElectricPc();
                // 
                sheet1.getRow(r).getCell(3).setCellValue(machineInfo);
            }
            // 
            sheet1.getRow(r).getCell(4).setCellValue(mod.getMachineNum());
            // 
            sheet1.getRow(r).getCell(5).setCellValue(mod.getMachinePrice());
            cellStyle = wb.createCellStyle();
            // 金额格式
            cellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
            sheet1.getRow(r).getCell(5).setCellStyle(cellStyle);
            List<Equipment> epArray = JSON.parseArray(mod.getEquipment(), Equipment.class);
            String strEp = "";
            int epAmount = 0;
            for (Equipment itemEquipment : epArray) {
                strEp += itemEquipment.getName() + ":" + itemEquipment.getNumber() + "个" + "\r\n";
                epAmount += itemEquipment.getPrice() * itemEquipment.getNumber();
            }
            sheet1.getRow(r).getCell(6).setCellStyle(wrapStyle);
            // 装置
            sheet1.getRow(r).getCell(6).setCellValue(new HSSFRichTextString(strEp));
            // 装置金额
            sheet1.getRow(r).getCell(7).setCellValue(epAmount);
            sheet1.getRow(r).getCell(7).setCellStyle(cellStyle);
            // 优惠金额
            sheet1.getRow(r).getCell(8).setCellValue(mod.getDiscounts());
            Double totalAmount = Double.parseDouble(mod.getMachinePrice()) * mod.getMachineNum() + epAmount - Double.parseDouble(mod.getDiscounts());
            sheet1.getRow(r).getCell(8).setCellStyle(cellStyle);
            // 总金额
            sheet1.getRow(r).getCell(9).setCellValue(totalAmount);
            sheet1.getRow(r).getCell(9).setCellStyle(cellStyle);
            // 币种
            sheet1.getRow(r).getCell(10).setCellValue(mod.getCurrencyType());
            // 销售员
            sheet1.getRow(r).getCell(11).setCellValue(mod.getSellman());
            // 销售费
            sheet1.getRow(r).getCell(12).setCellValue(mod.getBusinessExpense());
            // 保修费
            sheet1.getRow(r).getCell(13).setCellValue(mod.getWarrantyFee());
            // 保修人员
            sheet1.getRow(r).getCell(14).setCellValue(mod.getMaintainPerson());
            // 付款方式
            sheet1.getRow(r).getCell(15).setCellValue(mod.getPayMethod());
            // sheet1.getRow(r).getCell(16).setCellValue(0);//定金率 先空着
            // 改为要从成本核算员的意见中抽取 毛利率
            // 毛利率
            sheet1.getRow(r).getCell(17).setCellValue(mod.getGrossProfit());
            // 包装方式
            sheet1.getRow(r).getCell(18).setCellValue(mod.getPackageMethod());
            // 订单中关于技术部的意见里长度：***，显示在财务报表中，显示方式为【机架长度】一栏
            // 机架长度
            sheet1.getRow(r).getCell(19).setCellValue(mod.getMachineFrameLength());
            // 针数
            sheet1.getRow(r).getCell(20).setCellValue(mod.getNeedleNum());
            // 头数
            sheet1.getRow(r).getCell(21).setCellValue(mod.getHeadNum());
            // 头距
            sheet1.getRow(r).getCell(22).setCellValue(mod.getHeadDistance());
            // X行程
            sheet1.getRow(r).getCell(23).setCellValue(mod.getxDistance());
            // Y行程
            sheet1.getRow(r).getCell(24).setCellValue(mod.getyDistance());
            // 电脑
            sheet1.getRow(r).getCell(25).setCellValue(od.getElectricPc());
            // 剪线方式
            sheet1.getRow(r).getCell(26).setCellValue(od.getElectricTrim());
            // 换色方式
            sheet1.getRow(r).getCell(27).setCellValue(od.getColorChangeMode());
            // 加油系统
            sheet1.getRow(r).getCell(28).setCellValue(od.getElectricOil());
            // 夹线器
            sheet1.getRow(r).getCell(29).setCellValue(od.getAxleSplit());
            // 跳跃方式
            sheet1.getRow(r).getCell(30).setCellValue(od.getAxleJump());
            // 旋梭
            sheet1.getRow(r).getCell(31).setCellValue(od.getAxleHook());
            // 面线夹持
            sheet1.getRow(r).getCell(32).setCellValue(od.getAxleUpperThread());
            // 订单类型
            sheet1.getRow(r).getCell(33).setCellValue(mod.getOrderType());
            dateString = formatter.format(mod.getCreateTime());
            // 填表日期
            sheet1.getRow(r).getCell(34).setCellValue(dateString);
        }
        downloadPath = reportOutputPath + "账务报表" + ".xls";
        downloadPathForNginx = "/report/" + "账务报表" + ".xls";
        out = new FileOutputStream(downloadPath);
        wb.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    if ("".equals(downloadPath)) {
        return ResultGenerator.genFailResult("异常导出失败!");
    } else {
        return ResultGenerator.genSuccessResult(downloadPathForNginx);
    }
}