Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createCellStyle

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle.

Prototype

@Override
public HSSFCellStyle createCellStyle()

Source Link

Document

Create a new Cell style and add it to the workbook's style table.

Usage

From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java

License:Open Source License

public static void createCellAlignLeft(HSSFRow row, short column, String value, HSSFWorkbook wb) {

    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFCell cell = row.getCell(column);
    if (cell == null)
        cell = row.createCell(column);/*  www.j  av  a2s  .c om*/
    //cell.setEncoding(wb.ENCODING_UTF_16);
    cell.setCellValue(value);
    cell.setCellStyle(style);
}

From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java

License:Open Source License

public static void createCellBold(HSSFRow row, short column, String value, HSSFWorkbook wb) {

    HSSFCellStyle style = wb.createCellStyle();
    HSSFFont font = wb.createFont();/* w  w  w  . j a v a  2 s  .  co  m*/
    font.setColor((short) 0xc);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFCell cell = row.getCell(column);
    if (cell == null)
        cell = row.createCell(column);
    //cell.setEncoding(wb.ENCODING_UTF_16);
    cell.setCellValue(value);
    cell.setCellStyle(style);
}

From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java

License:Open Source License

public static void createCell(HSSFRow row, short column, int value, HSSFWorkbook wb) {
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFCell cell = row.getCell(column);
    if (cell == null)
        cell = row.createCell(column);//from  w  w  w.  j  a  v  a 2s . c  o  m
    cell.setCellValue(value);
    cell.setCellStyle(style);
}

From source file:com.work.fileHandle.fileHandleServlet.java

