/**
* Test get new id for shapes from existing file File already have for 1 shape on each sheet,
* because document must contain EscherDgRecord for each sheet
*/
public void testAllocateNewIds() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("empty.xls");
HSSFSheet sheet = wb.getSheetAt(0);
HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
/** 2048 - main SpContainer id 2049 - existing shape id */
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2050);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2051);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2052);
sheet = wb.getSheetAt(1);
patriarch = sheet.getDrawingPatriarch();
/** 3072 - main SpContainer id 3073 - existing shape id */
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3074);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3075);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3076);
sheet = wb.getSheetAt(2);
patriarch = sheet.getDrawingPatriarch();
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1026);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1027);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1028);
}
/**
* 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;
}
public void exportExcel(
String title,
Collection<T> dataset,
HttpServletResponse out,
Map<String, String> argsMap,
InputStream is,
Map<String, String> titleMap) {
try {
if (is != null && workbook == null) {
initworkbook(is);
}
if (dataset == null || dataset.isEmpty()) {
throw new Exception("传入的数据是null");
}
Iterator<T> it = dataset.iterator();
if ((dataset == null) || (!it.hasNext()) || (title == null) || (out == null)) {
throw new Exception(
"传入的数据不对！(dataset == null) || (!its.hasNext()) || (title == null) || (out == null)");
}
HSSFSheet sheet = workbook.getSheetAt(0);
if (argsMap.containsKey("sheetnum")) {
sheet = workbook.getSheetAt(Integer.parseInt(argsMap.get("sheetnum")));
}
int index = this.startrow;
// 更改标题等
this.setCellsByMap(sheet, titleMap);
// 循环行 从起始行startrow开始
while (it.hasNext()) {
HSSFRow row = sheet.createRow(index);
// 如果参数map中有rowHeight
if (argsMap.containsKey("rowHeight")) {
row.setHeight(Short.parseShort(argsMap.get("rowHeight")));
} else { // 如果参数map中没有有rowHeight 则默认为上一行的高度
row.setHeight(getlastHeight(row, sheet));
}
Map<String, Method> fieldmap = this.initFieldMap();
T t = it.next();
// 为一行的每一cell赋值
this.setCell(row, sheet, fieldmap, t);
index++;
}
// 屏幕输出
this.output(out, title, workbook);
} catch (Exception e) {
e.printStackTrace();
}
}
public void testReadCell() throws Exception {
FileInputStream in = new FileInputStream("src/test/resources/date.xls");
HSSFWorkbook wb = new HSSFWorkbook(in);
HSSFCell dateCell = wb.getSheetAt(0).getRow(0).getCell(0);
HSSFCell dateTimeCell = wb.getSheetAt(0).getRow(0).getCell(1);
Object value = ExcelItemReader.getCellValue(dateCell);
Assert.assertTrue(value instanceof Date);
Object value2 = ExcelItemReader.getCellValue(dateTimeCell);
Assert.assertTrue(value2 instanceof Date);
in.close();
}
public void testClearShapesForPatriarch() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
patriarch.createSimpleShape(new HSSFClientAnchor());
patriarch.createSimpleShape(new HSSFClientAnchor());
patriarch.createCellComment(new HSSFClientAnchor());
EscherAggregate agg = HSSFTestHelper.getEscherAggregate(patriarch);
assertEquals(agg.getShapeToObjMapping().size(), 6);
assertEquals(agg.getTailRecords().size(), 1);
assertEquals(patriarch.getChildren().size(), 3);
patriarch.clear();
assertEquals(agg.getShapeToObjMapping().size(), 0);
assertEquals(agg.getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 0);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(agg.getShapeToObjMapping().size(), 0);
assertEquals(agg.getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 0);
}
public void testRotation() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFSimpleShape rectangle =
patriarch.createSimpleShape(
new HSSFClientAnchor(0, 0, 100, 100, (short) 0, 0, (short) 5, 5));
rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
assertEquals(rectangle.getRotationDegree(), 0);
rectangle.setRotationDegree((short) 45);
assertEquals(rectangle.getRotationDegree(), 45);
rectangle.setFlipHorizontal(true);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0);
assertEquals(rectangle.getRotationDegree(), 45);
rectangle.setRotationDegree((short) 30);
assertEquals(rectangle.getRotationDegree(), 30);
patriarch.setCoordinates(0, 0, 10, 10);
rectangle.setString(new HSSFRichTextString("1234"));
}
public void testShapeIds() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet();
HSSFPatriarch patriarch1 = sheet1.createDrawingPatriarch();
for (int i = 0; i < 2; i++) {
patriarch1.createSimpleShape(new HSSFClientAnchor());
}
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet1 = wb.getSheetAt(0);
patriarch1 = sheet1.getDrawingPatriarch();
EscherAggregate agg1 = HSSFTestHelper.getEscherAggregate(patriarch1);
// last shape ID cached in EscherDgRecord
EscherDgRecord dg1 = agg1.getEscherContainer().getChildById(EscherDgRecord.RECORD_ID);
assertEquals(1026, dg1.getLastMSOSPID());
// iterate over shapes and check shapeId
EscherContainerRecord spgrContainer = agg1.getEscherContainer().getChildContainers().get(0);
// root spContainer + 2 spContainers for shapes
assertEquals(3, spgrContainer.getChildRecords().size());
EscherSpRecord sp0 =
((EscherContainerRecord) spgrContainer.getChild(0)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1024, sp0.getShapeId());
EscherSpRecord sp1 =
((EscherContainerRecord) spgrContainer.getChild(1)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1025, sp1.getShapeId());
EscherSpRecord sp2 =
((EscherContainerRecord) spgrContainer.getChild(2)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1026, sp2.getShapeId());
}
private void process_other(int idMo, ArrayList<String> queries) {
sheet = wb.getSheetAt(4);
queries.add(
String.format("DELETE FROM offers_other WHERE id_mo = '%d' AND year='%d';", idMo, YEAR));
int numberOfProfile = 0;
for (int i = 6; i < 16; i++) {
if (i == 8) continue;
row = sheet.getRow(i);
int offer = 0;
if (i < 10) {
offer = (int) row.getCell(2).getNumericCellValue();
} else {
offer = (int) row.getCell(1).getNumericCellValue();
}
int idprofile = Constants.planPatOther[numberOfProfile++];
queries.add(
String.format(
"INSERT INTO offers_other VALUES(NULL, '%d', '%d', '%d', '%d');",
idMo, idprofile, offer, YEAR));
}
}
/**
* 测试单元格样式
*
* @author David
* @param wb
* @param cell
* @param td
*/
private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {
Attribute typeAttr = td.getAttribute("type");
String type = typeAttr.getValue();
HSSFDataFormat format = wb.createDataFormat();
HSSFCellStyle cellStyle = wb.createCellStyle();
if ("NUMERIC".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
Attribute formatAttr = td.getAttribute("format");
String formatValue = formatAttr.getValue();
formatValue = StringUtils.isNotBlank(formatValue) ? formatValue : "#,##0.00";
cellStyle.setDataFormat(format.getFormat(formatValue));
} else if ("STRING".equalsIgnoreCase(type)) {
cell.setCellValue("");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellStyle.setDataFormat(format.getFormat("@"));
} else if ("DATE".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
} else if ("ENUM".equalsIgnoreCase(type)) {
CellRangeAddressList regions =
new CellRangeAddressList(
cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());
Attribute enumAttr = td.getAttribute("format");
String enumValue = enumAttr.getValue();
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(","));
// 数据有效性对象
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
wb.getSheetAt(0).addValidationData(dataValidation);
}
cell.setCellStyle(cellStyle);
}
private void autoSizeColumns() {
int sheetCount = workbook.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
HSSFSheet sheet = workbook.getSheetAt(i);
for (int colnum = 0; colnum <= sheet.getLastRowNum(); colnum++) sheet.autoSizeColumn(colnum);
}
}
public static void read(int rowxi, int rowxj) {
try {
POIFSFileSystem fs;
fs =
new POIFSFileSystem(
new FileInputStream(
"F:\\工作\\1_Java资料\\5_workspace\\hhw\\poi\\src\\com\\hhw\\poi\\temp1.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
for (int i = rowxi - 1; i < rowxj; i++) {
String name = sheet.getRow(i).getCell(0).getStringCellValue();
String bankName = name.substring(0, name.indexOf("(") - 1);
String bankNo = name.substring(name.indexOf("(") + 1, name.indexOf(")"));
sheet.getRow(i).getCell(13).setCellType(Cell.CELL_TYPE_STRING);
String bin = sheet.getRow(i).getCell(13).getStringCellValue();
String cardType = sheet.getRow(i).getCell(15).getStringCellValue();
System.out.println(bankName + "," + bankNo + "," + bin + "," + cardType);
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static String readExcel(String excelPath, int sheetNu, int rowNu, int cellNu)
throws Exception {
/** read Excel file with the Path get cell in sheetNu, rowNu and cellNu */
String cellValue = null;
FileInputStream fos = new FileInputStream(excelPath);
HSSFWorkbook wb = new HSSFWorkbook(fos);
HSSFSheet sheet = wb.getSheetAt(sheetNu);
HSSFRow row = sheet.getRow(rowNu);
try {
HSSFCell cell = row.getCell(cellNu);
if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
cellValue = cell.getStringCellValue();
// System.out.println("_________________"+cellValue);
}
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
cellValue = Long.toString((long) (cell.getNumericCellValue()));
// System.out.println("_________________"+cellValue);
}
return cellValue;
} catch (NullPointerException e) {
return null;
}
}
/**
* 根据模板初始化样式 注意：module.xls模板文件跟该类同一路径
*
* @param wb
* @return
*/
private HashMap<String, HSSFCellStyle> initStyles(HSSFWorkbook wb) {
HashMap<String, HSSFCellStyle> ret = new HashMap<String, HSSFCellStyle>();
try {
ClassPathResource res =
new ClassPathResource("module.xls", this.getClass()); // 注意：module.xls模板文件跟该类同一路径
InputStream is = res.getInputStream();
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook src = new HSSFWorkbook(fs);
HSSFSheet sheet = src.getSheetAt(0);
buildStyle(wb, src, sheet, 0, ret, "TITLE"); // 标题样式
buildStyle(wb, src, sheet, 1, ret, "SUB_TITLE"); // 副标题样式
buildStyle(wb, src, sheet, 2, ret, "SUB_TITLE2"); // 副标题2样式
buildStyle(wb, src, sheet, 4, ret, "TABLE_HEADER"); // 表头样式
buildStyle(wb, src, sheet, 5, ret, "STRING"); // 字符串单元格样式
buildStyle(wb, src, sheet, 6, ret, "INT"); // 整数单元格样式
buildStyle(wb, src, sheet, 7, ret, "D2"); // 2位小数单元格样式
buildStyle(wb, src, sheet, 8, ret, "D3"); // 3位小数单元格样式
buildStyle(wb, src, sheet, 10, ret, "STRING_C"); // 字符串单元格样式（带背景色）
buildStyle(wb, src, sheet, 11, ret, "INT_C"); // 整数单元格样式（带背景色）
buildStyle(wb, src, sheet, 12, ret, "D2_C"); // 2位小数单元格样式（带背景色）
buildStyle(wb, src, sheet, 13, ret, "D3_C"); // 3位小数单元格样式（带背景色）
buildStyle(wb, src, sheet, 15, ret, "RED_BG"); // 红色单元格背景
buildStyle(wb, src, sheet, 16, ret, "YELLOW_BG"); // 黄色单元格背景
buildStyle(wb, src, sheet, 17, ret, "GREEN_BG"); // 绿色单元格背景
} catch (Exception e) {
e.printStackTrace();
}
return ret;
}
private HSSFRowSource(HSSFWorkbook workbook) {
this.workbook = workbook;
this.formatter = new HSSFDataFormatter();
this.evaluator = new HSSFFormulaEvaluator(workbook);
this.stringCache = new ArrayList<String>();
initSheetIterator(workbook.getSheetAt(0));
}
/**
* Sometimes the 'shared formula' flag (<tt>FormulaRecord.isSharedFormula()</tt>) is set when
* there is no corresponding SharedFormulaRecord available. SharedFormulaRecord definitions do not
* span multiple sheets. They are are only defined within a sheet, and thus they do not have a
* sheet index field (only row and column range fields).<br>
* So it is important that the code which locates the SharedFormulaRecord for each FormulaRecord
* does not allow matches across sheets.</br>
*
* <p>Prior to bugzilla 44449 (Feb 2008), POI <tt>ValueRecordsAggregate.construct(int, List)</tt>
* allowed <tt>SharedFormulaRecord</tt>s to be erroneously used across sheets. That incorrect
* behaviour is shown by this test.
*
* <p><b>Notes on how to produce the test spreadsheet</b>: The setup for this test
* (AbnormalSharedFormulaFlag.xls) is rather fragile, insomuchas re-saving the file (either with
* Excel or POI) clears the flag.<br>
*
* <ol>
* <li>A new spreadsheet was created in Excel (File | New | Blank Workbook).
* <li>Sheet3 was deleted.
* <li>Sheet2!A1 formula was set to '="second formula"', and fill-dragged through A1:A8.
* <li>Sheet1!A1 formula was set to '="first formula"', and also fill-dragged through A1:A8.
* <li>Four rows on Sheet1 "5" through "8" were deleted ('delete rows' alt-E D, not 'clear'
* Del).
* <li>The spreadsheet was saved as AbnormalSharedFormulaFlag.xls.
* </ol>
*
* Prior to the row delete action the spreadsheet has two <tt>SharedFormulaRecord</tt>s. One for
* each sheet. To expose the bug, the shared formulas have been made to overlap.<br>
* The row delete action (as described here) seems to to delete the <tt>SharedFormulaRecord</tt>
* from Sheet1 (but not clear the 'shared formula' flags.<br>
* There are other variations on this theme to create the same effect.
*/
public void testSpuriousSharedFormulaFlag() {
long actualCRC =
getFileCRC(
HSSFTestDataSamples.openSampleFileStream(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE));
long expectedCRC = 2277445406L;
if (actualCRC != expectedCRC) {
System.err.println("Expected crc " + expectedCRC + " but got " + actualCRC);
throw failUnexpectedTestFileChange();
}
HSSFWorkbook wb =
HSSFTestDataSamples.openSampleWorkbook(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE);
HSSFSheet s = wb.getSheetAt(0); // Sheet1
String cellFormula;
cellFormula = getFormulaFromFirstCell(s, 0); // row "1"
// the problem is not observable in the first row of the shared formula
if (!cellFormula.equals("\"first formula\"")) {
throw new RuntimeException("Something else wrong with this test case");
}
// but the problem is observable in rows 2,3,4
cellFormula = getFormulaFromFirstCell(s, 1); // row "2"
if (cellFormula.equals("\"second formula\"")) {
throw new AssertionFailedError("found bug 44449 (Wrong SharedFormulaRecord was used).");
}
if (!cellFormula.equals("\"first formula\"")) {
throw new RuntimeException("Something else wrong with this test case");
}
}
/**
* This method is used to display the Excel content to command line.
*
* @param xlsPath
*/
@SuppressWarnings("unchecked")
public void displayFromExcel(InputStream inputStream) {
POIFSFileSystem fileSystem = null;
try {
fileSystem = new POIFSFileSystem(inputStream);
Map headerTable = null;
HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
HSSFSheet sheet = workBook.getSheetAt(0);
System.out.println("LastRowNum:" + sheet.getLastRowNum());
Iterator<HSSFRow> rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = rows.next();
// display row number in the console.
System.out.println("Row No.: " + row.getRowNum());
Contact contact = null;
if (row.getRowNum() == 0) {
headerTable = headerTable(row);
} else {
contact = processRow(row, headerTable);
if (contact != null) contactManager.saveContact(contact);
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 读取 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;
}
/**
* Reads userdata from all sheets in workbook.
*
* @return {@link List} of {@link User}.
*/
public List<User> getUsers() {
List<User> vUsers = new ArrayList<User>();
if (mFileType == FileTypes.EXCEL && mWorkbook != null) {
// go through every sheet as house
logger.info("Importing user data from excel file");
int vNumberOfSheets = mWorkbook.getNumberOfSheets();
for (int i = 0; i < vNumberOfSheets; i++) {
HSSFSheet vSheet = mWorkbook.getSheetAt(i);
String vSheetName = vSheet.getSheetName();
logger.info("Sheet name: " + vSheetName);
// try to figure out columns to use
int[] vColumns = getUserdataColumnsFromSheet(vSheet);
// get userdata from sheet
vUsers.addAll(getUsers(vSheet, vColumns));
}
} else if (mFileType == FileTypes.CSV && mExcelFile != null) {
logger.info("Importing user data from csv file.");
vUsers.addAll(getUsers(mExcelFile));
} else {
logger.error("Cannot get userdata from workbook. No workbook loaded!");
}
return vUsers;
}
public Collection<Tree> read(final String fileName) throws IOException {
List<Tree> result = new ArrayList<Tree>();
InputStream file = getClass().getResourceAsStream(fileName);
if (file == null) {
throw new IllegalArgumentException("File " + fileName + " not found");
}
try {
// Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
// Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
// Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();
rowIterator.next(); // ignore column title
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
TreeBuilder reader = new TreeBuilder(row);
Tree tree = reader.buildTree();
result.add(tree);
System.out.println(tree.toString());
}
} finally {
file.close();
}
return result;
}
/** tests <tt>NameXPtg.toFormulaString(Workbook)</tt> and logic in Workbook below that */
public void testReadFormulaContainingExternalFunction() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");
String expectedFormula = "YEARFRAC(B1,C1)";
HSSFSheet sht = wb.getSheetAt(0);
String cellFormula = sht.getRow(0).getCell(0).getCellFormula();
assertEquals(expectedFormula, cellFormula);
}
public int getIdMo() {
sheet = wb.getSheetAt(0);
row = sheet.getRow(6);
cell = row.getCell(0);
DBWorkerMO dbWorkerMO = new DBWorkerMO();
return dbWorkerMO.getIdMo((int) cell.getNumericCellValue());
}
/**
* @param filepath //文件路径
* @param filename //文件名
* @param startrow //开始行号
* @param startcol //开始列号
* @param sheetnum //sheet
* @return list
*/
public static List<Object> readExcel(
String filepath, String filename, int startrow, int startcol, int sheetnum) {
List<Object> varList = new ArrayList<Object>();
try {
File target = new File(filepath, filename);
FileInputStream fi = new FileInputStream(target);
HSSFWorkbook wb = new HSSFWorkbook(fi);
HSSFSheet sheet = wb.getSheetAt(sheetnum); // sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; // 取得最后一行的行号
for (int i = startrow; i < rowNum; i++) { // 行循环开始
PageData varpd = new PageData();
HSSFRow row = sheet.getRow(i); // 行
int cellNum = row.getLastCellNum(); // 每行的最后一个单元格位置
for (int j = startcol; j < cellNum; j++) { // 列循环开始
HSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case 0:
cellValue = String.valueOf((int) cell.getNumericCellValue());
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
// cellValue = String.valueOf(cell.getDateCellValue());
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
varpd.put("var" + j, cellValue);
}
varList.add(varpd);
}
} catch (Exception e) {
System.out.println(e);
}
return varList;
}
@DataProvider(name = "placeOrderCredentials")
public Iterator<Object[]> readFromExcelIterator() throws FileNotFoundException, IOException {
// prop.load(getClass().getResourceAsStream("AutomationEnv.properties"));
// String sourceXlsFileName=(String)prop.get("signUpAndOrderCard");
String fileName =
"C:\\Users\\WINQA\\workspace\\NewHkAutomationSuite\\Excel\\signUpandPlace.xls";
int sheetNo = 0;
ArrayList<Object[]> excelDataArray = new ArrayList<Object[]>();
int cnt = 0;
try {
InputStream input = new BufferedInputStream(new FileInputStream(fileName));
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(sheetNo);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
int cellCount = 0;
int flagStop = 0;
HSSFRow row = (HSSFRow) rows.next();
System.out.println("\n");
Iterator cells = row.cellIterator();
List<String> readExcelData = new LinkedList<String>();
while (cells.hasNext()) {
if (cellCount <= 10) {
int cellValueInt;
String CellValue;
HSSFCell cell = (HSSFCell) cells.next();
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
// System.out.print( cell.getNumericCellValue()+" " );
cellValueInt = (int) cell.getNumericCellValue();
CellValue = Integer.toString(cellValueInt);
} else {
CellValue = cell.getStringCellValue();
}
readExcelData.add(CellValue);
cnt++;
cellCount++;
}
if (cellCount == 11) break;
}
excelDataArray.add(new Object[] {readExcelData});
}
} catch (IOException ex) {
ex.printStackTrace();
}
return excelDataArray.iterator();
}
@Override
public String executeAction(HttpServletRequest request, List<FileItem> sessionFiles)
throws UploadActionException {
StringBuffer response = new StringBuffer();
for (FileItem item : sessionFiles) {
if (false == item.isFormField()) {
try {
if (item.getName().endsWith(".xls")) {
POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
HSSFWorkbook wb = new HSSFWorkbook(fs);
System.out.println("Sheet Num:" + wb.getNumberOfSheets());
// only get first sheet,ignore others
HSSFSheet sheet = wb.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
response.append(cell.toString() + ":");
}
response.append("\n");
}
} else if (item.getName().endsWith(".xlsx")) {
// POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
XSSFWorkbook wb = new XSSFWorkbook(item.getInputStream());
System.out.println("Sheet Num:" + wb.getNumberOfSheets());
// only get first sheet,ignore others
XSSFSheet sheet = wb.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
XSSFRow row = (XSSFRow) rows.next();
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
XSSFCell cell = (XSSFCell) cells.next();
response.append(cell.toString() + ":");
}
response.append("\n");
}
}
} catch (Exception e) {
throw new UploadActionException(e);
}
}
}
// / Remove files from session because we have a copy of them
removeSessionFileItems(request);
// / Send your customized message to the client.
return response.toString();
}
/** Creates a new XlsDataSet object that loads the specified Excel document. */
public XlsDataSet(InputStream in) throws IOException, DataSetException {
_tables = super.createTableNameMap();
HSSFWorkbook workbook = new HSSFWorkbook(in);
int sheetCount = workbook.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
ITable table = new XlsTable(workbook.getSheetName(i), workbook.getSheetAt(i));
_tables.add(table.getTableMetaData().getTableName(), table);
}
}
public void testEvaluate() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");
HSSFSheet sheet = wb.getSheetAt(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
confirmCellEval(sheet, 0, 0, fe, "YEARFRAC(B1,C1)", 29.0 / 90.0);
confirmCellEval(sheet, 1, 0, fe, "YEARFRAC(B2,C2)", 0.0);
confirmCellEval(sheet, 2, 0, fe, "YEARFRAC(B3,C3,D3)", 0.0);
confirmCellEval(sheet, 3, 0, fe, "IF(ISEVEN(3),1.2,1.6)", 1.6);
confirmCellEval(sheet, 4, 0, fe, "IF(ISODD(3),1.2,1.6)", 1.2);
}
/**
* Load a specific page of the given Excel Spreadsheet
*
* @param bytes Excel content as byte array
* @param page page to use
* @return true on success
*/
public boolean load(final InputStream inputStream, final int page) {
try {
fs = new POIFSFileSystem(inputStream);
HSSFWorkbook wb = new HSSFWorkbook(fs);
sheet = wb.getSheetAt(page);
return true;
} catch (Exception ex) {
return false;
}
}
public List<List<Map>> readSheetData(
int fileIdx, int startRow, String[][] fields, Class<? extends Map>[] classnames)
throws Exception {
List<List<Map>> results = new ArrayList<List<Map>>();
HSSFWorkbook workb = workbooks.get(fileIdx);
for (int i = 0; i < classnames.length; i++)
results.add(readSheet(workb.getSheetAt(i), startRow, fields[i], classnames[i]));
return results;
}
/** @param args */
public static void main(String[] args) {
// TODO Auto-generated method stub
// Directory path here
String path = "./plantillas";
String files;
File folder = new File(path);
File[] listOfFiles = folder.listFiles();
for (int i = 0; i < listOfFiles.length; i++) {
if (listOfFiles[i].isFile()) {
files = listOfFiles[i].getName();
System.out.println(files);
}
}
InputStream input;
try {
input = new BufferedInputStream(new FileInputStream("./plantillas/Panama.xls"));
POIFSFileSystem fs;
fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
System.out.println("\n");
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType())
System.out.print(cell.getNumericCellValue() + " ");
else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType())
System.out.print(cell.getStringCellValue() + " ");
else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType())
System.out.print(cell.getBooleanCellValue() + " ");
else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) System.out.print("BLANK ");
else System.out.print("Unknown cell type");
}
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* Load a specific page of the given Excel Spreadsheet
*
* @param file filename of the Excel file
* @param page page to use
* @return true on success
* @deprecated use load(InputStream) instead
*/
@Deprecated
public boolean load(final String file, final int page) {
try {
fs = new POIFSFileSystem(new FileInputStream(file));
HSSFWorkbook wb = new HSSFWorkbook(fs);
sheet = wb.getSheetAt(page);
return true;
} catch (Exception ex) {
return false;
}
}