import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFCell;
import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.sql.Timestamp;
import java.text.DecimalFormat;
/**
*
* Created by IntelliJ IDEA. User: admin Date: 2011-10 -10 Time: 16:10:29 To
*
* change this template use File | Settings | File Templates.
*/
public class UpdateExcel2003 {
/**
*
* 只是一个demo,这里假设修改的值是String类型
*
*
*
* @param exlFile
*
* @param sheetIndex
*
* @param col
*
* @param row
*
* @param value
*
* @throws Exception
*/
public static void updateExcel(File exlFile, int sheetIndex, int col,
int row, int outRowStart) throws Exception {
ArrayList<String> timeList = new ArrayList<String>();
FileInputStream fis = new FileInputStream(exlFile);
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(sheetIndex); // 第五个sheet页
// HSSFRow r=sheet.getRow(row);//第
// HSSFCell cell=r.getCell(col);
// int type=cell.getCellType();
// String str1=cell.getStringCellValue();
// System.out.println(sheet.getLastRowNum());//我期望是上班,下班,上班,下班
for (int i = 13; i <= sheet.getLastRowNum(); i++) {// 从13行循环到最后一行
HSSFRow rowi = sheet.getRow((short) i);// 行
if (i == 18 || i == 19 || i == 25 || i == 26 || i == 32 || i == 33
|| i == 39 || i == 40) {
} else {
if (null == rowi) {
// continue;
} else {
HSSFCell celli = rowi.getCell(col); // 上班时间
if (null == celli) {
} else {
String str = getCellValue(celli);
timeList.add(str);
}
HSSFCell celliRight = rowi.getCell(col + 2);// 下班时间
if (null == celliRight) {
} else {
String strRight = getCellValue(celliRight);
timeList.add(strRight);
}
}
}
}
fis.close(); // 关闭文件输入流
for (String str : timeList) {
System.out.print(str);
System.out.print("|");
}
System.out.println();
System.out.println(timeList.size());
File outfile = new File("C:\\Users\\LYF\\Desktop\\我去.xls");
FileInputStream fos = new FileInputStream(outfile);
HSSFWorkbook outworkbook = new HSSFWorkbook(fos);
HSSFSheet outsheet = outworkbook.getSheetAt(0);
HSSFRow outrow = outsheet.getRow((short) outRowStart);// 行★这里决定输出的行数更改谁
if (null == outrow) {
// continue;
} else {
for (int i = 4; i <= 45; i++) {// 从13行循环到最后一行
HSSFCell outcell = outrow.getCell(i); // 上班时间
/*
*
* if (null == outcell) { } else { String str =
*
* getCellValue( outcell); System.out.println(str); }
*/
outcell.setCellValue(timeList.get(i - 4));
}
}
FileOutputStream fos2 = new FileOutputStream(outfile);
outworkbook.write(fos2);
fos.close(); // 关闭文件输出流
// System.out.println("---------------------------------over");
}
private static String getCellValue(HSSFCell cell) {
String cellValue = "";
DecimalFormat df = new DecimalFormat("#");
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
cellValue = cell.getRichStringCellValue().getString().trim();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
cellValue = df.format(cell.getNumericCellValue()).toString();
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
break;
case XSSFCell.CELL_TYPE_FORMULA:
cellValue = cell.getCellFormula();
break;
default:
cellValue = "";
}
return cellValue;
}
/**
*
* @param args
*/
public static void main(String[] args) throws Exception {
// 下面改成你自己的 xls文件进行测试,2003格式的,不能2007
File file = new File("C:\\Users\\LYF\\Desktop\\标准报表2.xls");
// 下面尝试更改第一行第一列的单元格的值
// UpdateExcel2003.updateExcel(file, 13, 16, 14, 5);// 1//16// 31,33//
// 行★这里决定数据源
// UpdateExcel2003.updateExcel(file, 13, 31, 14, 9);//
// 页数,第几段, 第几行(第19页)
UpdateExcel2003.updateExcel(file, 12, 31, 14, 6);//
UpdateExcel2003.updateExcel(file, 14, 1, 14, 3);//
UpdateExcel2003.updateExcel(file, 14, 16, 14, 10);//
UpdateExcel2003.updateExcel(file, 6, 16, 14, 7);//
UpdateExcel2003.updateExcel(file, 15, 1, 14, 8);//
}
}