/**
* 全てのシートの拡大率を指定する
*
* @param workbook ワークブック
* @param numerator 拡大率は numerator/denominatorで算出される
* @param denominator 拡大率は numerator/denominatorで算出される
*/
public static void setZoom(XSSFWorkbook workbook, int numerator, int denominator) {
assert workbook != null;
for (int nIndex = 0; nIndex < workbook.getNumberOfSheets(); nIndex++) {
XSSFSheet sheet = workbook.getSheetAt(nIndex);
sheet.setZoom(numerator, denominator);
}
}
public void testGetSetColDefaultStyle() {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
CTWorksheet ctWorksheet = sheet.getCTWorksheet();
ColumnHelper columnHelper = sheet.getColumnHelper();
// POI column 3, OOXML column 4
CTCol col = columnHelper.getOrCreateColumn1Based(4, false);
assertNotNull(col);
assertNotNull(columnHelper.getColumn(3, false));
columnHelper.setColDefaultStyle(3, 2);
assertEquals(2, columnHelper.getColDefaultStyle(3));
assertEquals(-1, columnHelper.getColDefaultStyle(4));
StylesTable stylesTable = workbook.getStylesSource();
CTXf cellXf = CTXf.Factory.newInstance();
cellXf.setFontId(0);
cellXf.setFillId(0);
cellXf.setBorderId(0);
cellXf.setNumFmtId(0);
cellXf.setXfId(0);
stylesTable.putCellXf(cellXf);
CTCol col_2 = ctWorksheet.getColsArray(0).addNewCol();
col_2.setMin(10);
col_2.setMax(12);
col_2.setStyle(1);
assertEquals(1, columnHelper.getColDefaultStyle(11));
XSSFCellStyle cellStyle = new XSSFCellStyle(0, 0, stylesTable, null);
columnHelper.setColDefaultStyle(11, cellStyle);
assertEquals(0, col_2.getStyle());
assertEquals(1, columnHelper.getColDefaultStyle(10));
}
public static void main(String args[]) throws Exception {
File file = new File("d:/delete/createworkbook.xlsx");
FileInputStream fis = new FileInputStream(file);
if (file.isFile() && file.exists()) {
// Get the workbook instance for XLSX file
XSSFWorkbook workbook = new XSSFWorkbook(fis);
System.out.println("openworkbook.xlsx file open successfully.");
// get worksheet, 0 based
XSSFSheet sheet = workbook.getSheetAt(0);
// get rows
Iterator<Row> it = sheet.iterator();
while (it.hasNext()) {
Row row = (XSSFRow) it.next();
// get cells
Iterator<Cell> cellIt = row.cellIterator();
while (cellIt.hasNext()) {
Cell cell = cellIt.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + " \t\t ");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + " \t\t ");
break;
}
}
}
} else {
System.out.println("Error to open openworkbook.xlsx file.");
}
}
/**
* generated <code><col><code> tags.
* @param sheet
* @param table container.
*/
private void generateColumns(XSSFSheet sheet, Element table) {
List<CTCols> colsList = sheet.getCTWorksheet().getColsList();
MathContext mc = new MathContext(3);
for (CTCols cols : colsList) {
long oldLevel = 1;
for (CTCol col : cols.getColArray()) {
while (true) {
if (oldLevel == col.getMin()) {
break;
}
Element column = htmlDocumentFacade.createTableColumn();
// htmlDocumentFacade.addStyleClass(column, "col", "width:2cm;");
column.setAttribute("style", "width:2cm;");
table.appendChild(column);
oldLevel++;
}
Element column = htmlDocumentFacade.createTableColumn();
String width =
new BigDecimal(sheet.getColumnWidth(Long.bitCount(oldLevel)) / 1440.0, mc).toString();
column.setAttribute("style", "width:".concat(width).concat("cm;"));
table.appendChild(column);
oldLevel++;
}
}
}
/**
* 全てのシートの計算式を強制的に再評価させる
*
* @param workbook ワークブック
*/
public static void setForceFormulaRecalculation(XSSFWorkbook workbook) {
assert workbook != null;
for (int nIndex = 0; nIndex < workbook.getNumberOfSheets(); nIndex++) {
XSSFSheet sheet = workbook.getSheetAt(nIndex);
sheet.setForceFormulaRecalculation(true);
}
}
@Test
public void dataDrivernTest() throws IOException {
FileInputStream f =
new FileInputStream(
"E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\excelfiles\\registration.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(f);
XSSFSheet ws = wb.getSheet("Sheet1");
Iterator<Row> row = ws.iterator();
row.next();
WelcomeMercuryTours wm = PageFactory.initElements(driver, WelcomeMercuryTours.class);
RegisterMercuryTours1 rm1 = PageFactory.initElements(driver, RegisterMercuryTours1.class);
RegisterMercuryTours2 rm2 = PageFactory.initElements(driver, RegisterMercuryTours2.class);
wm.menu.register();
while (row.hasNext()) {
Row r = row.next();
rm1.contactInformation(r);
boolean result = rm2.validateRegistration(r.getCell(9).getStringCellValue());
if (result == true) {
r.createCell(12).setCellValue("Passed");
} else {
r.createCell(12).setCellValue("Failed");
}
driver.navigate().back();
}
FileOutputStream f1 =
new FileOutputStream(
"E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\resultexcelfiles\\registration.xlsx");
wb.write(f1);
f1.close();
driver.quit();
}
private int createMonth(
XSSFSheet sheet, LocalDate dt, int dayRow, int monthRow, int row, CellCopyPolicy policy) {
int r = row;
final int nbDays = dt.dayOfMonth().getMaximumValue();
final int month = dt.monthOfYear().get();
/* Insert days for the month */
for (int day = 1; day <= nbDays; day++) {
XSSFRow inserted = copyRow(sheet, dayRow, r, policy);
inserted.getCell(COLUMN_MONTH).setCellValue(month);
inserted.getCell(COLUMN_DAY).setCellValue(day);
r++;
}
/* Insert month summary */
XSSFRow inserted = copyRow(sheet, monthRow, r, policy);
inserted.getCell(COLUMN_MONTH).setCellValue(month);
r++;
/* Group month rows */
int groupRow = r - 2;
sheet.groupRow(row, groupRow);
sheet.setRowGroupCollapsed(groupRow, true);
return r;
}
private void printCurso(Curso c) {
int rowNum;
XSSFSheet sheet =
report.createSheet("Curso " + c.getNombre() + (c.getIsMixto() ? "(Mixto)" : "(No Mixto)"));
rowNum = 0;
for (Alumno a : c.getAlumnos()) {
XSSFRow row = sheet.createRow(rowNum++);
int cellNum = 0;
if (rowNum == 1) {
XSSFCell cell = row.createCell(cellNum++);
cell.setCellValue("Id");
cell = row.createCell(cellNum++);
cell.setCellValue("Apellido");
cell = row.createCell(cellNum++);
cell.setCellValue("Nombre");
cell = row.createCell(cellNum++);
cellNum = 0;
row = sheet.createRow(rowNum++);
}
XSSFCell cell = row.createCell(cellNum++);
cell.setCellValue(a.getId());
cell = row.createCell(cellNum++);
cell.setCellValue(a.getApellido());
cell = row.createCell(cellNum++);
cell.setCellValue(a.getNombres());
cell = row.createCell(cellNum++);
}
}
private void printPreferencias() {
int rowNum;
XSSFSheet sheet = report.createSheet("Preferencias");
rowNum = 0;
for (Preferencia p : AlumnosCursosModel.getInstance().getPreferencias()) {
XSSFRow row = sheet.createRow(rowNum++);
int cellNum = 0;
if (rowNum == 1) {
XSSFCell cell = row.createCell(cellNum++);
cell.setCellValue("Id 1");
cell = row.createCell(cellNum++);
cell.setCellValue("Id 2");
cell = row.createCell(cellNum++);
cell.setCellValue("Satisfecha?");
cell = row.createCell(cellNum++);
cellNum = 0;
row = sheet.createRow(rowNum++);
}
XSSFCell cell = row.createCell(cellNum++);
cell.setCellValue(p.getAlumno1().getId());
cell = row.createCell(cellNum++);
cell.setCellValue(p.getAlumno2().getId());
cell = row.createCell(cellNum++);
cell.setCellValue(p.getIsSatisfecha().toString());
cell = row.createCell(cellNum++);
}
}
@Override
public List<Map<String, Object>> parse() throws ReadCensusException {
XSSFWorkbook listaVotantes = null;
String fileName = Paths.get(filePath).getFileName().toString();
try {
FileInputStream file = new FileInputStream(new File(filePath));
listaVotantes = new XSSFWorkbook(file);
XSSFSheet hoja = listaVotantes.getSheetAt(0);
Iterator<Row> rowIterator = hoja.iterator();
Row row;
while (rowIterator.hasNext()) {
row = rowIterator.next();
Iterator<Cell> columns = row.cellIterator();
if (!columns.hasNext()) continue;
try {
voter = new HashMap<String, Object>();
voter.put("name", columns.next().getStringCellValue());
voter.put("email", columns.next().getStringCellValue());
voter.put("nif", columns.next().getStringCellValue());
voter.put("code", String.valueOf((int) columns.next().getNumericCellValue()));
voter.put("password", PasswordGenerator.generate(8));
voter.put("file", fileName);
voter.put("line", row.getRowNum());
} catch (Exception e) {
throw new ReadCensusException(
"[ERROR] ["
+ fileName
+ ":"
+ row.getRowNum()
+ "] El usuario no tiene el formato correcto");
}
voters.add(voter);
}
} catch (FileNotFoundException e) {
throw new ReadCensusException("[ERROR] [" + fileName + "] El fichero no existe");
} catch (Exception e) {
throw new ReadCensusException(
"[ERROR] [" + fileName + "] Fallo inesperado al leer el fichero: " + e.getMessage());
} finally {
try {
if (listaVotantes != null) listaVotantes.close();
} catch (Exception e) {
throw new ReadCensusException("[ERROR] [" + fileName + "] I/O Error: " + e.getMessage());
}
}
if (voters.isEmpty()) {
throw new ReadCensusException("[AVISO] [" + fileName + "] El censo está vacío");
}
return voters;
}
public static void main(String[] args) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook(); // or new HSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
XSSFRow row = sheet.createRow((short) 2);
XSSFCell cell = row.createCell(1);
XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox");
XSSFFont font1 = wb.createFont();
font1.setBold(true);
font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
rt.applyFont(0, 10, font1);
XSSFFont font2 = wb.createFont();
font2.setItalic(true);
font2.setUnderline(XSSFFont.U_DOUBLE);
font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0)));
rt.applyFont(10, 19, font2);
XSSFFont font3 = wb.createFont();
font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255)));
rt.append(" Jumped over the lazy dog", font3);
cell.setCellValue(rt);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx");
wb.write(fileOut);
fileOut.close();
}
@Test
public void linksTest() throws IOException {
FileInputStream f =
new FileInputStream(
"C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\excelfiles\\datadrivenexcel.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(f);
XSSFSheet ws = wb.getSheet("sheet1");
Iterator<Row> row = ws.iterator();
row.next();
while (row.hasNext()) {
Row r = row.next();
String linkname = r.getCell(0).getStringCellValue();
try {
driver.findElement(By.linkText(linkname)).click();
String acturl = driver.getCurrentUrl();
r.createCell(2).setCellValue(acturl);
String expurl = r.getCell(1).getStringCellValue();
if (acturl.equals(expurl)) {
r.createCell(3).setCellValue("passsed");
} else {
r.createCell(3).setCellValue("failed");
}
driver.navigate().back();
} catch (Exception e) {
r.createCell(3).setCellValue("links not found");
}
}
FileOutputStream f1 =
new FileOutputStream(
"C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\resultsexcelfiles\\datadrivenexcel.xlsxlinks.xlsx");
wb.write(f1);
f1.close();
driver.quit();
}
public ArrayList<String> working_Section(
int SOOWDLocation, int EOOWDLocation, XSSFWorkbook workbook) {
ArrayList<String> workingSection = new ArrayList<String>();
XSSFRow row;
XSSFCell cell;
XSSFSheet Sheet = workbook.getSheet("Operation_Standard");
String genrateFormula;
for (int start = (SOOWDLocation - 1); start < EOOWDLocation; start++) {
try {
row = Sheet.getRow(start);
cell = row.getCell(2);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
genrateFormula = "Operation_Standard!C" + (row.getRowNum() + 1);
workingSection.add(genrateFormula);
break;
case Cell.CELL_TYPE_BLANK:
break;
default:
System.out.println("Error");
break;
}
} catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
return workingSection;
}
private XSSFSheet createTypeSheet(XSSFWorkbook workBook) {
XSSFSheet typeSheet = workBook.createSheet(PRODUCTTYPE_SHEETNAME);
List<ProductType> allTypes = productTypeDao.getAll();
Collections.sort(allTypes, new LftRgtTreeNodeComparator()); // 按左值排序。
List<AdjacencyNode<ProductType>> adjacencyNodes =
new LftRgtTreeMenuTool<ProductType>().toAdjacencyNode(allTypes);
int rownum = 0;
XSSFRow typeTitleRow = typeSheet.createRow(rownum++);
typeTitleRow.createCell(0, Cell.CELL_TYPE_STRING).setCellValue("类别名称");
typeTitleRow.createCell(1, Cell.CELL_TYPE_STRING).setCellValue("类别编号");
typeTitleRow.createCell(2, Cell.CELL_TYPE_STRING).setCellValue("父类编号");
for (AdjacencyNode<ProductType> productType : adjacencyNodes) {
XSSFRow row = typeSheet.createRow(rownum);
XSSFCell typeCell = row.createCell(0, Cell.CELL_TYPE_STRING);
typeCell.setCellValue(productType.getNode().getName());
XSSFCell itemCell = row.createCell(1, Cell.CELL_TYPE_STRING);
itemCell.setCellValue(productType.getNode().getItem());
if (productType.getParent() != null) {
XSSFCell parentItemCell = row.createCell(2, Cell.CELL_TYPE_STRING);
parentItemCell.setCellValue(productType.getParent().getItem());
}
rownum++;
}
return typeSheet;
}
private void processSheet(Element container, XSSFSheet sheet) {
Element table = htmlDocumentFacade.createTable();
int sIndex = sheet.getWorkbook().getSheetIndex(sheet);
String sId = "sheet_".concat(String.valueOf(sIndex));
table.setAttribute("id", sId);
table.setAttribute("border", "1");
table.setAttribute("cellpadding", "2");
table.setAttribute("cellspacing", "0");
table.setAttribute("style", "border-collapse: collapse;");
css.append("#")
.append(sId)
.append(" tr{height:")
.append(sheet.getDefaultRowHeightInPoints() / 28.34)
.append("cm}\n");
css.append("#")
.append(sId)
.append(" td{width:")
.append(sheet.getDefaultColumnWidth() * 0.21)
.append("cm}\n");
// cols
generateColumns(sheet, table);
// rows
Iterator<Row> rows = sheet.iterator();
while (rows.hasNext()) {
Row row = rows.next();
if (row instanceof XSSFRow) processRow(table, (XSSFRow) row, sheet);
}
container.appendChild(table);
}
public String getSheetInfo() {
StringBuilder info = new StringBuilder();
info.append("File: " + fileExcel.getAbsolutePath() + "\n");
info.append("Aktuelle Tabelle: " + xssfSheet.getSheetName() + "\n");
info.append(getColumnCountFromSheet() + " Spalten\n");
info.append(xssfSheet.getLastRowNum() + " Datenzeilen");
return info.toString();
}
/**
* 指定した行番号の<b>XSSFRow</b>を取得する。有効範囲外の行の場合は新規作成する。
*
* @param sheet <b>XSSFSheet</b>
* @param nRow 取得したい行の行番号
* @return <b>XSSFRow</b>
*/
public static XSSFRow getRowAnyway(XSSFSheet sheet, int nRow) {
assert sheet != null;
XSSFRow row = sheet.getRow(nRow);
if (row == null) {
row = sheet.createRow(nRow);
}
return row;
}
@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();
}
// returns true if data is set successfully else false
public boolean setCellData(
String sheetName, String colName, int rowNum, String data, String url) {
// System.out.println("setCellData setCellData******************");
try {
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
if (rowNum <= 0) return false;
int index = workBook.getSheetIndex(sheetName);
int colNum = -1;
if (index == -1) return false;
sheet = workBook.getSheetAt(index);
// System.out.println("A");
row = sheet.getRow(0);
for (int i = 0; i < row.getLastCellNum(); i++) {
// System.out.println(row.getCell(i).getStringCellValue().trim());
if (row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName)) colNum = i;
}
if (colNum == -1) return false;
sheet.autoSizeColumn(colNum); // ashish
row = sheet.getRow(rowNum - 1);
if (row == null) row = sheet.createRow(rowNum - 1);
cell = row.getCell(colNum);
if (cell == null) cell = row.createCell(colNum);
cell.setCellValue(data);
XSSFCreationHelper createHelper = workBook.getCreationHelper();
// cell style for hyperlinks
// by default hypelrinks are blue and underlined
CellStyle hlink_style = workBook.createCellStyle();
XSSFFont hlink_font = workBook.createFont();
hlink_font.setUnderline(XSSFFont.U_SINGLE);
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hlink_style.setFont(hlink_font);
// hlink_style.setWrapText(true);
XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
link.setAddress(url);
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* Récupère les informations de fusion des cellules dans la sheet source pour les appliquer à la
* sheet destination... Récupère toutes les zones merged dans la sheet source et regarde pour
* chacune d'elle si elle se trouve dans la current row que nous traitons. Si oui, retourne
* l'objet CellRangeAddress.
*
* @param sheet the sheet containing the data.
* @param rowNum the num of the row to copy.
* @param cellNum the num of the cell to copy.
* @return the CellRangeAddress created.
*/
public static CellRangeAddress getMergedRegion(XSSFSheet sheet, int rowNum, short cellNum) {
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress merged = sheet.getMergedRegion(i);
if (((CellRangeAddressBase) merged).isInRange(rowNum, cellNum)) {
return merged;
}
}
return null;
}
/**
* シートのクローンを行う
*
* @param workbook ワークブック
* @param insertSheetId シートの挿入先番号
* @param from コピー元のシート名
* @param to 追加するシート名
* @return 追加するシート
*/
public static XSSFSheet cloneSheet(
XSSFWorkbook workbook, int insertSheetId, String from, String to) {
assert workbook != null && StringUtil.isNotEmpty(from) && StringUtil.isNotEmpty(to);
int sheetId = workbook.getSheetIndex(from);
XSSFSheet clone = workbook.cloneSheet(sheetId);
String cloneName = clone.getSheetName();
workbook.setSheetOrder(cloneName, insertSheetId);
workbook.setSheetName(insertSheetId, to);
int newSheetId = workbook.getSheetIndex(to);
return workbook.getSheetAt(newSheetId);
}
public static void setCellStyleX(int row, int col, XSSFSheet sheet, XSSFCellStyle style) {
XSSFRow r = sheet.getRow(row);
if (null == r) {
r = sheet.createRow(row);
}
XSSFCell cell = r.getCell(col);
if (null == cell) {
cell = sheet.getRow(row).createCell(col);
}
cell.setCellStyle(style);
}
public int getColumnCount(String sheetname) {
XSSFSheet worksheet = _workbook.getSheet(sheetname);
Iterator<Cell> cellIterator = worksheet.getRow(0).cellIterator();
int colcount = 0;
while (cellIterator.hasNext()) {
colcount++;
cellIterator.next();
}
return colcount;
}
public static void setCellsX(int row, int col, Object colValue, XSSFFont font, XSSFSheet sheet) {
XSSFRow r = sheet.getRow(row);
if (null == r) {
r = sheet.createRow(row);
}
XSSFCell cell = r.getCell(col);
if (null == cell) {
cell = sheet.getRow(row).createCell(col);
}
setCellValueOfCnX(cell, colValue);
cell.getCellStyle().setFont(font);
}
public List<String> getColumnNames(String sheetname) {
XSSFSheet worksheet = _workbook.getSheet(sheetname);
Iterator<Cell> cellIterator = worksheet.getRow(0).cellIterator();
List<String> colnames = new ArrayList<String>();
while (cellIterator.hasNext()) {
XSSFCell cell = (XSSFCell) cellIterator.next();
colnames.add(cell.getStringCellValue());
}
return colnames;
}
public void run() {
try {
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
Iterator<Row> rowIterator2 = sheet.iterator();
if (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
String aux = "";
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
aux += cell.getStringCellValue().toUpperCase() + " ";
break;
}
}
System.out.println("Aux: " + aux);
if (aux.trim().equals(VALID_HEAD)) {
textArea.append("EXCEL VALIDO\n\n");
int size = 0;
rowIterator2.next();
while (rowIterator2.hasNext()) {
rowIterator2.next();
size++;
}
progressBar.setMaximum(size);
progressBar.setValue(0);
textArea.append("Clientes en fichero: " + size + "\n\n");
int num = 0;
while (rowIterator.hasNext()) {
num++;
textArea.append("--- CLIENT NUMBER " + num + " ---\n");
Row row2 = rowIterator.next();
processLine(row2.cellIterator());
progressBar.setValue(num);
Thread.sleep(1000);
}
} else {
textArea.append(
"El fichero excel seleccionado no es valido. "
+ "Selecciona un fichero excel con las columnas: \n"
+ VALID_HEAD);
}
}
fis.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
public void testAccessMethods() throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
XSSFChart chart = drawing.createChart(anchor);
XSSFCategoryAxis axis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
axis.setCrosses(AxisCrosses.AUTO_ZERO);
assertEquals(axis.getCrosses(), AxisCrosses.AUTO_ZERO);
assertEquals(chart.getAxis().size(), 1);
}
// returns the data from a cell
public String getCellData(String sheetName, String colName, int rowNum) {
try {
if (rowNum <= 0) return "";
int index = workBook.getSheetIndex(sheetName);
int col_Num = -1;
if (index == -1) return "";
sheet = workBook.getSheetAt(index);
row = sheet.getRow(0);
for (int i = 0; i < row.getLastCellNum(); i++) {
// System.out.println(row.getCell(i).getStringCellValue().trim());
if (row.getCell(i).getStringCellValue().trim().equals(colName.trim())) col_Num = i;
}
if (col_Num == -1) return "";
sheet = workBook.getSheetAt(index);
row = sheet.getRow(rowNum - 1);
if (row == null) return "";
cell = row.getCell(col_Num);
if (cell == null) return "";
// System.out.println(cell.getCellType());
if (cell.getCellType() == Cell.CELL_TYPE_STRING) return cell.getStringCellValue();
else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
|| cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
String cellText = String.valueOf(cell.getNumericCellValue());
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// format in form of M/D/YY
double d = cell.getNumericCellValue();
Calendar cal = Calendar.getInstance();
cal.setTime(HSSFDateUtil.getJavaDate(d));
cellText = (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
cellText =
cal.get(Calendar.DAY_OF_MONTH) + "/" + cal.get(Calendar.MONTH) + 1 + "/" + cellText;
// System.out.println(cellText);
}
return cellText;
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return "";
else return String.valueOf(cell.getBooleanCellValue());
} catch (Exception e) {
e.printStackTrace();
return "row " + rowNum + " or column " + colName + " does not exist in xls";
}
}
/**
* 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;
}
private void applyConditionalFormattingToCopiedRows(XSSFSheet sheet, int startRow) {
final int lastRow = sheet.getLastRowNum();
XSSFSheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();
// Go from end to start because we will be removing them
for (int i = formatting.getNumConditionalFormattings() - 1; i >= 0; i--) {
// Get conditional formatting
XSSFConditionalFormatting format = formatting.getConditionalFormattingAt(i);
// Apply conditional formatting to new range
CellRangeAddress[] ranges =
computeNewFormattingRanges(format.getFormattingRanges(), startRow, lastRow);
applyRulesToRanges(formatting, format, ranges);
// remove previous version of the conditional formatting
formatting.removeConditionalFormatting(i);
}
}