public HSSFWorkbook createExcel() {

    HSSFWorkbook webbook = new HSSFWorkbook();

    HSSFSheet sheet = webbook.createSheet("?");

    HSSFRow row = sheet.createRow((int) 0);

    HSSFCellStyle style = webbook.createCellStyle();

    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue("?");
    cell.setCellStyle(style);/*from  w w  w . j  a v a 2s  .  c  om*/
    cell = row.createCell((short) 1);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell((short) 2);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell((short) 3);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell((short) 4);
    cell.setCellValue("?");
    cell.setCellStyle(style);

    List students = query();

    for (int i = 0; i < students.size(); i++) {
        row = sheet.createRow((int) i + 1);
        Student student = (Student) students.get(i);

        row.createCell((short) 0).setCellValue(student.getStudentId().toString());
        row.createCell((short) 1).setCellValue(student.getStudentCode());
        row.createCell((short) 2).setCellValue(student.getEnName());
        row.createCell((short) 3).setCellValue(student.getCnName());
        row.createCell((short) 4).setCellValue(student.getGrade());
    }

    try {
        String savePath = this.getServletContext().getRealPath("/WEB-INF/uploads");
        String filename = "students-grade.xls";
        File file = new File(savePath);

        if (!file.exists() && !file.isDirectory()) {
            System.out.println(savePath + "??");
            file.mkdir();
        }

        FileOutputStream fout = new FileOutputStream(savePath + "/" + filename);
        webbook.write(fout);
        fout.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

    return webbook;
}

From source file:com.xnradmin.core.test.TestJson.java

private static void processYsmcExcel(String res) throws JSONException, org.json.JSONException {
    JSONObject jo = new JSONObject(res);

    Iterator it = jo.keys();/*  w  ww.  j av a  2 s .c  o  m*/

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("ysmc");
    HSSFRow row = sheet.createRow((int) 0);
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?
    HSSFCell cell = row.createCell(0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(1);

    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(2);

    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(3);

    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(4);

    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(5);

    cell.setCellValue("?2");
    cell.setCellStyle(style);
    cell = row.createCell(6);

    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(7);

    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(8);

    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(9);

    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(10);

    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(11);

    int flag = 0;

    while (it.hasNext()) {
        String k = it.next().toString();
        // System.out.println(jo.get(k));
        flag = processYsmcSubLevel1(k, jo.get(k).toString(), wb, sheet, row, flag);
    }

    FileOutputStream fout;
    try {
        fout = new FileOutputStream("/Users/liubin/temp/ysmc" + StringHelper.getSystime("yyyyMMdd") + ".xls");
        wb.write(fout);
        fout.close();
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:com.xnradmin.core.test.TestJson.java

private static int processYsmcSubLevel2(String type1, String type2, String content, HSSFWorkbook wb,
        HSSFSheet sheet, HSSFRow row, int flag) throws JSONException, org.json.JSONException {
    JSONArray data = new JSONArray(content);

    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?

    for (int i = 0; i < data.length(); i++) {

        JSONObject temp = (JSONObject) data.get(i);

        System.out.println(temp);

        StringBuffer sb = new StringBuffer();
        sb.append("??").append(temp.get("name"));
        sb.append(" | ").append(temp.get("level"));
        sb.append(" | ??").append(temp.get("format"));
        sb.append(" | ??").append(temp.get("unit"));
        sb.append(" | ?:").append(temp.get("own_brand"));
        sb.append(" | ?2: ").append(temp.get("sell_brand"));
        sb.append(" | ").append(temp.get("class1"));
        sb.append(" | ").append(type1);
        sb.append(" | ?").append(type2);
        sb.append(" | ?").append(temp.get("price"));
        sb.append(" | ").append(temp.get("commodity_total_price"));
        System.out.println(sb.toString());

        int f = flag + 1;
        row = sheet.createRow(f);/*from   w ww  .  ja v a  2 s  .c o  m*/
        System.out.println("row: " + f);

        HSSFCell cell0 = row.createCell(0);
        cell0.setCellStyle(style);
        cell0.setCellValue(temp.get("name").toString());

        HSSFCell cell1 = row.createCell(1);
        cell1.setCellStyle(style);
        cell1.setCellValue(temp.get("level").toString());

        HSSFCell cell2 = row.createCell(2);
        cell2.setCellStyle(style);
        cell2.setCellValue(temp.get("format").toString());

        HSSFCell cell3 = row.createCell(3);
        cell3.setCellStyle(style);
        cell3.setCellValue(temp.get("unit").toString());

        HSSFCell cell4 = row.createCell(4);
        cell4.setCellStyle(style);
        cell4.setCellValue(temp.get("own_brand").toString());

        HSSFCell cell5 = row.createCell(5);
        cell5.setCellStyle(style);
        cell5.setCellValue(temp.get("sell_brand").toString());

        HSSFCell cell6 = row.createCell(6);
        cell6.setCellStyle(style);
        cell6.setCellValue(temp.get("class1").toString());

        HSSFCell cell7 = row.createCell(7);
        cell7.setCellStyle(style);
        cell7.setCellValue(temp.get("price").toString());

        HSSFCell cell8 = row.createCell(8);
        cell8.setCellStyle(style);
        cell8.setCellValue(temp.get("commodity_total_price").toString());

        HSSFCell cell9 = row.createCell(9);
        cell9.setCellStyle(style);
        cell9.setCellValue(type1);

        HSSFCell cell10 = row.createCell(10);
        cell10.setCellStyle(style);
        cell10.setCellValue(type2);

        // row.createCell(0).setCellValue(temp.get("name").toString());
        // row.createCell(1).setCellValue(temp.get("format").toString());
        // row.createCell(2).setCellValue(temp.get("unit").toString());
        // row.createCell(3).setCellValue(temp.get("own_brand").toString());
        // row.createCell(4).setCellValue(temp.get("sell_brand").toString());
        // row.createCell(5).setCellValue(temp.get("class1").toString());
        // row.createCell(6).setCellValue(temp.get("price").toString());

        flag = f;
    }
    return flag;
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

public static void writeToXLS(ExcelRow heads, ExcelData data, String sheetName, ByteArrayOutputStream out)
        throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet(sheetName);
    HSSFRow row = sheet1.createRow(0);/*w ww  .j  ava 2  s  .co  m*/
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFCell cell;

    for (int i = 0; i < heads.size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(heads.get(i));
        cell.setCellStyle(style);
    }
    if (data != null && data.size() != 0) {
        for (int n = 0; n < data.size(); n++) {
            row = sheet1.createRow(n + 1);
            ExcelRow datarow = data.get(n);
            for (int m = 0; m < datarow.size(); m++) {
                cell = row.createCell(m);
                cell.setCellValue(datarow.get(m));
                cell.setCellStyle(style);
            }
        }
    }
    wb.write(out);
}

From source file:com.zhu.action.CarAction.java

public void exportpeople(ActionMapping mapping, ActionForm form, HttpServletRequest request,
        HttpServletResponse response) {/*from  w  ww.j  av  a 2s . c o m*/
    CarForm carForm = (CarForm) form;

    // webbookExcel
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet
    HSSFSheet sheet = wb.createSheet("?");
    // sheet0,??poiExcel?short
    HSSFRow row = sheet.createRow((int) 0);
    // ? 
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?
    HSSFCell cell = row.createCell(0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(1);
    cell.setCellValue("???");
    cell.setCellStyle(style);
    // ? ??
    OrderService orderService = new OrderService();
    List<Orderinfo> list = orderService.getOrderCarDetail(carForm.getId());
    System.out.println(list.size());
    for (int i = 0; i < list.size(); i++) {
        row = sheet.createRow(i + 1);
        Orderinfo order = (Orderinfo) list.get(i);
        // ?
        row.createCell(0).setCellValue(order.getN1());
        row.createCell(1).setCellValue(order.getI1());
        row = sheet.createRow(i + list.size() + 1);
        row.createCell(0).setCellValue(order.getN2());
        row.createCell(1).setCellValue(order.getI2());
        row = sheet.createRow(i + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN3());
        row.createCell(1).setCellValue(order.getI3());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN4());
        row.createCell(1).setCellValue(order.getI4());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN5());
        row.createCell(1).setCellValue(order.getI5());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN6());
        row.createCell(1).setCellValue(order.getI6());
        row = sheet.createRow(
                i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN7());
        row.createCell(1).setCellValue(order.getI7());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size()
                + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN8());
        row.createCell(1).setCellValue(order.getI8());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size()
                + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN9());
        row.createCell(1).setCellValue(order.getI9());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size()
                + list.size() + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN10());
        row.createCell(1).setCellValue(order.getI10());
    }
    // ?
    String filePath = "";
    Date dt = new Date();
    DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
    String date = df.format(dt).toString();
    filePath = "/Users/Nemo/Documents/carpeople" + date + ".xls";
    File file = new File(filePath);

    try {
        // FileOutputStream fout = new FileOutputStream(
        // "/Users/Nemo/Documents/carpeople.xls");
        // wb.write(fout);
        // fout.close();
        OutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
    int key = 0;
    int MaxRowNum = 0, MaxCellNum = 0;
    try {
        FileInputStream in = new FileInputStream(filePath);
        POIFSFileSystem fs = new POIFSFileSystem(in);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        FileOutputStream out = new FileOutputStream("/Users/Nemo/Documents/carpeople" + date + ".xls");
        int number = workbook.getNumberOfSheets();
        for (int i = 0; i < number; i++) { // ?sheet
            sheet = workbook.getSheetAt(i); // 14
            System.out.println("" + sheet.getSheetName() + " ? "
                    + (sheet.getLastRowNum() + 1));
            MaxRowNum = 0;
            for (int k = 0; k <= sheet.getLastRowNum(); k++) {
                HSSFRow hRow = sheet.getRow(k);
                // System.out.println((k + 1) + "");
                if (isBlankRow(hRow)) // 
                {
                    int m = 0;
                    for (m = k + 1; m <= sheet.getLastRowNum(); m++) {
                        HSSFRow nhRow = sheet.getRow(m);
                        if (!isBlankRow(nhRow)) {
                            // System.out.println("?" + (m + 1));
                            sheet.shiftRows(m, sheet.getLastRowNum(), k - m);
                            break;
                        }
                    }
                    if (m > sheet.getLastRowNum())
                        break; // ?
                } else { // ?
                    MaxRowNum++;
                    if (MaxCellNum < hRow.getLastCellNum())
                        MaxCellNum = hRow.getLastCellNum();
                }
            }
            workbook.setPrintArea(i, 0, MaxCellNum, 0, MaxRowNum);
            System.out
                    .println("?? " + sheet.getSheetName() + "  " + MaxRowNum);
        }
        workbook.write(out);
        in.close();
        out.close();
    } catch (IOException e) {
        System.out.println(key + " " + e.getMessage() + " ");
        e.printStackTrace();

    }

    System.out.println("??");

}

