@Override
public String getPriceDate(String fileName) {
try {
HSSFSheet sheet = getSheet(fileName, 0);
String marker = "Дата";
int numOfRows = sheet.getPhysicalNumberOfRows() > 10 ? 10 : sheet.getPhysicalNumberOfRows();
String dirtyDate = null;
for (int i = priceConfig.getRowToStart(Brand.STELS); i < numOfRows; i++) {
Row row = sheet.getRow(i);
if (row.getPhysicalNumberOfCells() > 1) {
Cell cell = row.getCell(0);
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
String stringCellValue = cell.getStringCellValue();
if (stringCellValue.contains(marker)) {
dirtyDate = stringCellValue;
break;
}
}
}
}
if (dirtyDate != null) {
return dirtyDate.trim();
}
} catch (Exception ex) {
LOGGER.error("FAIL to get price date - return today");
return new Date().toString();
}
return new Date().toString();
}
/**
* POI:解析Excel文件中的数据并把每行数据封装成一个实体
*
* @param fis 文件输入流
* @return List<EmployeeInfo> Excel中数据封装实体的集合
*/
public static List<String> importControlsByPoi(InputStream fis) {
// 这里是解析出来的Excel的数据存放的List集合
List<String> controls = new ArrayList<String>();
try {
// 创建Excel工作薄
HSSFWorkbook hwb = new HSSFWorkbook(fis);
// 得到第一个工作表
HSSFSheet sheet = hwb.getSheetAt(0);
HSSFRow row = null;
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
sheet = hwb.getSheetAt(i);
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
row = sheet.getRow(j);
// 此方法调用getCellValue(HSSFCell cell)对解析出来的数据进行判断，并做相应的处理
if (ImportExcelByPoi.getCellValue(row.getCell(0)) != null
&& !"".equals(ImportExcelByPoi.getCellValue(row.getCell(0)))) {
controls.add(String.valueOf(ImportExcelByPoi.getCellValue(row.getCell(0))));
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
return controls;
}
/**
* 读取 Excel文件内容
*
* @param excel_name
* @return
* @throws Exception
*/
public static List<List<Object>> readExcelByInputStream(InputStream inputstream)
throws Exception {
// 结果集
List<List<Object>> list = new ArrayList<List<Object>>();
HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream);
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
// //System.out.println("excel行数： "+hssfsheet.getPhysicalNumberOfRows());
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
HSSFRow hssfrow = hssfsheet.getRow(j);
if (hssfrow != null) {
int col = hssfrow.getPhysicalNumberOfCells();
// 单行数据
List<Object> arrayString = new ArrayList<Object>();
for (int i = 0; i < col; i++) {
HSSFCell cell = hssfrow.getCell(i);
if (cell == null) {
arrayString.add("");
} else if (cell.getCellType() == 0) {
arrayString.add(new Double(cell.getNumericCellValue()).toString());
} else { // 如果EXCEL表格中的数据类型为字符串型
arrayString.add(cell.getStringCellValue().trim());
}
}
list.add(arrayString);
}
}
return list;
}
/**
* 判断报表是否可以输出
*
* @param sheet -- 报表对象
* @return
*/
public static boolean isAllowOut(HSSFSheet sheet) {
boolean ret = true;
if (sheet.getPhysicalNumberOfRows() > 50000) {
_log.showWarn("报表输出行数超出了最大行数：50000！");
ret = false;
}
return ret;
}
public void mapChampsdeExcel() {
openFichierOrdreDB2();
msg(nomTableDb2 + " mapChampsdeExcel dans la table memoire"); // $NON-NLS-1$
if (wb != null) {
HSSFSheet sheet = wb.getSheet(nomTableDb2);
// HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
int rows; // No of rows
rows = sheet.getPhysicalNumberOfRows();
int cols = xlsNombreDeColonne(sheet, rows);
Excel2Plat = createArrayList(cols);
Plat2Excel = createArrayList(cols);
rowEnTete = -1;
DB2debutOrdreDB2();
for (int r = 0; r < rows; r++) {
row = sheet.getRow(r);
if (row != null) {
if (rowEnTete == -1 && (r == 0 || r == 1)) {
xlsRechercheEntete(row, cols, r);
} else {
if (rowEnTete >= 0) {
msgProg(
nomTableDb2
+ " ("
+ r
+ ") rows d fichier excel lu "); //$NON-NLS-1$ //$NON-NLS-2$
for (int c = 0; c < Excel2Plat.size(); c++) {
if (Excel2Plat.get(c) > -1) {
cell = row.getCell(c);
if (cell != null) {
xlsPopulateValeur(cell, c);
}
}
}
ecrireLigne2Plat();
DB2addOrdreDB2();
for (int i = 0; i < nbChamps; i++) {
champs.get(i).valeurduchamp = "";
}
}
}
}
}
DB2ecrireOrdreDB2();
}
DB2fermetureOrdreDB2();
}
public void executeJob(Connection con) throws Exception {
// input ����
String fileName = "./기초자료/기초자료.xls";
String sheetName = "GHOST_TABLES";
FileInputStream fis = null;
HSSFWorkbook wb = null;
HSSFSheet sheet = null;
// output ����
String deleteSQL = "delete from ghost_tables";
String insertSQL = "insert into ghost_tables values(?,?,?)";
PreparedStatement pstmt = null;
Statement stmt = null;
try {
// Input �ڷ� �غ�
fis = new FileInputStream(fileName);
wb = new HSSFWorkbook(fis);
sheet = wb.getSheet(sheetName);
// �����ڷ� All ����
stmt = con.createStatement();
stmt.execute(deleteSQL);
// Insert�� SQL Statement ����
pstmt = con.prepareStatement(insertSQL);
HSSFRow row = null;
Bean bean = null;
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
readCount++;
if (row == null) continue;
bean = getBean(row);
pstmt.setString(1, bean.tableName);
pstmt.setString(2, bean.tableDesc);
pstmt.setString(3, bean.shortName);
pstmt.executeUpdate();
writeCount++;
System.out.printf("[%d][%s][%s][%s]\n", i, bean.tableName, bean.tableDesc, bean.shortName);
}
} finally {
if (wb != null) wb.close();
if (fis != null) fis.close();
JDBCUtil.close(stmt);
JDBCUtil.close(pstmt);
}
}
static StringBuilder readSheet(HSSFSheet sheet) {
StringBuilder json_sheet = new StringBuilder(1024 * 100);
int row_length = sheet.getPhysicalNumberOfRows();
Iterator<Row> rows = sheet.rowIterator();
HSSFRow row;
if (rows.hasNext()) row = (HSSFRow) rows.next();
else return null;
int col_length = row.getPhysicalNumberOfCells();
Iterator<Cell> cells = row.cellIterator();
HSSFCell cell;
if (cells.hasNext()) cell = (HSSFCell) cells.next();
else return null;
int first_row = cell.getRowIndex();
json_sheet.append("{'categories':[");
a:
for (int x = first_row; x < row_length; x++) {
row = sheet.getRow(x);
if (x > first_row) json_sheet.append("{");
for (int y = 0; y < col_length; y++) {
cell = row.getCell(y);
String value = "空白";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
value = "" + cell.getNumericCellValue();
// Log.v("数字",value);
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
// Log.v("布尔",""+cell.getBooleanCellValue());
break a;
case HSSFCell.CELL_TYPE_STRING:
value = "'" + cell.getStringCellValue() + "'";
// Log.v("文本",value);
break;
}
if (y == 0) {
if (x == first_row) continue;
else json_sheet.append("'name':" + value + ",'type':'line','data':[");
} else json_sheet.append(value + ",");
}
json_sheet.replace(json_sheet.length() - 1, json_sheet.length(), "]");
if (x == first_row) json_sheet.append("}\n[");
else json_sheet.append("},");
}
json_sheet.replace(json_sheet.length() - 1, json_sheet.length(), "]");
// Log.v("JSON",json_sheet.toString());
return json_sheet;
}
/**
* 读取 Excel文件内容
*
* @param excel_name
* @return
* @throws Exception
*/
public static List<String[]> readExcel(String excel_name) throws Exception {
// 结果集
List<String[]> list = new ArrayList<String[]>();
HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(excel_name));
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
HSSFRow hssfrow = hssfsheet.getRow(j);
if (hssfrow != null) {
int col = hssfrow.getPhysicalNumberOfCells();
// 单行数据
String[] arrayString = new String[col];
for (int i = 0; i < col; i++) {
HSSFCell cell = hssfrow.getCell(i);
if (cell == null) {
arrayString[i] = "";
} else if (cell.getCellType() == 0) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date d = cell.getDateCellValue();
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
arrayString[i] = formater.format(d);
} else {
arrayString[i] = new BigDecimal(cell.getNumericCellValue()).longValue() + "";
}
}
} else { // 如果EXCEL表格中的数据类型为字符串型
arrayString[i] = cell.getStringCellValue().trim();
}
}
list.add(arrayString);
}
}
return list;
}
public String saveRoomImp(HSSFSheet sheet) throws Exception {
String result = "";
try {
int rowCount = sheet.getPhysicalNumberOfRows();
if (rowCount > 0) {
int savenum = 0;
for (int i = 1; i < rowCount; i++) {
HSSFRow row = sheet.getRow(i);
try {
String schoolArea = ""; // 校区
String build = ""; // 楼宇
String roomName = ""; // 教室
schoolArea = row.getCell((short) 0).getStringCellValue().trim(); // 校区
build = row.getCell((short) 1).getStringCellValue().trim(); // 楼宇
roomName = row.getCell((short) 2).getStringCellValue().trim(); // 教室
if (findOneRoom(schoolArea, build, roomName) != null) {
result = result + " 第" + i + "行教室已存在，未导入。<br>";
continue;
} else {
TArea area = areaService.findAreaByName(schoolArea);
TRoom room = new TRoom();
room.setSchoolArea(area);
room.setBuild(build);
room.setRoomName(roomName);
// room.setStatus(status);
roomDAO.save(room);
}
savenum++;
} catch (Exception e) {
result = result + " 第" + i + "行，数据导入失败。<br>";
}
}
result = result + " 成功导入" + savenum + "行。<br>";
} else {
result = result + " 没有找到有效的记录。<br>";
}
} catch (Exception e) {
result = result + "导入出现异常中断。<br>";
}
return result;
}
public String ifRoomCanImp(HSSFSheet sheet) throws Exception {
String result = "";
try {
int rowCount = sheet.getPhysicalNumberOfRows();
if (rowCount > 0) {
for (int i = 1; i < rowCount; i++) {
HSSFRow row = sheet.getRow(i);
try {
String schoolArea = ""; // 校区
String build = ""; // 楼宇
String roomName = ""; // 教室
schoolArea = row.getCell((short) 0).getStringCellValue().trim(); // 校区
build = row.getCell((short) 1).getStringCellValue().trim(); // 楼宇
roomName = row.getCell((short) 2).getStringCellValue().trim(); // 教室
TArea area = areaService.findAreaByName(schoolArea);
if (area == null) {
result = result + "第" + i + "行校区名称错误，请核对校区名称。<br>";
break;
}
} catch (NumberFormatException ne) {
result = result + "第" + i + "行数字数据非文本类型，修改文本类型后再导入。<br> ";
} catch (NullPointerException n) {
result = result + "第" + i + "行有空数据，请填充后再导入。<br>";
}
}
} else {
result = "没有找到有效的记录。<br>";
}
} catch (Exception e) {
result = result + "Excel文件检查异常，无法导入。<br>";
}
if (!"".equals(result)) {
result = result + "导入失败。";
}
return result;
}
public void batchUploadHolHoldays() throws IOException {
if (file != null && file.length() > 0) {
HSSFRow row;
// HSSFCell cell;
InputStream is = new FileInputStream(file);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
// 获得excel中的第一张表
HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
List<HolHoliday> saveList = new ArrayList<HolHoliday>();
String yearError = "";
String repeatError = "";
String dayError = "";
String loginNameError = "";
String deptError = "";
String operatorId = servletRequest.getParameter("userId");
for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
HolHoliday holHoliday = new HolHoliday();
row = sheet.getRow(i);
String year = getCellData(row, 0); // 年度
String loginName = getCellData(row, 1); // 工号
String dayCounts = getCellData(row, 2); // 天数
String remark = getCellData(row, 3); // 备注
String reg = "[0-9]{4}";
Pattern p = Pattern.compile(reg);
Matcher m = p.matcher(year);
boolean yearFlag = m.matches();
if (!yearFlag) {
yearError += (i + 1) + ",";
continue;
}
List<CsUser> userList = holHolidayService.findUsersByLoginName(loginName);
if (userList == null || userList.size() < 1) {
loginNameError += (i + 1) + ",";
continue;
}
CsUser csUser = userList.get(0);
List<HolHoliday> list =
holHolidayService.findByYearAndHolPersonId(year, csUser.getId() + "");
if (list != null && list.size() > 0) {
repeatError += (i + 1) + ",";
continue;
}
// holHoliday.setHolName(csUser.getName());
// holHoliday.setHolLoginName(csUser.getLoginName());
// holHoliday.setDeptName(csUser.getDept());
holHoliday.setHolId(csUser.getId() + "");
// holHoliday.setHolId(loginName);
if (!StringUtils.isNumeric(dayCounts)) {
dayCounts += (i + 1) + ",";
continue;
}
holHoliday.setHolYear(year);
holHoliday.setHolDays(Long.valueOf(dayCounts));
holHoliday.setRemark(remark);
HolHoliday last =
this.holHolidayService.findLastHolidaysSetByholPersonId(loginName); // 同工号，上一次的数据
if (last != null) {
holHoliday.setHolDaysLeft(last.getHolDaysLeft() + holHoliday.getHolDays());
holHoliday.setHolDaysWait(last.getHolDaysWait());
} else {
holHoliday.setHolDaysLeft(holHoliday.getHolDays());
holHoliday.setHolDaysWait(0l);
}
holHoliday.setRemoved(0l);
if (StringUtils.isNotEmpty(operatorId)) {
holHoliday.setOperator(Long.valueOf(operatorId));
}
holHoliday.setOperateTime(sdf.format(new Date()));
saveList.add(holHoliday);
}
servletResponse.setCharacterEncoding("utf-8"); // 务必，防止返回文件名是乱码
servletResponse.setContentType("ajax");
String msg = "";
if (StringUtils.isEmpty(yearError)
&& StringUtils.isEmpty(dayError)
&& StringUtils.isEmpty(repeatError)
&& StringUtils.isEmpty(loginNameError)) {
try {
holHolidayService.saveAll(saveList);
msg = "上传成功!共有" + saveList.size() + "条数据入库！";
servletResponse.getWriter().write("{\"message\":\"success\",\"info\":\"" + msg + "\"}");
} catch (Exception e) {
e.printStackTrace();
}
} else {
String error1 = "", error2 = "", error3 = "", error4 = "";
if (StringUtils.isNotEmpty(yearError)) {
error1 = "第" + yearError.substring(0, yearError.length() - 1) + "行，年份格式错误！";
}
if (StringUtils.isNotEmpty(loginNameError)) {
error4 = "第" + loginNameError.substring(0, loginNameError.length() - 1) + "行，该用户不存在！";
}
if (StringUtils.isNotEmpty(repeatError)) {
error2 =
"第" + repeatError.substring(0, repeatError.length() - 1) + "行，数据重复！该用户在该年份下已设置过公休！";
}
if (StringUtils.isNotEmpty(dayError)) {
error3 = "第" + dayError.substring(0, dayError.length() - 1) + "行，公休天数格式错误！";
}
servletResponse
.getWriter()
.write(
"{\"message\":\"error\",\"error1\":\""
+ error1
+ "\",\"error2\":\""
+ error2
+ "\",\"error3\":\""
+ error3
+ "\",\"error4\":\""
+ error4
+ "\"}");
}
}
}
public List<T> importExcel(File file) {
List<T> dist = new ArrayList<T>();
// 文件输入流
if (file != null) {
try {
// 得到目标目标类的所有的字段列表
Field[] fields = clazz.getDeclaredFields();
// 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中
Map<String, Method> fieldMap = new HashMap<String, Method>();
// 循环读取所有字段
for (Field field : fields) {
// 得到单个字段上的Annotation
ExcelAnnotation excelAnnotation = field.getAnnotation(ExcelAnnotation.class);
// 如果标识了Annotationd
if (excelAnnotation != null) {
String fieldName = field.getName();
// 构造设置了Annotation的字段的Setter方法
String setMethodName =
"set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
// 构造调用的method
Method setMethod = clazz.getMethod(setMethodName, new Class[] {field.getType()});
// 将这个method以Annotaion的名字为key来存入
fieldMap.put(excelAnnotation.exportName(), setMethod);
}
}
FileInputStream fileInputStream = new FileInputStream(file);
// POIFSFileSystem这是POIFS类库的主要类，它管理文件系统的整个生命周期。
POIFSFileSystem fs = new POIFSFileSystem(fileInputStream);
HSSFWorkbook wb = new HSSFWorkbook(fs);
// 将标题的文字内容放入到一个map中
Map<Integer, String> titleMap = new HashMap<Integer, String>();
// for (int k = 0; k < wb.getNumberOfSheets(); k++) {
// 获取第一个Sheet
HSSFSheet sheet = wb.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++) {
HSSFRow row = sheet.getRow(r);
if (row != null) {
int cells = 27;
// 判断整行单元格是否全为空，true为全为空
if (!isAllCellNULL(cells, row)) {
// 得到传入类的实例
T tObject = clazz.newInstance();
// 序号
Method method = clazz.getMethod("setSerialNumber", new Class[] {String.class});
method.invoke(tObject, ((row.getRowNum() + 1) + ""));
for (int c = 0; c < cells; c++) {
HSSFCell cell = row.getCell(c);
if (cell != null) {
// 获取列名
if (r == 0) {
titleMap.put(cell.getColumnIndex(), cell.getStringCellValue());
} else {
// 这里得到此列的对应的标题
String titleString = titleMap.get(c);
if (titleString != null) {
titleString = titleString.trim();
}
// 如果这一列的标题和类中的某一列的Annotation相同，那么则调用此类的的set方法，进行设值
if (fieldMap.containsKey(titleString)) {
Method setMethod = fieldMap.get(titleString);
// 得到setter方法的参数
Type[] types = setMethod.getGenericParameterTypes();
// 只要一个参数
String xclass = String.valueOf(types[0]);
// 判断参数类型
if ("class java.lang.String".equals(xclass)) {
// 设置所有的cell为文本形式
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
setMethod.invoke(
tObject,
String.valueOf(cell.getNumericCellValue())
.substring(
0,
String.valueOf(cell.getNumericCellValue()).lastIndexOf(".")));
} else {
setMethod.invoke(
tObject,
cell.getStringCellValue().equals("")
? ""
: cell.getStringCellValue());
}
} else if ("class java.util.Date".equals(xclass)) {
if (titleString.equals("入学时间")
|| titleString.equals("推送日期")
|| titleString.equals("日期")) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
setMethod.invoke(tObject, cell.getDateCellValue());
} else {
if (cell.getStringCellValue() != null
&& !"".equals(cell.getStringCellValue())) {
try {
if (cell.getStringCellValue().trim().length() == 10) {
setMethod.invoke(
tObject,
DateUtil.StringToDate(
cell.getStringCellValue(), "yyyy-MM-dd"));
} else if (cell.getStringCellValue().trim().length() == 8) {
setMethod.invoke(
tObject,
DateUtil.StringToDate(cell.getStringCellValue(), "yyyyMMdd"));
} else {
}
} catch (Exception e) {
System.out.println(cell.getStringCellValue() + "日期错误");
setMethod.invoke(tObject, null);
}
}
}
}
} else if ("class java.lang.Boolean".equals(xclass)) {
Boolean boolName = true;
if ("否".equals(cell.getStringCellValue())) {
boolName = false;
}
setMethod.invoke(tObject, boolName);
} else if ("class java.lang.Integer".equals(xclass) || "int".equals(xclass)) {
// 设置所有的cell为文本形式
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (titleString.equals("民族")) {
setMethod.invoke(
tObject,
ConstantsPeopleMap.getCode(cell.getStringCellValue().trim()));
}
if (titleString.equals("入学方式")) {
setMethod.invoke(
tObject,
ConstantsEntranceWayMap.getCode(cell.getStringCellValue().trim()));
}
if (titleString.equals("政治面貌")) {
setMethod.invoke(
tObject,
ConstantsPoliticalMap.getCode(cell.getStringCellValue().trim()));
}
if (titleString.equals("证件类型")) {
setMethod.invoke(
tObject, ConstantsIDMap.getCode(cell.getStringCellValue().trim()));
}
if (titleString.equals("学习中心")) {
setMethod.invoke(tObject, cell.getStringCellValue().trim());
}
if (titleString.equals("报读院校")) {
setMethod.invoke(tObject, cell.getStringCellValue().trim());
}
if (titleString.equals("招生批次")) {
setMethod.invoke(tObject, cell.getStringCellValue().trim());
}
if (titleString.equals("学籍批次")) {
setMethod.invoke(tObject, cell.getStringCellValue().trim());
}
if (titleString.equals("层次")) {
setMethod.invoke(tObject, cell.getStringCellValue().trim());
}
if (titleString.equals("专业")) {
setMethod.invoke(tObject, cell.getStringCellValue().trim());
}
if (titleString.equals("是否免试生")) {
if ("否".equals(cell.getStringCellValue())) {
setMethod.invoke(tObject, 0);
}
if ("是".equals(cell.getStringCellValue())) {
setMethod.invoke(tObject, 1);
}
}
if (titleString.equals("性别")) {
if ("男".equals(cell.getStringCellValue())) {
setMethod.invoke(tObject, Constants.SEX_MALE);
}
if ("女".equals(cell.getStringCellValue())) {
setMethod.invoke(tObject, Constants.SEX_FAMALE);
}
}
} else if ("class java.lang.Long".equals(xclass)) {
setMethod.invoke(tObject, new Long(cell.getStringCellValue()));
} else {
//
}
}
}
}
}
if (r != 0) {
dist.add(tObject);
}
}
}
}
// }
} catch (Exception e) {
e.printStackTrace();
}
}
return dist;
}
/**
* 复制来源表格中第1个SHEET中的图片到目标表格中的第1个SHEET中。
*
* @param destBook -- 目标表格
* @param srcBook -- 来源表格
*/
private static void copySheetImage(HSSFWorkbook destBook, HSSFWorkbook srcBook) {
// 来源表单
HSSFSheet srcSheet = srcBook.getSheetAt(0);
// 目标表单
HSSFSheet destSheet = destBook.getSheetAt(0);
// 需要偏移的行数
int endRowNum = destSheet.getPhysicalNumberOfRows();
// 取来源表单中的图片对象
List<HSSFPicture> lsSrcPicture = getAllPicture(srcSheet);
_log.showDebug("----------source picture size:" + lsSrcPicture.size());
// 取所有子图形数据，如果是主从报表且明细数据占多页时，则会报空指针错误
List<HSSFPictureData> lsPicData = null;
try {
lsPicData = srcBook.getAllPictures();
} catch (Exception e) {
// e.printStackTrace();
_log.showWarn("由于表单明细有多页，造成临时表的图片数据取不到，只能采用原表第1个图替代！");
// 原表中也没有图片，则不处理图片复制了
lsPicData = destBook.getAllPictures();
if (lsPicData == null || lsPicData.isEmpty()) return;
// 只取原表中第1个图片信息
List<HSSFPictureData> destData = FactoryUtil.newList();
for (int i = 0, n = lsSrcPicture.size(); i < n; i++) {
destData.add(lsPicData.get(0));
}
lsPicData = destData;
}
if (lsPicData == null || lsPicData.isEmpty()) return;
_log.showDebug("----------source data size:" + lsPicData.size());
// data数量可能大于图片数量
if (lsSrcPicture.size() > lsPicData.size()) {
_log.showWarn("图片数量与数据数量不符！");
return;
}
// 取图片管理器
HSSFPatriarch destDraw = destSheet.getDrawingPatriarch();
if (destDraw == null) {
destDraw = destSheet.createDrawingPatriarch();
}
// 取原目标表单中的图片对象
List<HSSFPicture> lsDestPicture = getAllPicture(destSheet);
int index = lsDestPicture.size();
for (int i = 0, n = lsSrcPicture.size(); i < n; i++) {
// 取图片对象
HSSFPicture picture = lsSrcPicture.get(i);
// 根据图片序号取图片数据
HSSFPictureData picdata = lsPicData.get(i);
// 取图片字节信息
byte[] datas = picdata.getData();
// 取图片位置信息
HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor();
// 添加行偏移值
anchor.setRow1(anchor.getRow1() + endRowNum);
anchor.setRow2(anchor.getRow2() + endRowNum);
// 插入新图片，返回的新图片序号无效
destBook.addPicture(datas, picdata.getFormat());
// 上面代码中新建图片的序号没有考虑原有图片数量，所以取原图片数量+1作为新图片的序号
index++;
_log.showDebug("---------copy new image index=" + index);
destDraw.createPicture(anchor, index);
}
}
/**
* 添加临时表格内容
*
* @param mainSheet -- 原表单内容
* @param subSheet -- 临时表单内容
* @return
*/
public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) {
if (mainSheet == null || subSheet == null) return null;
// 判断报表是否允许输出
if (!isAllowOut(mainSheet)) return mainSheet;
// 原报表的最后一行
int endRowNum = mainSheet.getPhysicalNumberOfRows();
HSSFRow sourow = null, descrow = null;
HSSFCell sourcell = null, descell = null, orgcell = null;
int i = 0, offsetcnt = 0;
// 复制表格中的图片
copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook());
// 设置以合并的单元格
CellRangeAddress range = null;
int mergedNum = subSheet.getNumMergedRegions();
for (i = 0; i < mergedNum; i++) {
range = subSheet.getMergedRegion(i);
range.setFirstRow(range.getFirstRow() + endRowNum);
range.setLastRow(range.getLastRow() + endRowNum);
mainSheet.addMergedRegion(range);
}
range = null;
// int k = 0;
// 设置相关参数
mainSheet.setAlternativeExpression(subSheet.getAlternateExpression());
mainSheet.setAlternativeFormula(subSheet.getAlternateFormula());
mainSheet.setAutobreaks(subSheet.getAutobreaks());
mainSheet.setDialog(subSheet.getDialog());
mainSheet.setDisplayGuts(subSheet.getDisplayGuts());
mainSheet.setFitToPage(subSheet.getFitToPage());
for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext(); ) {
sourow = (HSSFRow) iterow.next();
offsetcnt = sourow.getRowNum() + endRowNum;
descrow = mainSheet.createRow(offsetcnt);
descrow.setHeight(sourow.getHeight());
descrow.setHeightInPoints(sourow.getHeightInPoints());
java.util.Iterator<Cell> iter = sourow.cellIterator();
while (iter.hasNext()) {
sourcell = (HSSFCell) iter.next();
int column = sourcell.getColumnIndex();
descell = descrow.createCell(column);
// 取模板中的单元格，与来源表单位置相同
int row = sourcell.getRowIndex();
orgcell = mainSheet.getRow(row).getCell(column);
if (orgcell != null) {
// 取模板中的类型赋值
descell.setCellType(orgcell.getCellType());
// 取模板中的样式赋值
descell.setCellStyle(orgcell.getCellStyle());
} else {
_log.showWarn("module xls [{0}, {1}] cell is null!", row, column);
}
if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING)
descell.setCellValue(sourcell.getStringCellValue());
else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
descell.setCellValue(sourcell.getNumericCellValue());
else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_BLANK) ;
}
sourow = null;
sourcell = null;
descrow = null;
orgcell = null;
}
return mainSheet;
}
/**
* 读取97-2003格式
*
* @param filePath 文件路径
* @throws java.io.IOException
*/
@SuppressWarnings("rawtypes")
public static List<Map> readExcel2003(String filePath) throws IOException {
// 返回结果集
List<Map> valueList = new ArrayList<Map>();
FileInputStream fis = null;
try {
fis = new FileInputStream(filePath);
HSSFWorkbook wookbook = new HSSFWorkbook(fis); // 创建对Excel工作簿文件的引用
HSSFSheet sheet = wookbook.getSheetAt(0); // 在Excel文档中，第一张工作表的缺省索引是0
int rows = sheet.getPhysicalNumberOfRows(); // 获取到Excel文件中的所有行数
Map<Integer, String> keys = new HashMap<Integer, String>();
int cells = 0;
// 遍历行（第1行 表头） 准备Map里的key
HSSFRow firstRow = sheet.getRow(0);
if (firstRow != null) {
// 获取到Excel文件中的所有的列
cells = firstRow.getPhysicalNumberOfCells();
// 遍历列
for (int j = 0; j < cells; j++) {
// 获取到列的值
try {
HSSFCell cell = firstRow.getCell(j);
String cellValue = getCellValue(cell);
keys.put(j, cellValue);
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 遍历行（从第二行开始）
for (int i = 1; i < rows; i++) {
// 读取左上端单元格(从第二行开始)
HSSFRow row = sheet.getRow(i);
// 行不为空
if (row != null) {
// 准备当前行 所储存值的map
Map<String, Object> val = new HashMap<String, Object>();
boolean isValidRow = false;
// 遍历列
for (int j = 0; j < cells; j++) {
// 获取到列的值
try {
HSSFCell cell = row.getCell(j);
String cellValue = getCellValue(cell);
val.put(keys.get(j), cellValue);
if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) {
isValidRow = true;
}
} catch (Exception e) {
e.printStackTrace();
}
}
// 第I行所有的列数据读取完毕，放入valuelist
if (isValidRow) {
valueList.add(val);
}
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
fis.close();
}
return valueList;
}
/**
* 从一行开始，到全部数据，列的范围指定
*
* @param sheet
* @param startRowIndex
* @param startCellIndex
* @param endCellIndex
* @return
*/
public static List<String[]> getCellsStringValue(
HSSFSheet sheet, int startRowIndex, int startCellIndex, int endCellIndex) {
// 最后一行，就是全部行数-1
int endRowIndex = sheet.getPhysicalNumberOfRows() - 1;
return getCellsStringValue(sheet, startRowIndex, startCellIndex, endRowIndex, endCellIndex);
}
@Test
public void testSheet() {
try {
truckService = ctx.getBean(TruckService.class);
annualDataService = ctx.getBean(AnnualDataFleetFuelService.class);
personService = ctx.getBean(PersonService.class);
StringTokenizer stringTokenizer;
SimpleDateFormat simpleFormat = (SimpleDateFormat) DateFormat.getDateInstance();
simpleFormat.applyPattern("dd-MMM-yyyy");
// Open the Excel File
URL url = this.getClass().getResource("/setupfleet/Fleet_Data_Annual.xls");
FileInputStream fileInputStream = new FileInputStream(url.getFile());
HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); // Open Spreadsheet Work book
// Process each Sheet at a time. API FAILS to move to next sheets
for (int j = 16; j <= 16; j++) {
Date datee = null;
String truckId = null;
String driverPersonId = null;
BigDecimal amount;
Integer closingMileage;
// Get the first Sheet(0)
HSSFSheet worksheet = workbook.getSheetAt(j); // First Sheet is 0 , second is 1 and so on
vehicleNumber = worksheet.getRow(4).getCell(0).toString().trim();
employeeNumber = worksheet.getRow(4).getCell(4).toString().trim();
System.out.println(
"\n\n"
+ "Begin processing Sheet: "
+ j
+ " i.e. "
+ vehicleNumber
+ ", Driver: "
+ employeeNumber);
// Get the Truck based of the VEHICLEnUMBER
truck = truckService.findByVehicleNumber(vehicleNumber);
// Set this as Start Milleage for Truck
if (truck != null) {
truckId = truck.getId();
}
// Get personId
driver = personService.findDriverWithEmployeeNumber(employeeNumber);
if (driver != null) {
driverPersonId = driver.getId();
}
if (truck != null && driver != null) {
// iterated through all the rows
for (int i = 4;
i <= worksheet.getPhysicalNumberOfRows();
i++) { // if Row 6, index is (5)
try {
datee =
simpleFormat.parse(
worksheet.getRow(i).getCell(1).toString().trim()); // String to Date
} catch (ParseException ex) {
Logger.getLogger(Setup_DailyInputSheetOne.class.getName())
.log(Level.SEVERE, null, ex);
List<AnnualDataFleetFuel> annualDataFleetFuelList = annualDataService.findAll();
for (AnnualDataFleetFuel annualDataFuel : annualDataFleetFuelList) {
System.out.println(
"READING DATABASE - TruckId: "
+ annualDataFuel.getTruckId()
+ ". Date: "
+ annualDataFuel.getTransactionDate()
+ ". Amount: "
+ annualDataFuel.getMonthlyFuelCost()
+ ". Closing Mileage: "
+ annualDataFuel.getClosingMileage()
+ ". Driver Person ID: "
+ annualDataFuel.getDriverPersonId());
}
}
//
amount = new BigDecimal(worksheet.getRow(i).getCell(2).toString().trim());
if (amount.compareTo(new BigDecimal("0.0")) == 0) {
amount = new BigDecimal("0.00");
}
//
String mileageToken = worksheet.getRow(i).getCell(3).toString().trim();
stringTokenizer = new StringTokenizer(mileageToken, ".");
closingMileage = Integer.parseInt(stringTokenizer.nextElement().toString());
//
System.out.println(
"TruckId: "
+ truckId
+ ". Date: "
+ datee
+ ". Amount: "
+ amount.setScale(2, BigDecimal.ROUND_HALF_UP)
+ ". Closing Mileage: "
+ closingMileage
+ ". Driver Person ID: "
+ driverPersonId);
// Build and Persist the AnnualDataFleetFuel Object
AnnualDataFleetFuel annualData =
createAnnualDataEntity(
datee,
amount.setScale(2, BigDecimal.ROUND_HALF_UP),
closingMileage,
truckId,
driverPersonId);
annualDataService.persist(annualData);
// // Build and Update the Truck Object
// addTruckAnnualData(annualData);
}
} else {
if (truck != null) {
System.out.println("Truck NOT FOUND");
}
if (driver != null) {
System.out.println("DRIVER NOT FOUND");
}
}
}
fileInputStream.close();
} catch (FileNotFoundException e) {
System.out.println("\n\n" + "File Not Found in function setupDailyInputs()");
System.out.println("" + e);
} catch (IOException e) {
System.out.println("\n\n" + "Problem Reading File: /setupfleet/Fleet_Data_Annual.xls");
System.out.println("" + e);
}
}
