/**
* 取得当前sheet的sheet名
*
* @return
*/
public String getSheetName() {
if (readOnlyWBook == true) {
return currentSheet.getName();
} else {
return wrCurrentSheet.getName();
}
}
private List<String> getInvestigationNameFromExcel(File excelFile) throws Exception {
File tmpInvestigation =
new File(System.getProperty("java.io.tmpdir") + File.separator + "tmpInvestigation.txt");
if (tmpInvestigation.exists()) {
boolean deleteSuccess = tmpInvestigation.delete();
if (!deleteSuccess) {
throw new Exception("Deletion of tmp file 'tmpInvestigation.txt' failed, cannot proceed.");
}
}
boolean createSuccess = tmpInvestigation.createNewFile();
if (!createSuccess) {
throw new Exception("Creation of tmp file 'tmpInvestigation.txt' failed, cannot proceed.");
}
Workbook workbook = Workbook.getWorkbook(excelFile);
for (Sheet sheet : workbook.getSheets()) {
if (sheet.getName().toLowerCase().equals("investigation")) {
writeSheetToFile(sheet, tmpInvestigation);
}
}
List<String> names = XgapCommonImport.getInvestigationNameFromFile(tmpInvestigation);
return names;
}
/** Writes out the workbook data as XML, without formatting information */
private void writeXML() throws IOException {
try {
OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
BufferedWriter bw = new BufferedWriter(osw);
bw.write("<?xml version=\"1.0\" ?>");
bw.newLine();
bw.write("<!DOCTYPE workbook SYSTEM \"workbook.dtd\">");
bw.newLine();
bw.newLine();
bw.write("<workbook>");
bw.newLine();
for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++) {
Sheet s = workbook.getSheet(sheet);
bw.write(" <sheet>");
bw.newLine();
bw.write(" <name><![CDATA[" + s.getName() + "]]></name>");
bw.newLine();
Cell[] row = null;
for (int i = 0; i < s.getRows(); i++) {
bw.write(" <row number=\"" + i + "\">");
bw.newLine();
row = s.getRow(i);
for (int j = 0; j < row.length; j++) {
if (row[j].getType() != CellType.EMPTY) {
bw.write(" <col number=\"" + j + "\">");
bw.write("<![CDATA[" + row[j].getContents() + "]]>");
bw.write("</col>");
bw.newLine();
}
}
bw.write(" </row>");
bw.newLine();
}
bw.write(" </sheet>");
bw.newLine();
}
bw.write("</workbook>");
bw.newLine();
bw.flush();
bw.close();
} catch (UnsupportedEncodingException e) {
System.err.println(e.toString());
}
}
/**
* Constructor
*
* @param w The workbook to interrogate
* @param out The output stream to which the CSV values are written
* @param encoding The encoding used by the output stream. Null or unrecognized values cause the
* encoding to default to UTF8
* @param hide Suppresses hidden cells
* @exception java.io.IOException
*/
public CSV(Workbook w, OutputStream out, String encoding, boolean hide) throws IOException {
if (encoding == null || !encoding.equals("UnicodeBig")) {
encoding = "UTF8";
}
try {
OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
BufferedWriter bw = new BufferedWriter(osw);
for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++) {
Sheet s = w.getSheet(sheet);
if (!(hide && s.getSettings().isHidden())) {
bw.write("*** " + s.getName() + " ****");
bw.newLine();
Cell[] row = null;
for (int i = 0; i < s.getRows(); i++) {
row = s.getRow(i);
if (row.length > 0) {
if (!(hide && row[0].isHidden())) {
bw.write(row[0].getContents());
// Java 1.4 code to handle embedded commas
// bw.write("\"" +
// row[0].getContents().replaceAll("\"","\"\"")
// + "\"");
}
for (int j = 1; j < row.length; j++) {
bw.write(',');
if (!(hide && row[j].isHidden())) {
bw.write(row[j].getContents());
// Java 1.4 code to handle embedded quotes
// bw.write("\"" +
// row[j].getContents().replaceAll("\"","\"\"")
// + "\"");
}
}
}
bw.newLine();
}
}
}
bw.flush();
bw.close();
} catch (UnsupportedEncodingException e) {
System.err.println(e.toString());
}
}
public static List<Map<String, String>> getXlsUserInfoData(String path) {
try {
File xlsFile = new File(path);
Workbook book = Workbook.getWorkbook(xlsFile);
Sheet[] sheet = book.getSheets();
if (sheet.length > 0) {
Sheet page = sheet[0];
int columns = page.getColumns();
System.out.println(columns);
int rows = page.getRows();
System.out.println(page.getName());
if (rows < 2) return null;
int columnsCount = 0;
List<String> titleList = new ArrayList<String>();
for (int i = 0; i < columns; i++) {
// 列 行
Cell cell = page.getCell(i, 0);
String key = cell.getContents();
if (key == null || key.equals("")) {
continue;
}
columnsCount++;
logger.debug(key);
titleList.add(key);
}
columns = columnsCount;
List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
for (int i = 1; i < rows; i++) {
logger.info("ii:" + i);
Map<String, String> dataMap = new HashMap<String, String>();
for (int j = 0; j < columns; j++) {
String key = titleList.get(j);
String value = page.getCell(j, i).getContents();
dataMap.put(key, value);
logger.debug(key + " " + value);
}
dataList.add(dataMap);
}
logger.debug("xls length:" + dataList.size());
return dataList;
}
} catch (Exception ex) {
ex.printStackTrace();
}
return null;
}
/**
* Gets the sheet index in this workbook. Used when importing a sheet
*
* @param sheet the sheet
* @return the 0-based sheet index, or -1 if it is not found
*/
public int getIndex(Sheet sheet) {
String name = sheet.getName();
int index = -1;
int pos = 0;
for (Iterator i = boundsheets.iterator(); i.hasNext() && index == -1; ) {
BoundsheetRecord br = (BoundsheetRecord) i.next();
if (br.getName().equals(name)) {
index = pos;
} else {
pos++;
}
}
return index;
}
/** Writes out the workbook data as XML, with formatting information */
private void writeFormattedXML() throws IOException {
try {
OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
BufferedWriter bw = new BufferedWriter(osw);
bw.write("<?xml version=\"1.0\" ?>");
bw.newLine();
bw.write("<!DOCTYPE workbook SYSTEM \"formatworkbook.dtd\">");
bw.newLine();
bw.newLine();
bw.write("<workbook>");
bw.newLine();
for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++) {
Sheet s = workbook.getSheet(sheet);
bw.write(" <sheet>");
bw.newLine();
bw.write(" <name><![CDATA[" + s.getName() + "]]></name>");
bw.newLine();
Cell[] row = null;
CellFormat format = null;
Font font = null;
for (int i = 0; i < s.getRows(); i++) {
bw.write(" <row number=\"" + i + "\">");
bw.newLine();
row = s.getRow(i);
for (int j = 0; j < row.length; j++) {
// Remember that empty cells can contain format information
if ((row[j].getType() != CellType.EMPTY) || (row[j].getCellFormat() != null)) {
format = row[j].getCellFormat();
bw.write(" <col number=\"" + j + "\">");
bw.newLine();
bw.write(" <data>");
bw.write("<![CDATA[" + row[j].getContents() + "]]>");
bw.write("</data>");
bw.newLine();
if (row[j].getCellFormat() != null) {
bw.write(" <format wrap=\"" + format.getWrap() + "\"");
bw.newLine();
bw.write(
" align=\"" + format.getAlignment().getDescription() + "\"");
bw.newLine();
bw.write(
" valign=\""
+ format.getVerticalAlignment().getDescription()
+ "\"");
bw.newLine();
bw.write(
" orientation=\""
+ format.getOrientation().getDescription()
+ "\"");
bw.write(">");
bw.newLine();
// The font information
font = format.getFont();
bw.write(" <font name=\"" + font.getName() + "\"");
bw.newLine();
bw.write(" point_size=\"" + font.getPointSize() + "\"");
bw.newLine();
bw.write(" bold_weight=\"" + font.getBoldWeight() + "\"");
bw.newLine();
bw.write(" italic=\"" + font.isItalic() + "\"");
bw.newLine();
bw.write(
" underline=\""
+ font.getUnderlineStyle().getDescription()
+ "\"");
bw.newLine();
bw.write(" colour=\"" + font.getColour().getDescription() + "\"");
bw.newLine();
bw.write(
" script=\"" + font.getScriptStyle().getDescription() + "\"");
bw.write(" />");
bw.newLine();
// The cell background information
if (format.getBackgroundColour() != Colour.DEFAULT_BACKGROUND
|| format.getPattern() != Pattern.NONE) {
bw.write(
" <background colour=\""
+ format.getBackgroundColour().getDescription()
+ "\"");
bw.newLine();
bw.write(
" pattern=\""
+ format.getPattern().getDescription()
+ "\"");
bw.write(" />");
bw.newLine();
}
// The cell border, if it has one
if (format.getBorder(Border.TOP) != BorderLineStyle.NONE
|| format.getBorder(Border.BOTTOM) != BorderLineStyle.NONE
|| format.getBorder(Border.LEFT) != BorderLineStyle.NONE
|| format.getBorder(Border.RIGHT) != BorderLineStyle.NONE) {
bw.write(
" <border top=\""
+ format.getBorder(Border.TOP).getDescription()
+ "\"");
bw.newLine();
bw.write(
" bottom=\""
+ format.getBorder(Border.BOTTOM).getDescription()
+ "\"");
bw.newLine();
bw.write(
" left=\""
+ format.getBorder(Border.LEFT).getDescription()
+ "\"");
bw.newLine();
bw.write(
" right=\""
+ format.getBorder(Border.RIGHT).getDescription()
+ "\"");
bw.write(" />");
bw.newLine();
}
// The cell number/date format
if (!format.getFormat().getFormatString().equals("")) {
bw.write(" <format_string string=\"");
bw.write(format.getFormat().getFormatString());
bw.write("\" />");
bw.newLine();
}
bw.write(" </format>");
bw.newLine();
}
bw.write(" </col>");
bw.newLine();
}
}
bw.write(" </row>");
bw.newLine();
}
bw.write(" </sheet>");
bw.newLine();
}
bw.write("</workbook>");
bw.newLine();
bw.flush();
bw.close();
} catch (UnsupportedEncodingException e) {
System.err.println(e.toString());
}
}