private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
throws Exception {
Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
nCell.setCellStyle(tCell.getCellStyle());
tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
nCell.setCellValue(
tCell
.getStringCellValue()
.replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
.replaceAll(
PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
.replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
nCell.setCellStyle(tCell.getCellStyle());
Row tempRow = tempSheet.getRow(4);
Row newRow = newSheet.createRow(4);
for (int c = 0; c < tempRow.getLastCellNum(); c++) {
tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
nCell = newRow.createCell(c, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue());
nCell.setCellStyle(tCell.getCellStyle());
}
for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
newSheet.addMergedRegion(mergedRegion);
}
}
public static void copySheet(Sheet sheet, Sheet newSheet) {
int maxCol = 0;
for (int row = 0; row <= sheet.getLastRowNum(); row++) {
Row oldRow = sheet.getRow(row);
if (oldRow == null) continue;
Row newRow = newSheet.getRow(row);
if (newRow == null) newRow = newSheet.createRow(row);
if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
maxCol = (maxCol >= oldRow.getLastCellNum() - 1 ? maxCol : oldRow.getLastCellNum() - 1);
for (int col = 0; col < oldRow.getLastCellNum(); col++) {
Cell oldCell = oldRow.getCell(col);
if (oldCell == null) continue;
Cell newCell = newRow.getCell(col);
if (newCell == null) newCell = newRow.createCell(col);
copyCell(oldCell, newCell, true);
}
}
for (int col = 0; col <= maxCol; col++) {
if (sheet.getColumnWidth(col) >= 0) newSheet.setColumnWidth(col, sheet.getColumnWidth(col));
}
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress cra = sheet.getMergedRegion(i);
newSheet.addMergedRegion(cra);
}
}
public void parse() throws FileNotFoundException, IOException, InvalidFormatException {
InputStream inp;
inp = new FileInputStream(fileName);
Workbook wb = WorkbookFactory.create(inp);
Row row;
Sheet sheet = wb.getSheet(sheetName);
int startingRow = 0;
boolean breakNow = false;
for (int i = startingRow; i <= sheet.getLastRowNum(); i++) {
if (breakNow) {
break;
}
row = sheet.getRow(i);
if (row == null) {
break;
}
for (int j = 0; j < row.getLastCellNum(); j++) {
if (row.getCell(j).getStringCellValue().isEmpty()
|| row.getCell(j).getStringCellValue() == null) {
breakNow = true;
break;
}
// category.setCategoryName(row.getCell(j).getStringCellValue());
}
}
inp.close();
}
@Override
public List<String> getHeaderColumns() {
if (headerColumns == null) {
headerColumns = new ArrayList<>();
Row row = dataSheet.getRow(0);
int colCount = row != null ? row.getLastCellNum() : 0;
if (row == null || colCount == 0) {
LogMgr.logError(
"ExcelReader.getHeaderColumns()",
"Cannot retrieve column names because no data is available in the first row of the sheet: "
+ dataSheet.getSheetName(),
null);
String msg = ResourceMgr.getFormattedString("ErrExportNoCols", dataSheet.getSheetName());
messages.append(msg);
messages.appendNewLine();
return headerColumns;
}
for (int i = 0; i < colCount; i++) {
Cell cell = row.getCell(i);
Object value = getCellValue(cell);
if (value != null) {
headerColumns.add(value.toString());
} else {
headerColumns.add("Col" + Integer.toString(i));
}
}
}
return headerColumns;
}
private void parseRow(final Row row, final List<String> csvLines) {
int lastCellNum = row.getLastCellNum();
for (int i = zeroPos; i <= lastCellNum; i++) {
Cell cell = row.getCell(i);
if (cell != null) csvLines.add(parse(cell));
}
csvMap.put(row, csvLines);
}
// traversal cell
public void traversalCell(String filePath) {
try {
Workbook workBook = null;
try {
workBook = new XSSFWorkbook(filePath); // 支持2007
} catch (Exception ex) {
workBook = new HSSFWorkbook(new FileInputStream(filePath)); // 支持2003及以前
}
// 获得Excel中工作表个数
System.out.println("工作表个数 :" + workBook.getNumberOfSheets());
// 循环每个工作表
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
// 创建工作表
Sheet sheet = workBook.getSheetAt(i);
int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
System.out.println(
"工作表" + sheet.getSheetName() + " 行数 :" + sheet.getPhysicalNumberOfRows());
if (rows > 0) {
sheet.getMargin(Sheet.TopMargin);
for (int r = 0; r < rows; r++) { // 行循环
Row row = sheet.getRow(r);
if (row != null) {
int cells = row.getLastCellNum(); // 获得列数
for (short c = 0; c < cells; c++) { // 列循环
Cell cell = row.getCell(c);
if (cell != null) {
String value = getCellData(cell);
System.out.println("第" + r + "行 " + "第" + c + "列：" + value);
}
}
}
}
}
// 查询合并的单元格
for (i = 0; i < sheet.getNumMergedRegions(); i++) {
System.out.println("第" + i + "个合并单元格");
CellRangeAddress region = sheet.getMergedRegion(i);
int row = region.getLastRow() - region.getFirstRow() + 1;
int col = region.getLastColumn() - region.getFirstColumn() + 1;
System.out.println("起始行:" + region.getFirstRow());
System.out.println("起始列:" + region.getFirstColumn());
System.out.println("所占行:" + row);
System.out.println("所占列:" + col);
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
protected List<String> loadFileFormat(InputStream input) throws IOException {
Workbook book = new HSSFWorkbook(input);
Row headerRow = book.getSheetAt(0).getRow(headerRowNums - 1);
List<String> list = new ArrayList<String>();
for (int i = 0; i < headerRow.getLastCellNum(); i++) {
list.add(headerRow.getCell(i).getStringCellValue());
}
return list;
}
private void processRow(Row row) {
int fc = row.getFirstCellNum();
if (fc == 0) {
Cell cell = row.getCell(0);
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
columnCount = columnCount < row.getLastCellNum() ? row.getLastCellNum() : columnCount;
String rowType = cell.getStringCellValue().toUpperCase();
switch (rowType) {
case "TITLE":
{
title.add(row);
}
break;
case "GROUPH":
{
String fName = getGroupParam(row);
if (!fName.isEmpty()) {
detail.addGroup(true, row, fName);
}
}
break;
case "DETAIL1":
{
detail.add(row);
}
break;
case "GROUPF":
{
String fName = getGroupParam(row);
if (!fName.isEmpty()) {
detail.addGroup(false, row, fName);
}
}
break;
case "SUMMARY":
{
summary.add(row);
}
break;
}
}
}
}
public int getExcelLastCellNum() {
int count = 0;
if (null != wb.getSheetAt(0)) {
Sheet aSheet = wb.getSheetAt(0);
Row aRow = aSheet.getRow(0);
count = aRow.getLastCellNum();
}
return count;
}
private void makeCellAutosizeAndBold(Workbook wb, Row row) {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
for (int i = 0; i < row.getLastCellNum(); i++) {
row.getCell(i).setCellStyle(style);
}
makeCellsAutosize(wb, row);
}
// ����Excel
public String ExcelInto() throws Exception {
String directory = "/file";
String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
File target = UploadFile.Upload(uploadFile, uploadFileFileName, targetDirectory);
List<Dise> sList = new ArrayList<Dise>();
excelFile = new FileInputStream(target);
Workbook wb = new HSSFWorkbook(excelFile);
Sheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum() + 1;
for (int i = 1; i < rowNum; i++) {
Dise dise = new Dise();
Row row = sheet.getRow(i);
int cellNum = row.getLastCellNum();
for (int j = 0; j < cellNum; j++) {
Cell cell = row.getCell(j);
String cellValue = null;
switch (cell.getCellType()) { // �ж�excel��Ԫ�����ݵĸ�ʽ�����������ת�����Ա������ݿ�
case 0:
cellValue = String.valueOf((int) cell.getNumericCellValue());
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = String.valueOf((int) cell.getNumericCellValue());
break;
case 3:
cellValue = cell.getStringCellValue();
break;
case 4:
cellValue = cell.getStringCellValue();
break;
}
switch (j) { // ͨ���������ж϶�Ӧ������ֶ�
case 1:
dise.setName(cellValue);
break;
case 2:
dise.setA(cellValue);
break;
case 3:
dise.setB(cellValue);
break;
case 4:
dise.setC(cellValue);
break;
}
}
sList.add(dise);
}
DiseService.add(sList);
return "listAll";
}
public Integer getColumnCount()
throws FileNotFoundException, IOException, InvalidFormatException {
InputStream inp;
inp = new FileInputStream(fileName);
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheet(sheetName);
Row row = sheet.getRow(0);
int numColumns = row.getLastCellNum();
inp.close();
return numColumns;
}
private void processInnland(Sheet sheet) throws InvalidFormatException {
if (sheet.getPhysicalNumberOfRows() > 0) {
int lastRowNum = 0;
Row row = null;
lastRowNum = sheet.getLastRowNum();
System.out.println("Innland har " + lastRowNum + " rader");
row = sheet.getRow(0);
int lastCellNum = row.getLastCellNum();
System.out.println("Innland rad 0 har " + lastCellNum + " celler");
System.out.println("Kjører tilregnelighetssjekk");
String KO = text(row, INNLAND_KONTONR);
String LE = text(row, INNLAND_LEVNR);
String NA = text(row, INNLAND_NAVN);
String A1 = text(row, INNLAND_ADDR1);
String A2 = text(row, INNLAND_ADDR2);
String NR = text(row, INNLAND_POSTNUMMER);
String ST = text(row, INNLAND_POSTSTED);
if (KO.equals("Kontonr")
&& LE.equals("Lev.nr")
&& NA.equals("Navn")
&& A1.equals("Adresse 1")
&& A2.equals("Adresse 2")
&& NR.equals("Postnr.")
&& ST.equals("Poststed")) {
System.out.println("Første rad ser OK ut, fortsetter");
for (int j = 1; j <= lastRowNum; j++) {
System.out.println("Prosesserer rad " + j + " av " + lastRowNum);
row = sheet.getRow(j);
this.rowToXML(row, j);
}
} else {
throw new InvalidFormatException(
"Kjenner ikke igjen første rad\n"
+ "Skulle vært (1), fant (2)\n(1) 'Kontonr' 'Lev.nr' 'Navn' 'Adresse 1' 'Adresse 2' 'Postnr.' 'Poststed'\n'"
+ ""
+ KO
+ "' '"
+ LE
+ "' '"
+ NA
+ "' '"
+ A1
+ "' '"
+ A2
+ "' '"
+ NR
+ "' '"
+ ST
+ "'");
}
}
}
private void addSumBottom() {
for (int i = 0; i < book.getNumberOfSheets(); i++) {
Sheet sheet = book.getSheetAt(i);
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
row.setHeight((short) (ROW_HEIGHT + 100));
for (int j = 0; j < 1000000; j++) {
if (StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(0).getCell(j)))
&& StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(2).getCell(j)))) {
break;
}
Cell cell = row.createCell(j);
cell.setCellStyle(Style.get(book).SUM);
if (j == 0) {
cell.setCellValue("合计");
} else {
cell.setCellValue(0);
}
if (j >= 7) {
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula(
String.format(
"SUM(%s%s:%s%s)",
CellUtils.convertToABC(j + 1),
5,
CellUtils.convertToABC(j + 1),
sheet.getLastRowNum()));
}
}
sheet.addMergedRegion(
new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 6));
}
for (int i = 0; i < book.getNumberOfSheets(); i++) {
Sheet sheet = book.getSheetAt(i);
for (int j = 4; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
for (int k = 0; k <= row.getLastCellNum(); k++) {
Cell cell = row.getCell(k);
if (cell == null) {
continue;
}
if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(k)))) {
cell.setCellStyle(Style.get(book).SUM);
}
}
}
}
}
/** 从Excel导入到TableBean */
public void doImport() {
int rowNum = sheet.getLastRowNum() + 1;
int columnNum = 0;
for (int i = 0; i < rowNum; i++) {
if (sheet.getRow(i) != null) {
int last = sheet.getRow(i).getLastCellNum();
columnNum = last > columnNum ? last : columnNum;
}
}
tableBean = new TableBean(rowNum, columnNum);
Collection<CellBean> cellBeans = new ArrayList<CellBean>();
for (int r = startRow; r < rowNum; r++) {
Row row = sheet.getRow(r);
if (row != null) {
for (int c = 0; c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
Integer type = forceCellType.get(c);
if (type != null) {
cell.setCellType(type);
}
String cellValue = null;
if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
cellValue = cell.getBooleanCellValue() + "";
} else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
try {
cellValue = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
cellValue = String.valueOf(cell.getRichStringCellValue()).trim();
}
} else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (DateUtil.isCellDateFormatted(cell)) {
Date date2 = cell.getDateCellValue();
SimpleDateFormat dff = new SimpleDateFormat(dateFormat);
cellValue = dff.format(date2); // 日期转化
} else cellValue = String.valueOf(cell.getNumericCellValue());
} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
cellValue = cell.getStringCellValue();
}
if (cellValue != null && cellValue instanceof String) {
cellValue = cellValue.toString().trim();
}
CellBean cellBean = new CellBean(cellValue, r, c);
cellBean.setCell(cell);
cellBeans.add(cellBean);
}
}
}
}
tableBean.setCellBeans(cellBeans);
}
@Override
public int getColumnCount() {
if (config != null) {
return config.getColumnLast() - config.getColumnOffset() + 1;
} else {
Row row = sheet.getRow(sheet.getFirstRowNum());
if (row == null) {
return 0;
} else {
return row.getLastCellNum();
}
}
}
/**
* 读取Excel中的数据.将这些数据放入到一个三维数组中.
*
* @param sheetNumber 读取工作表的下标(从1开始).可有可无,默认读取所有表单.
*/
public static final ArrayList<ArrayList<Object>> readExcel(InputStream is, int... sheetNumber)
throws Exception {
ArrayList<ArrayList<Object>> subdata = new ArrayList<ArrayList<Object>>();
Workbook wb = null;
try {
wb = WorkbookFactory.create(is);
if (sheetNumber == null || sheetNumber.length < 1) {
int sheetCount = wb.getNumberOfSheets(); // 得到所有Excel中页的列表.
sheetNumber = new int[sheetCount];
for (int i = 0; i < sheetNumber.length; i++) {
sheetNumber[i] = i + 1;
}
}
Sheet sheet = null;
ArrayList<ArrayList<String>> alList = null;
ArrayList<String> tablenames = null;
ArrayList<Object> tableAndContents = null;
for (int a = 0; a < sheetNumber.length; a++) {
alList = new ArrayList<ArrayList<String>>();
tablenames = new ArrayList<String>();
tableAndContents = new ArrayList<Object>();
String tablename = wb.getSheetName(sheetNumber[a] - 1).trim();
int b = 0;
sheet = wb.getSheetAt(sheetNumber[a] - 1);
int rsRows = sheet.getRow(0) == null ? -1 : sheet.getLastRowNum();
log.warn("Sheet " + sheetNumber[a] + "." + tablename + ":" + (rsRows + 1));
for (int i = 1; i <= rsRows; i++) {
ArrayList<String> al = new ArrayList<String>();
Row row = sheet.getRow(i);
int cellCount = row.getLastCellNum();
for (int j = 0; j < cellCount; j++) {
// 通用的获取cell值的方式,返回字符串
String strc00 = getCellValue(row.getCell(j));
// 获得cell具体类型值的方式得到内容.
al.add(j, strc00);
}
alList.add(b, al);
b++;
}
tablenames.add(tablename);
tableAndContents.add(0, tablenames);
tableAndContents.add(1, alList);
subdata.add(a, tableAndContents);
}
} catch (Exception e) {
log.error("Read excel error.", e);
throw e;
}
return subdata;
}
/**
* Create a new row within the sheet and return the high level representation
*
* @param rownum row number
* @return high level Row object representing a row in the sheet
* @throws IllegalArgumentException If the max. number of rows is exceeded or a rownum is provided
* where the row is already flushed to disk.
* @see #removeRow(Row)
*/
public Row createRow(int rownum) {
int maxrow = SpreadsheetVersion.EXCEL2007.getLastRowIndex();
if (rownum < 0 || rownum > maxrow) {
throw new IllegalArgumentException(
"Invalid row number (" + rownum + ") outside allowable range (0.." + maxrow + ")");
}
// attempt to overwrite a row that is already flushed to disk
if (rownum <= _writer.getLastFlushedRow()) {
throw new IllegalArgumentException(
"Attempting to write a row["
+ rownum
+ "] "
+ "in the range [0,"
+ _writer.getLastFlushedRow()
+ "] that is already written to disk.");
}
// attempt to overwrite a existing row in the input template
if (_sh.getPhysicalNumberOfRows() > 0 && rownum <= _sh.getLastRowNum()) {
throw new IllegalArgumentException(
"Attempting to write a row["
+ rownum
+ "] "
+ "in the range [0,"
+ _sh.getLastRowNum()
+ "] that is already written to disk.");
}
// Make the initial allocation as big as the row above.
Row previousRow = rownum > 0 ? getRow(rownum - 1) : null;
int initialAllocationSize = 0;
// have previous row in memory -> take that value.
if (previousRow != null) initialAllocationSize = previousRow.getLastCellNum();
// are we called after a flush(0)? If yes, ask the writer for the value.
if (initialAllocationSize <= 0 && _writer.getNumberOfFlushedRows() > 0)
initialAllocationSize = _writer.getNumberOfCellsOfLastFlushedRow();
// default to 10 on the first row.
if (initialAllocationSize <= 0) initialAllocationSize = 10;
SXSSFRow newRow = new SXSSFRow(this, initialAllocationSize);
_rows.put(new Integer(rownum), newRow);
if (_randomAccessWindowSize >= 0 && _rows.size() > _randomAccessWindowSize) {
try {
flushRows(_randomAccessWindowSize);
} catch (IOException ioe) {
throw new RuntimeException(ioe);
}
}
return newRow;
}
/**
* read excel Xls and add the result into arraylist.
*
* @param sheetName excel sheet name
* @throws RuntimeException
*/
public List<Map<String, String>> excelToList(String sheetName) {
Row firstxlRow = null;
FileInputStream fso = null;
List<Map<String, String>> paraList = new ArrayList<Map<String, String>>();
try {
fso = new FileInputStream(fileName);
Workbook workBook = getWorkBook(fso, true);
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
LOG.error("sheet [" + sheetName + "] does not exist!");
return null;
}
firstxlRow = xlSheet.getRow(xlSheet.getFirstRowNum());
int firstCell = firstxlRow.getFirstCellNum();
int lastCell = firstxlRow.getLastCellNum();
List<String> keyList = new ArrayList<String>();
for (int cNum = firstCell; cNum < lastCell; cNum++) {
if (firstxlRow.getCell(cNum).toString() == null) {
break;
}
keyList.add(firstxlRow.getCell(cNum).toString());
}
for (int i = xlSheet.getFirstRowNum() + 1; i < xlSheet.getPhysicalNumberOfRows(); i++) {
xlRow = xlSheet.getRow(i);
List<String> valueList = new ArrayList<String>();
if (xlRow == null) {
break;
}
for (int j = firstCell; j < lastCell; j++) {
xlCell = xlRow.getCell(j);
if (xlCell == null) {
valueList.add(null);
continue;
} else {
valueList.add(xlCell.toString());
}
}
paraList.add(creatMap(keyList, valueList));
}
fso.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("read excel failed:" + e.getMessage());
}
return paraList;
}
private void writeDataToCell(Sheet sheet, int rowNum, int columnNum, Object value) {
LOG.debug(
"writeDataToCell started:"
+ sheet.getSheetName()
+ ",rowNum:"
+ rowNum
+ ",columnNum:"
+ columnNum
+ ",value:"
+ value);
Row row = sheet.getRow(rowNum);
if (row == null) {
row = sheet.createRow(rowNum);
}
Cell cell = row.getCell(columnNum);
if (cell == null) {
int lastColumn = row.getLastCellNum();
if (lastColumn < 0) lastColumn = 0;
// if there are any blank cells, then last cell number will return value higher than actual
// column number.
// so set that value back to your required column number.
if (lastColumn > columnNum) {
lastColumn = columnNum;
}
for (int i = lastColumn; i <= columnNum; i++) {
cell = row.createCell(i);
}
}
if (value instanceof String) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) trimActualResult(value.toString()));
} else if (value instanceof Double) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue((Double) value);
} else if (value instanceof Integer) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue((Integer) value);
} else if (value instanceof Long) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue((Long) value);
} else if (value instanceof Float) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue((Float) value);
} else if (value != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) trimActualResult(value.toString()));
}
}
/**
* 获取Excel数据,返回List<String[]>;
*
* @param sheetNumber 读取工作表的下标(从1开始).可有可无,默认读取所有表单.
*/
public static final List<String[]> excelToArrayList(InputStream is, int... sheetNumber)
throws Exception {
ArrayList<String[]> resultList = new ArrayList<String[]>();
Workbook wb = null;
try {
wb = WorkbookFactory.create(is);
if (sheetNumber == null || sheetNumber.length < 1) {
int sheetCount = wb.getNumberOfSheets(); // 得到所有Excel中页的列表.
sheetNumber = new int[sheetCount];
for (int i = 0; i < sheetNumber.length; i++) {
sheetNumber[i] = i + 1;
}
}
Sheet sheet = null;
for (int k = 0; k < sheetNumber.length; k++) { // 循环工作表
sheet = wb.getSheetAt(sheetNumber[k] - 1);
int rsRows = sheet.getRow(0) == null ? -1 : sheet.getLastRowNum();
log.warn(
"Sheet "
+ sheetNumber[k]
+ "."
+ wb.getSheetName(sheetNumber[k] - 1)
+ ":"
+ (rsRows + 1));
for (int i = 0; i <= rsRows; i++) { // 循环行
Row row = sheet.getRow(i);
if (row != null) {
int cellCount = row.getLastCellNum();
if (cellCount > 0) {
String[] objects = new String[cellCount];
for (int j = 0; j < cellCount; j++) { // 读取单元格
objects[j] = getCellValue(row.getCell(j));
}
resultList.add(objects);
}
}
}
}
} catch (Exception e) {
log.error("Read excel error.", e);
throw e;
}
return resultList;
}
public String[] getHeaders() throws FileNotFoundException, IOException, InvalidFormatException {
InputStream inp;
inp = new FileInputStream(fileName);
Workbook wb = WorkbookFactory.create(inp);
List<String> listHeaders = new ArrayList();
Row row;
Sheet sheet = wb.getSheet(sheetName);
row = sheet.getRow(0);
for (int j = 0; j < row.getLastCellNum(); j++) {
if (row.getCell(j).getStringCellValue().isEmpty()
|| row.getCell(j).getStringCellValue() == null) {
break;
}
listHeaders.add(row.getCell(j).getStringCellValue());
}
inp.close();
return listHeaders.toArray(new String[listHeaders.size()]);
}
private void contentProcess(
Sheet tempSheet, Sheet newSheet, int contentRow, ConfirmationRecord data) throws Exception {
int tempRowNum = -1;
if (data.getAction() == null) {
tempRowNum = 7;
} else if (data.getAction().getParamKey().equals("CONFIRMATION_LOG_ACTION_ACCEPT")) {
tempRowNum = 5;
} else {
tempRowNum = 6;
}
Row tempRow = tempSheet.getRow(tempRowNum);
Row newRow = newSheet.createRow(contentRow);
for (int c = 0; c < tempRow.getLastCellNum(); c++) {
Cell tCell = tempRow.getCell(c);
Cell cell = newRow.createCell(c, tCell.getCellType());
setValue(cell, data);
cell.setCellStyle(tCell.getCellStyle());
cell.getRow().getSheet().setColumnWidth(c, tempRow.getSheet().getColumnWidth(c));
}
}
@Override
public List<Object> getRowValues(int rowIndex) {
Row row = dataSheet.getRow(rowIndex);
ArrayList<Object> values = new ArrayList<>();
if (row == null) return values;
int nullCount = 0;
int colCount = row.getLastCellNum();
for (int col = 0; col < colCount; col++) {
Cell cell = row.getCell(col);
// treat rows with merged cells as "empty"
if (isMerged(cell)) {
LogMgr.logDebug(
"ExcelReader.getRowValues()",
dataSheet.getSheetName()
+ ": column:"
+ cell.getColumnIndex()
+ ", row:"
+ cell.getRowIndex()
+ " is merged. Ignoring row!");
return Collections.emptyList();
}
Object value = getCellValue(cell);
if (value == null) {
nullCount++;
}
values.add(value);
}
if (nullCount == values.size()) {
// return an empty list if all columns are null
values.clear();
}
return values;
}
/**
* 获取row的数据，利用反射机制构建Object对象
*
* @autor:chenssy
* @data:2014年8月9日
* @param row row对象
* @param properties Object参考的属性
* @param methodMap object对象的setter方法映射
* @param fieldMap object对象的属性映射
* @return
* @throws Exception
*/
@SuppressWarnings("rawtypes")
private static Object getObject(
Row row,
String[] properties,
Map<String, Method> methodMap,
Map<String, Field> fieldMap,
Class obj)
throws Exception {
Object object = obj.newInstance();
for (int numCell = 0; numCell < row.getLastCellNum(); numCell++) {
Cell cell = row.getCell(numCell);
if (cell == null) {
continue;
}
String cellValue = getValue(cell);
String property = properties[numCell].toLowerCase();
Field field = fieldMap.get(property); // 该property在object对象中对应的属性
Method method = methodMap.get(property); // 该property在object对象中对应的setter方法
setObjectPropertyValue(object, field, method, cellValue);
}
return object;
}
public static void exportExcelForMap(
Workbook workBook, List<Map<String, Object>> dataList, OutputStream outputStream) {
try {
Sheet sheet = workBook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
Row row = sheet.getRow(lastRowNum);
int rowNum = row.getLastCellNum();
List<String> rowNames = new ArrayList<String>();
List<CellStyle> styles = new ArrayList<CellStyle>();
for (int i = 0; i < rowNum; i++) {
Cell cell = row.getCell(i);
rowNames.add(cell.getStringCellValue());
styles.add(cell.getCellStyle());
}
if (dataList != null && dataList.size() > 0) {
for (int i = 0; i < dataList.size(); i++) {
Map<String, Object> rowData = dataList.get(i);
rowData.put("NUM", lastRowNum + i);
createRow(sheet, rowData, rowNames, styles, lastRowNum + i);
rowData = null;
}
} else {
sheet.removeRow(row);
}
workBook.write(outputStream);
outputStream.flush();
} catch (IOException e) {
throw new RuntimeException("导出文件发生异常");
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
throw new RuntimeException("关闭导出文件流发生异常");
}
}
}
}
public static void debugRow(Sheet sheet, Integer rowIndex) {
// Using a StringBuffer to create one line with all column titles
StringBuffer sb = new StringBuffer();
sb.append("Debugging Row " + rowIndex + " ... ");
// Selecting first row since this should be the place where the column titles should be placed
Row firstRow = sheet.getRow(rowIndex);
// Iterating through all cells in first row
for (short i = firstRow.getFirstCellNum(); i < firstRow.getLastCellNum(); i++) {
Cell cell = firstRow.getCell(i);
// Get the Cell Value as Object
Object object = DataUtil.getCellValue(cell);
// Get a String representation of the value
String cellValue = getStringValue(object);
// Add the content of the cell to StringBuffer
sb.append("Column " + i + ": '" + cellValue + "' ");
}
// Show the StringBuffer content in logging
logger.info(sb.toString());
}
/**
* @Title: HopIncBlh.java @Description: TODO(导入药品)
*
* @param res
* @return:void
* @author zhouxin
* @date 2014年6月10日 下午2:37:46
* @version V1.0
* @throws IOException
*/
@SuppressWarnings("unchecked")
public void upload(BusinessRequest res) throws IOException {
HopIncDto dto = super.getDto(HopIncDto.class, res);
dto.setOpFlg("1");
// 生成随机文件名
String newFileName = UUID.randomUUID().toString();
// 获取文件存储路径
String storageFileName = ServletActionContext.getServletContext().getRealPath("/uploadtmps");
// 判断文件存储路径是否存在，若不存在则自动新建
File document = new File(storageFileName);
if (!document.exists()) {
document.mkdir();
}
File dstFile = new File(storageFileName, newFileName);
com.dhcc.framework.util.FileUtils.copyFile(dto.getUpload(), dstFile, BaseConstants.BUFFER_SIZE);
//
SysImpModelDto SysImpModelDto = new SysImpModelDto();
SysImpModelDto.setImpModel(new ImpModel());
SysImpModelDto.getImpModel().setType("INC");
List<ImpModel> listImpModels = sysImpModelService.getModelList(SysImpModelDto);
Map<Integer, String> modelMap = new HashMap<Integer, String>();
for (int i = 0; i < listImpModels.size(); i++) {
modelMap.put(
Integer.valueOf(listImpModels.get(i).getSeq().toString()),
listImpModels.get(i).getCode());
}
// 读取excel
try {
List<HopInc> hopIncs = new ArrayList<HopInc>();
// 读取Excel文件
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
String prefix =
dto.getUploadFileName().substring(dto.getUploadFileName().lastIndexOf(".") + 1);
if (prefix.equals("xls")) {
workbook =
new HSSFWorkbook(new FileInputStream(storageFileName + File.separator + newFileName));
} else if (prefix.equals("xlsx")) {
workbook =
new XSSFWorkbook(new FileInputStream(storageFileName + File.separator + newFileName));
} else {
dto.setOpFlg("-1");
dto.setMsg("<br>文件类型错误:");
WebContextHolder.getContext().getResponse().getWriter().write(JsonUtils.toJson(dto));
return;
}
sheet = workbook.getSheetAt(0);
// 明细
for (int numRows = 1; numRows <= sheet.getLastRowNum(); numRows++) {
row = sheet.getRow(numRows);
HopInc hopInc = new HopInc();
for (int numCells = 0; numCells <= row.getLastCellNum(); numCells++) {
cell = row.getCell(numCells);
String colNameString = modelMap.get(numCells);
if (StringUtils.isNullOrEmpty(colNameString)) {
colNameString = " ";
}
;
switch (colNameString) {
case "HOSPINC_CODE":
if (cell != null) {
hopInc.setIncCode(cell.toString());
}
break;
case "HOSPINC_NAME":
if (cell != null) {
hopInc.setIncName(cell.toString());
}
break;
case "HOSPINC_SPEC":
if (cell != null) {
hopInc.setIncSpec(cell.toString());
}
break;
case "HOSPINC_PUOM":
if (cell != null) {
hopInc.setIncUomname(cell.toString());
}
break;
case "HOSPINC_RP":
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
hopInc.setIncRp((float) (cell.getNumericCellValue()));
}
break;
case "HOSPINC_MANF":
if (cell != null) {
if (hopManfService.getIdByName(cell.toString()) == null) {
HopManf manf = new HopManf();
manf.setManfName(cell.toString());
manf.setManfHisid(
WebContextHolder.getContext().getVisit().getUserInfo().getHopId());
commonService.saveOrUpdate(manf);
hopInc.setIncManfid(manf.getHopManfId());
} else {
hopInc.setIncManfid(hopManfService.getIdByName(cell.toString()));
}
}
break;
case "HOSPINC_CAT":
if (cell != null) {
hopInc.setIncCat(cell.toString());
}
break;
case "HOSPINC_ALIAS":
if (cell != null) {
hopInc.setIncAliaS(cell.toString());
}
break;
case "HOSPINC_PUOMCODE":
if (cell != null) {
hopInc.setIncUomcode(cell.toString());
}
break;
case "HOSPINC_SP":
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
hopInc.setIncSp((float) (cell.getNumericCellValue()));
}
break;
}
}
// 验证数据的完整性
if (org.apache.commons.lang.StringUtils.isBlank(hopInc.getIncCode())) {
dto.setOpFlg("-1");
dto.setMsg("<br>" + "第" + numRows + "行医院商品代码不能为空！");
continue;
} else {
if (dto.getOpFlg().equals("1")) {
DetachedCriteria criteria = DetachedCriteria.forClass(HopInc.class);
criteria.add(Restrictions.eq("incCode", hopInc.getIncCode()));
criteria.add(
Restrictions.eq(
"hopHopId", Long.valueOf(super.getLoginInfo().get("HOSPID").toString())));
List<HopInc> hopIncsIds = commonService.findByDetachedCriteria(criteria);
if (hopIncsIds.size() > 0) {
hopInc.setIncHospid(hopIncsIds.get(0).getIncHospid());
hopInc.setIncId(hopIncsIds.get(0).getIncId());
}
}
}
hopIncs.add(hopInc);
}
if (dto.getOpFlg().equals("1")) {
dto.setHopIncs(hopIncs);
hopIncService.saveInc(dto);
}
workbook = null;
FileUtils.forceDelete(dstFile);
} catch (Exception e) {
e.printStackTrace();
dto.setOpFlg("-1");
dto.setMsg("<br>" + dto.getMsg() + "<br>" + e.getMessage());
} finally {
super.writeJSON(dto);
FileUtils.forceDelete(dstFile);
}
}
public static List<List<List<String>>> readExcel(File file, Rule rule) {
int start = rule.getStart();
int end = rule.getEnd();
List<List<List<String>>> result = Lists.newArrayList();
Workbook wb;
try {
wb = WorkbookFactory.create(file);
} catch (Exception e) {
throw new ExcelException(e);
}
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
List<List<String>> sheetList = Lists.newArrayList();
int rows = sheet.getLastRowNum();
if (start <= sheet.getFirstRowNum()) {
start = sheet.getFirstRowNum();
}
if (end >= rows) {
end = rows;
} else if (end <= 0) {
end = rows + end;
}
for (int rowIndex = start; rowIndex <= end; rowIndex++) {
Row row = sheet.getRow(rowIndex);
List<String> columns = Lists.newArrayList();
int cellNum = row.getLastCellNum();
System.out.println(row.getLastCellNum());
System.out.println(row.getPhysicalNumberOfCells());
for (int cellIndex = row.getFirstCellNum(); cellIndex < cellNum; cellIndex++) {
Cell cell = row.getCell(cellIndex);
int cellType = cell.getCellType();
String column = "";
switch (cellType) {
case Cell.CELL_TYPE_NUMERIC:
// DecimalFormat format = new DecimalFormat();
// format.setGroupingUsed(false);
column = String.valueOf(cell.getDateCellValue());
break;
case Cell.CELL_TYPE_STRING:
column = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
column = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA:
column = cell.getCellFormula();
break;
case Cell.CELL_TYPE_ERROR:
case Cell.CELL_TYPE_BLANK:
column = " ";
break;
default:
}
columns.add(column.trim());
}
List<Boolean> rowFilterFlagList = Lists.newArrayList();
List<RowFilter> rowFilterList = Lists.newArrayList();
for (int k = 0; k < rowFilterList.size(); k++) {
RowFilter rowFilter = rowFilterList.get(k);
rowFilterFlagList.add(rowFilter.doFilter(rowIndex, columns));
}
if (!rowFilterFlagList.contains(false)) {
sheetList.add(columns);
}
}
result.add(sheetList);
}
return result;
}
@Override
public RecordSet getRecords() throws MalformedSourceException {
try {
openReader();
} catch (IOException e) {
logger_.info("Problem loading file: " + filename + " (" + e.getMessage() + ")");
throw new MalformedSourceException(
"Problem loading file: " + filename + " (" + e.getMessage() + ")");
} catch (InvalidFormatException e) {
logger_.info("Problem loading file: " + filename + " (" + e.getMessage() + ")");
throw new MalformedSourceException(
"Problem loading file: " + filename + " (" + e.getMessage() + ")");
}
RecordSet ret = new RecordSet();
// Currently we need this flag in order for
// TransformationEngine not to go into an infinite loop.
if (!isRead_) {
logger_.info("Opening file: " + filename);
int nSheets = wb_.getNumberOfSheets();
logger_.info("number of sheets: " + nSheets);
for (int i = 0; i < nSheets; i++) {
Sheet cSheet = wb_.getSheetAt(i);
String cSheetName = cSheet.getSheetName();
for (int j = skipLines_; j <= cSheet.getLastRowNum(); j++) {
if (ignoreLinesAfter_ != 0 && j >= ignoreLinesAfter_) {
break;
}
Row row = cSheet.getRow(j);
MapRecord rec = new MapRecord();
for (int k = 0; k < row.getLastCellNum(); k++) {
if (!fieldMap_.keySet().contains(k)) {
continue;
}
StringValue val;
Cell cCell = row.getCell(k);
if (cCell == null) {
continue;
}
int cellType = cCell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_STRING:
val = new StringValue(cCell.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cCell)) {
SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
val = new StringValue(sdf.format(cCell.getDateCellValue()));
} else {
val = new StringValue(String.valueOf(cCell.getNumericCellValue()));
}
break;
case Cell.CELL_TYPE_BLANK:
val = new StringValue("");
break;
case Cell.CELL_TYPE_BOOLEAN:
val = new StringValue(String.valueOf(cCell.getBooleanCellValue()));
break;
default:
val = new StringValue("Unsupported cell type");
}
rec.addValue(fieldMap_.get(k), val);
}
// TODO remove the hardcoded value
rec.addValue("ExcelSheetName", new StringValue(cSheetName));
ret.addRecord(rec);
}
}
isRead_ = true;
}
return ret;
}
