public int getCcIndexForCell(Cell cell, ArrayList<ConnexComposant> ccs) {
for (int i = 0; i < ccs.size(); i++) {
if (cell.getRow() >= ccs.get(i).getStartY()
&& cell.getRow() <= ccs.get(i).getEndY()
&& cell.getColumn() >= ccs.get(i).getStartX()
&& cell.getColumn() <= ccs.get(i).getEndX()) {
return i;
}
}
return -1;
}
public String[][] getTableArray() throws Exception {
String[][] tabArray = null;
Workbook workbook = Workbook.getWorkbook(new File(inputFile));
Sheet sheet = workbook.getSheet(sheetName);
int startRow, startCol, endRow, endCol, ci, cj;
Cell tableStart = sheet.findCell(tableName);
startRow = tableStart.getRow();
startCol = tableStart.getColumn();
Cell tableEnd = sheet.findCell(tableName, startCol + 1, startRow + 1, 400, 64000, false);
// Cell tableEnd= sheet.findCell(tableName, startCol+1,startRow+1, 100, 64000, false);
endRow = tableEnd.getRow();
endCol = tableEnd.getColumn();
System.out.println(
"startRow="
+ startRow
+ ", endRow="
+ endRow
+ ", "
+ "startCol="
+ startCol
+ ", endCol="
+ endCol);
tabArray = new String[endRow - startRow - 1][endCol - startCol - 1];
ci = 0;
for (int i = startRow + 1; i < endRow; i++, ci++) {
cj = 0;
for (int j = startCol + 1; j < endCol; j++, cj++) {
tabArray[ci][cj] = sheet.getCell(j, i).getContents();
}
}
return (tabArray);
}
/**
* Récupère la liste des étudiants dans un xls
*
* @param uri : l'uri du fichier xls
* @return la liste remplie
* @author Foulon Alain
*/
public static GestionEtudiants getValsFromXls(String uri) throws BiffException, IOException {
GestionEtudiants liste = new GestionEtudiants();
try {
// Récupère le fichier
File fich = new File(uri);
WorkbookSettings workSet = new WorkbookSettings(); // config de fichier .xls
workSet.setLocale(new Locale("fr", "FR")); // vars locales
Workbook workbook =
Workbook.getWorkbook(fich, workSet); // ouvre un fichier existant en fct des settings
Sheet feuille = workbook.getSheet(0); // ouvre la première feuille du fichier
Cell cellNom = feuille.findCell("NOM"); // trouve la cellule "NOM" dans la feuille
// soit : REGEX sur "nom, Nom, noms, NOM, NOMS..."
// soit : fct qui teste les possibilités; (note: la CELL est null si non trouvée)
int colNom = cellNom.getColumn(); // colonne noms
int colPrenom = colNom + 1; // colonne prénoms
int firstRow = cellNom.getRow() + 1; // premiere ligne
int lastRow = feuille.getRows(); // derniere ligne
// boucle de remplissage
for (int i = firstRow, j = 0; i < lastRow; i++) {
// je récupère les infors
String nom = feuille.getCell(colNom, i).getContents();
String prenom = feuille.getCell(colPrenom, i).getContents();
// j'ajoute l'étudiant dans la liste
j++;
liste.add(new Etudiant(j, nom, prenom));
}
} catch (IOException ioe) {
System.out.println("Erreur : " + ioe); // Erreur lors de l'ouverture du fichier Excel
}
// je retourne la liste finie
return liste;
}
/**
* Check all the merged cells for borders. If the merge record has borders, then we need to rejig
* the cell formats to take account of this. This is called by the write method of the
* WritableWorkbookImpl, so that any new XFRecords that are created may be written out with the
* others
*/
void checkMergedBorders() {
Range[] mcells = mergedCells.getMergedCells();
ArrayList borderFormats = new ArrayList();
for (int mci = 0; mci < mcells.length; mci++) {
Range range = mcells[mci];
Cell topLeft = range.getTopLeft();
XFRecord tlformat = (XFRecord) topLeft.getCellFormat();
if (tlformat != null && tlformat.hasBorders() == true && !tlformat.isRead()) {
try {
CellXFRecord cf1 = new CellXFRecord(tlformat);
Cell bottomRight = range.getBottomRight();
cf1.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf1.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
cf1.setBorder(
Border.TOP, tlformat.getBorderLine(Border.TOP), tlformat.getBorderColour(Border.TOP));
if (topLeft.getRow() == bottomRight.getRow()) {
cf1.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
}
if (topLeft.getColumn() == bottomRight.getColumn()) {
cf1.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
}
int index = borderFormats.indexOf(cf1);
if (index != -1) {
cf1 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf1);
}
((WritableCell) topLeft).setCellFormat(cf1);
// Handle the bottom left corner
if (bottomRight.getRow() > topLeft.getRow()) {
// Handle the corner cell
if (bottomRight.getColumn() != topLeft.getColumn()) {
CellXFRecord cf2 = new CellXFRecord(tlformat);
cf2.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf2.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
cf2.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
index = borderFormats.indexOf(cf2);
if (index != -1) {
cf2 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf2);
}
sheet.addCell(new Blank(topLeft.getColumn(), bottomRight.getRow(), cf2));
}
// Handle the cells down the left hand side (and along the
// right too, if necessary)
for (int i = topLeft.getRow() + 1; i < bottomRight.getRow(); i++) {
CellXFRecord cf3 = new CellXFRecord(tlformat);
cf3.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf3.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
if (topLeft.getColumn() == bottomRight.getColumn()) {
cf3.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
}
index = borderFormats.indexOf(cf3);
if (index != -1) {
cf3 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf3);
}
sheet.addCell(new Blank(topLeft.getColumn(), i, cf3));
}
}
// Handle the top right corner
if (bottomRight.getColumn() > topLeft.getColumn()) {
if (bottomRight.getRow() != topLeft.getRow()) {
// Handle the corner cell
CellXFRecord cf6 = new CellXFRecord(tlformat);
cf6.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf6.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
cf6.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
index = borderFormats.indexOf(cf6);
if (index != -1) {
cf6 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf6);
}
sheet.addCell(new Blank(bottomRight.getColumn(), topLeft.getRow(), cf6));
}
// Handle the cells along the right
for (int i = topLeft.getRow() + 1; i < bottomRight.getRow(); i++) {
CellXFRecord cf7 = new CellXFRecord(tlformat);
cf7.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf7.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
index = borderFormats.indexOf(cf7);
if (index != -1) {
cf7 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf7);
}
sheet.addCell(new Blank(bottomRight.getColumn(), i, cf7));
}
// Handle the cells along the top, and along the bottom too
for (int i = topLeft.getColumn() + 1; i < bottomRight.getColumn(); i++) {
CellXFRecord cf8 = new CellXFRecord(tlformat);
cf8.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf8.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
if (topLeft.getRow() == bottomRight.getRow()) {
cf8.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
}
index = borderFormats.indexOf(cf8);
if (index != -1) {
cf8 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf8);
}
sheet.addCell(new Blank(i, topLeft.getRow(), cf8));
}
}
// Handle the bottom right corner
if (bottomRight.getColumn() > topLeft.getColumn()
|| bottomRight.getRow() > topLeft.getRow()) {
// Handle the corner cell
CellXFRecord cf4 = new CellXFRecord(tlformat);
cf4.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf4.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
cf4.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
if (bottomRight.getRow() == topLeft.getRow()) {
cf4.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
}
if (bottomRight.getColumn() == topLeft.getColumn()) {
cf4.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
}
index = borderFormats.indexOf(cf4);
if (index != -1) {
cf4 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf4);
}
sheet.addCell(new Blank(bottomRight.getColumn(), bottomRight.getRow(), cf4));
// Handle the cells along the bottom (and along the top
// as well, if appropriate)
for (int i = topLeft.getColumn() + 1; i < bottomRight.getColumn(); i++) {
CellXFRecord cf5 = new CellXFRecord(tlformat);
cf5.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf5.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
if (topLeft.getRow() == bottomRight.getRow()) {
cf5.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
}
index = borderFormats.indexOf(cf5);
if (index != -1) {
cf5 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf5);
}
sheet.addCell(new Blank(i, bottomRight.getRow(), cf5));
}
}
} catch (WriteException e) {
// just log e.toString(), not the whole stack trace
logger.warn(e.toString());
}
}
}
}
/**
* @param is 要导入Excel的输入流
* @param sheetName 导入的工作表名称
* @param entityClass List中对象的类型（Excel中的每一行都要转化为该类型的对象）
* @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 例：{id=编号}
* @param uniqueFields 指定业务主键组合（即复合主键），这些列的组合不能重复
* @return List
* @throws ExcelException @Description 将Excel转化成实体对象List
*/
public static <T> List<T> excelToList(
InputStream is,
String sheetName,
Class<T> entityClass,
LinkedHashMap<String, String> fieldMap,
String[] uniqueFields)
throws ExcelException {
// 定义要返回的list
List<T> resultList = new ArrayList<T>();
try {
// 根据excel数据源创建WorkBook
Workbook wb = Workbook.getWorkbook(is);
// 获取工作表
Sheet sheet = wb.getSheet(sheetName);
// 获取工作表的有效行数
int realRows = 0;
for (int i = 0; i < sheet.getRows(); i++) {
int nullCols = 0;
for (int j = 0; j < sheet.getColumns(); j++) {
Cell CurrentCell = sheet.getCell(j, i);
if (CurrentCell == null || "".equals(CurrentCell.getContents().toString())) {
nullCols++;
}
}
if (nullCols == sheet.getColumns()) {
break;
} else {
realRows++;
}
}
// 如果Excel中没有任何数据则提示错误信息
if (realRows <= 1) {
throw new ExcelException("Excel文件中没有任何数据");
}
Cell[] firstRow = sheet.getRow(0);
String[] excelFieldNames = new String[firstRow.length];
// 获取Excel的列名
for (int i = 0; i < firstRow.length; i++) {
excelFieldNames[i] = firstRow[i].getContents().toString().trim();
}
// 判断需要的字段在Excel中是否都存在
boolean isExist = true;
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fieldMap.values()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
break;
}
}
// 如果有列名不存在或不匹配，则抛出异常并提示错误
if (!isExist) {
throw new ExcelException("Excel中缺少必要的字段，或字段名称有误");
}
// 将列名和列号放入Map中，这样通过列名就可以拿到列号
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
for (int i = 0; i < excelFieldNames.length; i++) {
colMap.put(excelFieldNames[i], firstRow[i].getColumn());
}
// 判断是否有重复行
// 1.获取uniqueFields指定的列
Cell[][] uniqueCells = new Cell[uniqueFields.length][];
for (int i = 0; i < uniqueFields.length; i++) {
int col = colMap.get(uniqueFields[i]);
uniqueCells[i] = sheet.getColumn(col);
}
// 2.从指定列中寻找重复行
for (int i = 1; i < realRows; i++) {
int nullCols = 0;
int length = uniqueFields.length;
for (int j = 0; j < length; j++) {
Cell currentCell = uniqueCells[j][i];
String currentContent = currentCell.getContents().toString().trim();
Cell sameCell =
sheet.findCell(
currentContent,
currentCell.getColumn(),
currentCell.getRow() + 1,
currentCell.getColumn(),
uniqueCells[j][realRows - 1].getRow(),
true);
if (sameCell != null) {
nullCols++;
}
}
// 复合主键，意味着这些列的组合不能重复，
// 只有当所有的列都有重复的时候，才被认为是有重复行
if (nullCols == length) {
throw new Exception("Excel中有重复行，请检查");
}
}
// 将sheet转换为list
for (int i = 1; i < realRows; i++) {
// 新建要转换的对象
T entity = entityClass.newInstance();
// 给对象中的字段赋值
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
// 获取英文字段名
String enNormalName = entry.getKey();
// 获取中文字段名
String cnNormalName = entry.getValue();
// 根据中文字段名获取列号
int col = colMap.get(cnNormalName);
// 获取当前单元格中的内容
String content = sheet.getCell(col, i).getContents().toString().trim();
// 给对象赋值
setFieldValueByName(enNormalName, content, entity);
}
resultList.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
// 如果是ExcelException,则直接抛出
if (e instanceof ExcelException) {
throw (ExcelException) e;
} else {
// 否则将其包装成ExcelException,再将其抛出
throw new ExcelException("导入ExceL失败");
}
}
return resultList;
}