POI操作Excel表

来源：互联网发布：权力的48条法则知乎编辑：程序博客网时间：2024/05/15 05:13

import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.InputStream;import java.math.BigDecimal;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;    import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;    public class TitleTest {/** * 返回第一个不是数字的值 * @param args *///  public static void main(String[] args) {//      String path = "D://excel/2013年月度卡新版式（2月上）.xls";//      String sheetName = "工业全市1";//      String number = "-8.1";//      List<String> list = seat(path, sheetName, number);////      for (int i = 0; i < list.size(); i++) {//          System.out.println("list" + i + "=" + list.get(i));//      }////  }    public static void main(String[] args) {        String path = "D://excel/2013年月度卡新版式（10月中）.xls";        String sheetName = "全省";        try {            InputStream is = new FileInputStream(path);            POIFSFileSystem fs = new POIFSFileSystem(is);            HSSFWorkbook workbook = new HSSFWorkbook(fs); // 获整个Excel            HSSFSheet sheet = workbook.getSheet(sheetName);// 获所有的sheet            int rowNum = sheet.getLastRowNum();            HSSFRow row = sheet.getRow(0);            int colNum = row.getPhysicalNumberOfCells();            for (int i = 0; i <rowNum; i++) {                row = sheet.getRow(i);                boolean b=isBlankRow(row, rowNum, colNum);                if(b==false){                    System.out.println(i+"行不为空");                }else{                    System.out.println(i+"行为空******");                }            }        } catch (Exception e) {            e.printStackTrace();        }       }            /**     * 判断行是否为空     * @param row     * @return     */    public static boolean isBlankRow(HSSFRow row, int rowNum, int colNum) {        boolean b = true;        if (row == null) {            b = true;        } else {            for (int i = 0; i < colNum; i++) {                HSSFCell cell = row.getCell(i);                if (cell == null) {                    continue;                } else {                    String value = getCellValue(cell).toString();                    if (value.length()!=0) {                        b = false;                    }                }            }        }        return b;    }                    /**     * 返回数字所在的行列 a[0]=行 ；a[1]=列     * @param type  ：类型（月度？长三角?省内11地市？）     * @param IndexName ：表名     * @param sheetName ：sheet名     * @param number     * @return     */    public static List seat(String path, String sheetName, String number) {        List<String> list = new ArrayList<String>();        try {            InputStream is = new FileInputStream(path);            POIFSFileSystem fs = new POIFSFileSystem(is);            HSSFWorkbook workbook = new HSSFWorkbook(fs); // 获整个Excel            HSSFSheet sheet = workbook.getSheet(sheetName);// 获所有的sheet            int rowNum = sheet.getLastRowNum();            HSSFRow row = sheet.getRow(0);            int colNum = row.getPhysicalNumberOfCells();            System.out.println("总行数为：" + rowNum + "   总列数为：" + colNum);            for (int i = 0; i <= rowNum; i++) {                row = sheet.getRow(i);                for (int j = 0; j < colNum; j++) {                    HSSFCell cell = row.getCell(j);                    String context = getCellValue(cell).toString();                    if (context.equals(number)) {                        list.add(Integer.toString(i));                        list.add(Integer.toString(j));                        System.out.println("行为：" + i + " 列为：" + j);                        continue;                    }                }            }            int list0 = Integer.parseInt(list.get(0));            int list1 = Integer.parseInt(list.get(1));            HSSFRow row1 = sheet.getRow(list0);            HSSFCell cell = row1.getCell(0);            System.out.println("第一列值为：" + getCellValue(cell).toString());                for (int i = list0; i >= 0; i--) {                HSSFRow row2 = sheet.getRow(i);                HSSFCell cell1 = row2.getCell(list1);                String context = getCellValue(cell1).toString();                if ((!context.equals("")) && (isNum(context) == false)) {                    System.out.println("第" + i + "行值为：" + context);                    list.add(context);                    continue;                }            }            is.close();        } catch (Exception e) {            System.out.println("    seat方法异常" + e);        }        return list;    }                                                            /**     * 判断字符串是否为数字     * @param s     * @return     */    public static boolean isNum(String s) {        boolean value = false;        try {            Double.parseDouble(s);            value = true;        } catch (Exception e) {            value = false;        }        return value;    }        /**     * POI取得Excel单元格的值     * @param cell     * @return     * @throws IOException     */    private static Object getCellValue(HSSFCell cell) {        Object value = "";        if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {            value = cell.getRichStringCellValue().toString();        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {            if (HSSFDateUtil.isCellDateFormatted(cell)) {                Date date = (Date) cell.getDateCellValue();                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");                value = sdf.format(date);            } else {                double value_temp = (double) cell.getNumericCellValue();                BigDecimal bd = new BigDecimal(value_temp);                BigDecimal bd1 = bd.setScale(3, bd.ROUND_HALF_UP);                value = bd1.doubleValue();                DecimalFormat format = new DecimalFormat("#0.###");                value = format.format(cell.getNumericCellValue());            }        }        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {            value = "";        }        if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {            try {                value = String.valueOf(cell.getStringCellValue());            } catch (IllegalStateException e) {                value = String.valueOf(cell.getNumericCellValue());            }        }        return value;    }}

POI创建合并单元格

public class test {              /**     * 创建合并单元格     * @throws Exception     */    public  static void main(String[] args) throws Exception {         Workbook wb = new HSSFWorkbook();        Sheet sheet = wb.createSheet("new sheet");        Row row = sheet.createRow((short) 1);        Cell cell = row.createCell((short) 1);        cell.setCellValue("This is a test of merging");        sheet.addMergedRegion(new CellRangeAddress(                1, //first row (0-based)                1, //last row  (0-based)                1, //first column (0-based)                2  //last column  (0-based)        ));              // Write the output to a file        FileOutputStream fileOut = new FileOutputStream("D://excel/workbook.xls");        wb.write(fileOut);        fileOut.close();    }}

0 0