/**
* 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;
}
public static void WriteCol(String QueryType, String num) throws IOException {
Map<String, Object[]> data = new HashMap<String, Object[]>();
List<String> sqlDML = new ArrayList<String>();
File myfile = new File("C://Users/Administrator/Documents/Heckathon/Result.xlsx");
FileInputStream fis = new FileInputStream(myfile);
XSSFWorkbook myworkbook = new XSSFWorkbook(fis);
XSSFSheet mysheet = myworkbook.getSheetAt(2);
data.put(num, new Object[] {QueryType, num});
Set<String> newRows = data.keySet();
int rownum = mysheet.getPhysicalNumberOfRows();
for (String Key : newRows) {
Row row = mysheet.createRow(rownum++);
Object[] objArr = data.get(Key);
int cellnum = 0;
for (Object obj : objArr) {
Cell cell = row.createCell(cellnum++);
cell.setCellValue((String) obj);
}
}
FileOutputStream os = new FileOutputStream(myfile);
myworkbook.write(os);
System.out.println("Record Entered");
}
/**
* 读取2007-2013格式
*
* @param filePath 文件路径
* @return
* @throws java.io.IOException
*/
@SuppressWarnings("rawtypes")
public static List<Map> readExcel2007(String filePath) throws IOException {
List<Map> valueList = new ArrayList<Map>();
FileInputStream fis = null;
try {
fis = new FileInputStream(filePath);
XSSFWorkbook xwb = new XSSFWorkbook(fis); // 构造 XSSFWorkbook 对象，strPath 传入文件路径
XSSFSheet sheet = xwb.getSheetAt(0); // 读取第一章表格内容
// 定义 row、cell
XSSFRow row;
// 循环输出表格中的第一行内容 表头
Map<Integer, String> keys = new HashMap<Integer, String>();
row = sheet.getRow(0);
if (row != null) {
// System.out.println("j = row.getFirstCellNum()::"+row.getFirstCellNum());
// System.out.println("row.getPhysicalNumberOfCells()::"+row.getPhysicalNumberOfCells());
for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
// 通过 row.getCell(j).toString() 获取单元格内容，
if (row.getCell(j) != null) {
if (!row.getCell(j).toString().isEmpty()) {
keys.put(j, row.getCell(j).toString());
}
} else {
keys.put(j, "K-R1C" + j + "E");
}
}
}
// 循环输出表格中的从第二行开始内容
for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row != null) {
boolean isValidRow = false;
Map<String, Object> val = new HashMap<String, Object>();
for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
XSSFCell cell = row.getCell(j);
if (cell != null) {
String cellValue = null;
if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
cellValue =
new DataFormatter()
.formatRawCellContents(
cell.getNumericCellValue(), 0, "yyyy-MM-dd HH:mm:ss");
} else {
cellValue = String.valueOf(cell.getNumericCellValue());
}
} else {
cellValue = cell.toString();
}
if (cellValue != null && cellValue.trim().length() <= 0) {
cellValue = null;
}
val.put(keys.get(j), cellValue);
if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) {
isValidRow = true;
}
}
}
// 第I行所有的列数据读取完毕，放入valuelist
if (isValidRow) {
valueList.add(val);
}
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
fis.close();
}
return valueList;
}