From source file:com.zhu.action.CarAction.java

public void export(ActionMapping mapping, ActionForm form, HttpServletRequest request,
        HttpServletResponse response) {/*from w ww .  j  av a  2  s. c o  m*/
    // webbookExcel
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet
    HSSFSheet sheet = wb.createSheet("?");
    // sheet0,??poiExcel?short
    HSSFRow row = sheet.createRow((int) 0);
    // ? 
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?
    HSSFCell cell = row.createCell(0);
    cell.setCellValue("id");
    cell.setCellStyle(style);
    cell = row.createCell(1);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(2);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(3);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(4);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(5);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(6);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(7);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(8);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    // ? ??
    CarService carService = new CarService();
    List<Car> list = carService.getCar();

    for (int i = 0; i < list.size(); i++) {
        row = sheet.createRow((int) i + 1);
        Car car = (Car) list.get(i);
        // ?
        row.createCell(0).setCellValue(car.getId());
        row.createCell(1).setCellValue(car.getCarid());
        row.createCell(2).setCellValue(car.getLicense());
        row.createCell(3).setCellValue(car.getNumber());
        row.createCell(4).setCellValue(car.getPeople());
        row.createCell(5).setCellValue(car.getRoute());
        row.createCell(6).setCellValue(car.getStartdate());
        row.createCell(7).setCellValue(car.getEnddate());
        if (car.getStatus() == 0) {
            row.createCell(8).setCellValue("");
        } else if (car.getStatus() == 1) {
            row.createCell(8).setCellValue("");
        } else {
            row.createCell(8).setCellValue("");
        }
        // cell = row.createCell((short) 3);
    }
    // ?
    String filePath = "";
    Date dt = new Date();
    DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
    String date = df.format(dt).toString();
    filePath = "/Users/Nemo/Documents/car" + date + ".xls";
    File file = new File(filePath);
    try {
        FileOutputStream fout = new FileOutputStream(file);
        wb.write(fout);
        fout.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.zrx.authority.util.ObjectExcelView.java

@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    // TODO Auto-generated method stub
    Date date = new Date();
    String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
    HSSFSheet sheet;/*from w  ww . j a  va2  s  . c  o  m*/
    HSSFCell cell;
    response.setContentType("application/octet-stream");
    response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
    sheet = workbook.createSheet("sheet1");

    List<String> titles = (List<String>) model.get("titles");
    int len = titles.size();
    HSSFCellStyle headerStyle = workbook.createCellStyle(); //?
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    HSSFFont headerFont = workbook.createFont(); //
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerFont.setFontHeightInPoints((short) 11);
    headerStyle.setFont(headerFont);
    short width = 20, height = 25 * 20;
    sheet.setDefaultColumnWidth(width);
    for (int i = 0; i < len; i++) { //
        String title = titles.get(i);
        cell = getCell(sheet, 0, i);
        cell.setCellStyle(headerStyle);
        setText(cell, title);
    }
    sheet.getRow(0).setHeight(height);

    HSSFCellStyle contentStyle = workbook.createCellStyle(); //?
    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    List<PageData> varList = (List<PageData>) model.get("varList");
    int varCount = varList.size();
    for (int i = 0; i < varCount; i++) {
        PageData vpd = varList.get(i);
        for (int j = 0; j < len; j++) {
            String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : "";
            cell = getCell(sheet, i + 1, j);
            cell.setCellStyle(contentStyle);
            setText(cell, varstr);
        }

    }

}