看代码,很简单
public static void main(String[] args) throws IOException {
export();
}
public static void export() throws IOException {
InputStream is = new FileInputStream(
"C:\\Users\\Administrator\\Desktop\\aa.xls");
HSSFWorkbook wb = new HSSFWorkbook(is);
HSSFCellStyle LockCellStyle = wb.createCellStyle();
LockCellStyle.setLocked(true);//锁定单元格的样式
HSSFCellStyle noLockCellStyle = wb.createCellStyle();
noLockCellStyle.setLocked(false);//不锁定单元格的样式
//设置日期单元格格式
/*CellStyle dateStyle = wb.createCellStyle();
CreationHelper creationHelper = wb.getCreationHelper();
dateStyle.setDataFormat(
creationHelper.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss")
); */
HSSFSheet sheetAt = wb.getSheetAt(0);//第一个工作表
sheetAt.protectSheet("123456"); //设置表单保护密码
Object[][] array={{1,"我是String",new Date()},{4,5,6,},{7,8,9},{10,11,12}};
for (int i = 0; i < array.length; i++) {
int rowstart=i+1;//0是表头站的行数,不被数据替换+1+1;
HSSFRow row = sheetAt.getRow(rowstart);//获得一行
if(row==null){
row= sheetAt.createRow(rowstart);//row为空 就创建一个
}
for (int j = 0; j < array[i].length; j++) {
boolean lock=false;//是否锁定单元格
lock=true;//锁定单元格
HSSFCell cell = row.getCell(j);//获得一个单元格
if(cell==null){
cell = row.createCell(j);//cell为空 就创建一个
}
Object v=array[i][j];
System.out.println(cell.getCellType());
if(v instanceof Integer){
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue((Integer)v);
}else if(v instanceof Double){
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue((Double)v);
}else if(v instanceof String){
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String)v);
}else if(v instanceof Date){
cell.setCellType(Cell.CELL_TYPE_STRING);
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String format = simpleDateFormat.format((Date)v);
cell.setCellValue((String)format);
}
if(lock){
cell.setCellStyle(LockCellStyle);
}else{
cell.setCellStyle(noLockCellStyle);
}
String cellValue = getCellValue(cell);
System.out.println(cellValue);//调试时输出
}
}
wb.write(new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\aa1.xls")));
}
/**
* 获取表格单元格Cell内容
* @param cell
* @return
*/
private static String getCellValue(Cell cell) {
String result = new String();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:// 数字类型
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result = format.format(value);
}
break;
case Cell.CELL_TYPE_STRING:// String类型
result = cell.getRichStringCellValue().toString();
break;
case Cell.CELL_TYPE_BLANK:
result = "";
break;
default:
result = "";
break;
}
return result;
}
模板的样子
生成数据后的,修改后可以同模板批量生成excel