POI操作Excel表

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();
    }
}