文件上传、POI
来源:互联网 发布:俯卧撑 知乎 编辑:程序博客网 时间:2024/05/19 23:56
数据导出:
getResponse().setHeader("Content-Disposition","attachment; filename=ce_remove_mml_list.csv");
getResponse().setContentType("application/vnd.ms-csv;CHARSET=gbk");
getResponse().setCharacterEncoding("gbk");
Writer sw = getResponse().getWriter();
CSVWriter writer = new CSVWriter(sw);
final String[] header = new String[]{
"更新批次标识","BSC名称","基站编号","生成的MML命令"
,"CE增量","SPUO框号","SPUO槽号"
,"DO当前配置CE数","DO周最大使用CE数","DO准备修改的CE数"
,"DO物理最大CE数"
,"执行时间","类型"
};
writer.writeNext(header);
writer.writeNext(csvValue);
if(count % 100 == 0){
writer.flush();
}
count++;
}
writer.close();
数据导入:
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
try {
File tempfile = new File(System.getProperty("java.io.tmpdir"));//采用系统临时文件目录
DiskFileItemFactory diskFileItemFactory = new DiskFileItemFactory();
diskFileItemFactory.setSizeThreshold(4096); // 设置缓冲区大小,这里是4kb
diskFileItemFactory.setRepository(tempfile); //设置缓冲区目录
ServletFileUpload fu = new ServletFileUpload(diskFileItemFactory);
fu.setSizeMax(41943040);
List fileItems = fu.parseRequest(request);
Iterator i = fileItems.iterator();
while (i.hasNext()) {
FileItem fi = (FileItem) i.next();
if (!fi.isFormField()){
String fileName = fi.getName();
if (fileName != null) {
namepath = uploadPath+"/"+ fi.getName();
File f = new File(namepath);
fi.write(f);
}
}else{//不是的话
request.setAttribute(fi.getFieldName(), fi.getString());//这里就是获取他的元素名称和值放在request.Attribute中,这样当你要使用的时候直接用 request.getAttribute("form表单元素名称") 就行了
}
}
File file = new File(namepath);
String sqlsStr = FileUtils.readFileToString(file, "utf-8");
String[] sqls = DbHelper.splitSqls(sqlsStr);
Map<String, Database> databases = (Map<String, Database>) request.getSession().getServletContext().getAttribute("databases");
String dbId = (String) request.getAttribute("dbId");
Database db = databases.get(dbId);
Session session = db.openSession();
session.executeBatch(sqls);
session.close();
System.out.println("upload succeed");
String msg = "导入成功";
request.setAttribute("msg", msg);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage(),e);
}
request.getRequestDispatcher("/database/batchImportSql.jsp").forward(request, response);
}
throws IOException, ServletException {
try {
File tempfile = new File(System.getProperty("java.io.tmpdir"));//采用系统临时文件目录
DiskFileItemFactory diskFileItemFactory = new DiskFileItemFactory();
diskFileItemFactory.setSizeThreshold(4096); // 设置缓冲区大小,这里是4kb
diskFileItemFactory.setRepository(tempfile); //设置缓冲区目录
ServletFileUpload fu = new ServletFileUpload(diskFileItemFactory);
fu.setSizeMax(41943040);
List fileItems = fu.parseRequest(request);
Iterator i = fileItems.iterator();
while (i.hasNext()) {
FileItem fi = (FileItem) i.next();
if (!fi.isFormField()){
String fileName = fi.getName();
if (fileName != null) {
namepath = uploadPath+"/"+ fi.getName();
File f = new File(namepath);
fi.write(f);
}
}else{//不是的话
request.setAttribute(fi.getFieldName(), fi.getString());//这里就是获取他的元素名称和值放在request.Attribute中,这样当你要使用的时候直接用 request.getAttribute("form表单元素名称") 就行了
}
}
File file = new File(namepath);
String sqlsStr = FileUtils.readFileToString(file, "utf-8");
String[] sqls = DbHelper.splitSqls(sqlsStr);
Map<String, Database> databases = (Map<String, Database>) request.getSession().getServletContext().getAttribute("databases");
String dbId = (String) request.getAttribute("dbId");
Database db = databases.get(dbId);
Session session = db.openSession();
session.executeBatch(sqls);
session.close();
System.out.println("upload succeed");
String msg = "导入成功";
request.setAttribute("msg", msg);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage(),e);
}
request.getRequestDispatcher("/database/batchImportSql.jsp").forward(request, response);
}
EXCEL.java
package com.yuqiaotech.report;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* Excel操作类。
* 本类提供了简单的获取指定cell给指定cell添加内容的方法。
*
* 注意遵照java的习惯,row从0开始算,不过excel对单元格标注的习惯是从1开始算,这个需要留意。
*
*/
public class Excel {
private static final Log log = LogFactory.getLog(Excel.class);
HSSFWorkbook wb;
private final static SimpleDateFormat fullTimeFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private final static SimpleDateFormat fullDateFmt = new SimpleDateFormat("yyyy-MM-dd");
public Excel(InputStream is){
POIFSFileSystem fs;
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (FileNotFoundException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public Excel(){
wb = new HSSFWorkbook();
}
public Excel(String filePath){
POIFSFileSystem fs;
try {
fs = new POIFSFileSystem(new FileInputStream(filePath));
wb = new HSSFWorkbook(fs);
} catch (FileNotFoundException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
class Point{
public Point(String cellPositionStr){
char[] chars = cellPositionStr.toCharArray();
int i = 0;
for (; i < chars.length; i++) {
if(Character.isDigit(chars[i])){
break;
}
}
row = Integer.parseInt(cellPositionStr.substring(i))-1;
col = cellNumStr2Int(cellPositionStr.substring(0, i));
}
public Point(String colStr,int row){
col = cellNumStr2Int(colStr);
this.row = row;
}
int row;
int col;
}
/**
* 获取sheet数目。
* @return
*/
public int getSheetCnt(){
return this.wb.getNumberOfSheets();
}
/**
* 给Excel中的某个sheet的某个单元格赋值。
*
* @param cellPositionStr 位置参数如A12表示A列,12行。
* @param sheetNo
* @param v
* @return
*/
public HSSFCell setCellValue(String cellPositionStr, int sheetNo, Object v){
Point p = new Point(cellPositionStr);
return setCellValue( p, sheetNo, v);
}
public HSSFCell setCellValue(String cellPositionStr, Object v){
Point p = new Point(cellPositionStr);
return setCellValue( p, 0, v);
}
/**
* 给Excel中的某个sheet的某个单元格赋值。
*
* @param colNumStr 哪一列
* @param rowNum
* @param sheetNo
* @param v
* @return
*/
public HSSFCell setCellValue(String colNumStr, int rowNum, int sheetNo, Object v){
Point p = new Point(colNumStr,rowNum);
return setCellValue( p, sheetNo, v);
}
public HSSFCell setCellValue(Point p, int sheetNo, Object v){
return setCellValue( p.col, p.row, sheetNo, v);
}
/**
* 给Excel中的某个sheet的某个单元格赋值。
*
* @param colNum
* @param rowNum 从0开始。
* @param sheetNo 从0开始。
* @param v
* @return
*/
public HSSFCell setCellValue(int colNum, int rowNum, int sheetNo, Object v){
HSSFCell cell = this.getCell(colNum, rowNum, sheetNo);
if(v == null){
cell.setCellValue(new HSSFRichTextString(""));//TODO 添加的值是以单元格格式为准,还是以数据类型为准?
return cell;
}
if(v.getClass() == Boolean.class){
cell.setCellValue((Boolean)v);
}else if(v.getClass() == Integer.class){
cell.setCellValue((Integer)v);
}else if(v.getClass() == Double.class){
cell.setCellValue((Double)v);
}else if(v.getClass() == Float.class){
cell.setCellValue((Float)v);
}else if(v.getClass() == BigDecimal.class){
cell.setCellValue(((BigDecimal)v).doubleValue());
}else if(v instanceof Date){
cell.setCellValue(new HSSFRichTextString(fullTimeFmt.format((Date)v)));//TODO 权益之计
}else if(v instanceof oracle.sql.TIMESTAMP){
oracle.sql.TIMESTAMP vx = (oracle.sql.TIMESTAMP)v;
try {
cell.setCellValue(new HSSFRichTextString(fullTimeFmt.format(vx.timestampValue())));
} catch (SQLException e) {
throw new RuntimeException(e.getMessage(),e);
}
}else if(v.getClass() == String.class){
String cellStr = (String)v;
if( cellStr.length() >= 32766 ){
cellStr = cellStr.substring(0, 32765);
if(log.isWarnEnabled()){
log.warn("str has been substring(0, 32765) for cell ("+sheetNo+","+rowNum+","+colNum+")");
}
}
cell.setCellValue(new HSSFRichTextString( cellStr ) );
}else{
cell.setCellValue(new HSSFRichTextString(v.toString()));
}
return cell;
}
/**
* 根据指定行列和sheet获取单元。
*
* @param rowNum
* @param cellNum
* @param sheetNo
* @return
*/
public HSSFCell getCell(int colNum,int rowNum,int sheetNo){
HSSFRow row = getRow(rowNum,sheetNo);
HSSFCell cell = row.getCell(colNum);
if(cell == null)cell = row.createCell(colNum);
return cell;
}
public HSSFCell getCell(String colNumStr, int rowNum, int sheetNo){
int colNum = cellNumStr2Int(colNumStr);
return getCell(colNum,rowNum,sheetNo);
}
public HSSFCell getCell(String cellPositionStr,int sheetNo){
Point p = new Point(cellPositionStr);
return getCell(p.col,p.row,sheetNo);
}
public HSSFSheet getSheetAt(int num){
return wb.getSheetAt(num);
}
/**
* 合并。
* @param sheetNum
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
*/
public void addMergedRegion(int sheetNum, int firstRow, int lastRow, int firstCol, int lastCol ){
HSSFSheet sheet = getSheetAt(sheetNum);
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));//指定合并区域
}
/**
* 获取某一行。
*
* @param rowNum
* @param sheetNo
* @return
*/
public HSSFRow getRow(int rowNum,int sheetNo){
HSSFSheet sheet = null;
if(sheetNo >= wb.getNumberOfSheets()){
sheet = wb.createSheet("sheet-"+sheetNo);
}else{
sheet = wb.getSheetAt(sheetNo);
}
HSSFRow row = sheet.getRow(rowNum);
if(row == null)
row = sheet.createRow(rowNum);
return row;
}
/**
* 将列的名称转换为数字。
*
* @param cellNumStr
* @return
*/
private static int cellNumStr2Int(String cellNumStr){
cellNumStr = cellNumStr.toLowerCase();
int cellNum = 0;
char[] chars = cellNumStr.toCharArray();
int j = 0;
for(int i = chars.length-1; i >=0 ; i--){
cellNum += (chars[i]-'a'+1)*Math.pow(26, j);
j++;
}
return cellNum-1;
}
public static String cellNumIntToStr(int colNum){
String colName = "";
// for(int i=0;i<colNum/26+1;i++){
// char c = (char)(colNum%26+'a'-i);
// colName = c + colName;
// colNum = colNum/26;
// }
//colNum++;
do{
char c = (char)(colNum%26+'A');
colName = c + colName;
colNum = colNum/26-1;
}while(colNum >= 0);
return colName;
}
/**
* 将excel写入到某个输出流中。
*
* @param out
*/
public void write(OutputStream out){
try {
wb.write(out);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public void save(String filePath){
try {
OutputStream out = new FileOutputStream(new File(filePath));
write(out);
out.flush();
out.close();
} catch (FileNotFoundException e) {
throw new RuntimeException(e.getMessage(),e);
} catch (IOException e) {
throw new RuntimeException(e.getMessage(),e);
}
}
/**
* 获取某个单元格的值,并做一定的类型判断。
*
* @param cell
* @return
*/
public Object getCellValue(HSSFCell cell){
Object value = null;
if(cell != null){
int cellType = cell.getCellType();
HSSFCellStyle style = cell.getCellStyle();
short format = style.getDataFormat();
switch(cellType){
case HSSFCell.CELL_TYPE_NUMERIC:
double numTxt = cell.getNumericCellValue();
if(format == 22 || format == 14)value = HSSFDateUtil.getJavaDate(numTxt);
else value = numTxt;
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
boolean booleanTxt = cell.getBooleanCellValue();
value = booleanTxt;
break;
case HSSFCell.CELL_TYPE_BLANK:
value = null;
break;
case HSSFCell.CELL_TYPE_FORMULA:
HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
eval.evaluateInCell(cell);
value = getCellValue(cell);
break;
case HSSFCell.CELL_TYPE_STRING:
HSSFRichTextString rtxt = cell.getRichStringCellValue();
if(rtxt == null){
break;
}
String txt = rtxt.getString();
value = txt;
break;
default:
//System.out.println(cell.getColumnIndex()+" col cellType="+cellType);
}
}
return value;
}
public static interface CellCallback{
public void handler(HSSFCell cell);
}
/**
* 遍历所有的单元格。
* @param callback
* @param sheetNo
*/
public void iterator(CellCallback callback,int sheetNo){
HSSFSheet sheet = wb.getSheetAt(sheetNo);
if(sheet == null)return;
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for (int i = firstRowNum; i <= lastRowNum; i++) {
HSSFRow row = sheet.getRow(i);
if(row == null)continue;
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
HSSFCell cell = row.getCell(j);
callback.handler(cell);
}
}
}
/**
* 读取某个excel,然后将其转化为List的List。
*
* @param source
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public List<List> excelToListList(int sheetNo){
//首先是讲excel的数据读入,然后根据导入到的数据库的结构和excel的结构来决定如何处理。
HSSFSheet sheet = wb.getSheetAt(sheetNo);
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
List rows = new ArrayList();
for (int i = firstRowNum; i <= lastRowNum; i++) {
HSSFRow row = sheet.getRow(i);
List cellList = new ArrayList();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
Object value = null;
HSSFCell cell = row.getCell(j);
value = getCellValue(cell);
cellList.add(value);
}
rows.add(cellList);
}
return rows;
}
/**把excel转换成List<Map>格式
* @param sheetNo 需要转换的数据所在的sheet次序号(0,1,2...n)
* @return
*/
public List<Map> excelToMapList(int sheetNo)
{
HSSFSheet sheet = this.wb.getSheetAt(sheetNo);
int firstRowNum = sheet.getFirstRowNum();
return excelToMapList(sheetNo, firstRowNum, firstRowNum + 1);
}
/**把excel转换成List<Map>格式
* @param sheetNo需要转换的数据所在的sheet次序号(0,1,2...n)
* @param keyRowNo 作为key的行号 (0,1,2...n)
* @param dataStartRowNo第一行数据的行号 (1,2...n)
* @return
*/
public List<Map> excelToMapList(int sheetNo, int keyRowNo, int dataStartRowNo)
{
return excelToMapList( sheetNo, keyRowNo, keyRowNo, dataStartRowNo);
}
/**
* 标题从多行进行合并得到。
* @param sheetNo
* @param keyRowNoFrom
* @param keyRowNoTo
* @param dataStartRowNo
* @return
*/
public List<Map> excelToMapList(int sheetNo, int keyRowNoFrom, int keyRowNoTo, int dataStartRowNo)
{
HSSFSheet sheet = this.wb.getSheetAt(sheetNo);
List rowMapList = new ArrayList();
String[] keyList = new String[200];
for (int i = keyRowNoFrom; i <=keyRowNoTo ; i++) {
HSSFRow mapKeyRow = sheet.getRow(i);
String lstKey = null;
for (int j = mapKeyRow.getFirstCellNum(); j < mapKeyRow.getLastCellNum(); j++) {
HSSFCell col = mapKeyRow.getCell(j);
String key = col.getRichStringCellValue().getString();
String keyx = keyList[j];
if(key == null){
key = keyx;
}else if(keyx != null)key = keyx+key;
if(key == null || "".equals(key)){
key = lstKey;
}
lstKey = key;
keyList[j] = key;
}
}
int lastRowNum = sheet.getLastRowNum();
for (int i = dataStartRowNo; i <= lastRowNum; ++i) {
HSSFRow dataRow = sheet.getRow(i);
if (dataRow == null) continue;
Map rowMap = new HashMap();
for (int j = dataRow.getFirstCellNum(); j < dataRow.getLastCellNum(); ++j) {
String key = keyList[j];
if(key == null || key.equals("")){
continue;
}
Object value = getCellValue(dataRow.getCell(j));
rowMap.put(key, value);
}
rowMapList.add(rowMap);
}
return rowMapList;
}
public void mapListToExcel(Excel excel,List<Map> rs, Iterator it){
}
/**
* 复制srcRowNum,然后在targetRowNum处添加一行。
* @param srcRowNum
* @return
*/
public HSSFRow createRow(int srcRowNum){
HSSFSheet sheet = wb.getSheetAt(0);
int targetRowNum = sheet.getLastRowNum();
return createRow(sheet,sheet,srcRowNum, targetRowNum);
}
/**
* 复制srcRowNum,然后在targetRowNum处添加一行。
* @param srcRowNum
* @param targetRowNum
* @return
*/
public HSSFRow createRow(int srcRowNum, int targetRowNum){
HSSFSheet sheet = wb.getSheetAt(0);
return createRow(sheet,sheet,srcRowNum, targetRowNum);
}
/**
* 复制srcRowNum,然后在targetRowNum处添加一行。
* @param sheet
* @param srcRowNum
* @param targetRowNum
* @return
*/
public HSSFRow createRow(HSSFSheet srcSheet,HSSFSheet targetSheet,int srcRowNum, int targetRowNum){
HSSFRow srcRow = srcSheet.getRow(srcRowNum);
HSSFRow newRow = targetSheet.createRow(targetRowNum);
newRow.setHeight(srcRow.getHeight());
int i = 0;
for (Iterator<Cell> cit = srcRow.cellIterator(); cit.hasNext(); ) {
Cell hssfCell = cit.next();
//HSSFCell中的一些属性转移到Cell中
HSSFCell cell = newRow.createCell(i++);
CellStyle s = hssfCell.getCellStyle();
cell.setCellStyle(hssfCell.getCellStyle());
}
return newRow;
}
public void deleteRow(int rowNum){
deleteRow(0,rowNum);
}
public void deleteRow(int sheetNo,int rowNum){
HSSFSheet sheet = wb.getSheetAt(sheetNo);
sheet.shiftRows(rowNum, sheet.getLastRowNum(), -1);
}
/**
* 拷贝行粘帖到指定位置。
* @param sheet
* @param srcRow
* @param rowNum
* @return
*/
public HSSFRow copyAndInsertRow(HSSFSheet sheet, HSSFRow srcRow,int targetRowNum){
sheet.shiftRows(targetRowNum, sheet.getLastRowNum(), 1);
HSSFRow newRow = sheet.getRow(targetRowNum);
newRow.setHeight(srcRow.getHeight());
int j = 0;
for (Iterator<Cell> cit = srcRow.cellIterator(); cit.hasNext(); ) {
Cell hssfCell = cit.next();
//HSSFCell中的一些属性转移到Cell中
HSSFCell cell = newRow.createCell(j++);
cell.setCellStyle(hssfCell.getCellStyle());
}
for( int i = 0; i < sheet.getNumMergedRegions(); i++ ){
CellRangeAddress s = null;s.getFirstColumn();
CellRangeAddress region = sheet.getMergedRegion(i);
if(region.getFirstRow() == srcRow.getRowNum()
&& region.getLastRow() == region.getFirstRow()
){
sheet.addMergedRegion(new CellRangeAddress(targetRowNum,region.getFirstColumn(),
targetRowNum,region.getLastColumn()));
}
}
return newRow;
}
public HSSFRow copyAndInsertRow(int sheetNo,int fromRowNum,int targetRowNum){
HSSFSheet sheet = wb.getSheetAt(sheetNo);
HSSFRow srcRow = sheet.getRow(fromRowNum);
return copyAndInsertRow( sheet, srcRow, targetRowNum);
}
public HSSFRow copyAndInsertRow(int fromRowNum,int targetRowNum){
return copyAndInsertRow(0, fromRowNum, targetRowNum);
}
public HSSFWorkbook getWb() {
return wb;
}
public void setWb(HSSFWorkbook wb) {
this.wb = wb;
}
public void setForceFormulaRecalculation(boolean v){
wb.getSheetAt(0).setForceFormulaRecalculation(v);
}
public static void main(String[] args) throws ParseException, FileNotFoundException, IOException {
//if(true)return;
Excel excel = new Excel("f:/antennaxxx.xls");//如果你用的是2007的话,保存时选择97~2003的格式
List<Map> rs = excel.excelToMapList(0);
for (Map map : rs) {
String log = (String)map.get("F_OPERATION_LOG");
System.out.println("Excel.main()"+log);
}
}
}
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* Excel操作类。
* 本类提供了简单的获取指定cell给指定cell添加内容的方法。
*
* 注意遵照java的习惯,row从0开始算,不过excel对单元格标注的习惯是从1开始算,这个需要留意。
*
*/
public class Excel {
private static final Log log = LogFactory.getLog(Excel.class);
HSSFWorkbook wb;
private final static SimpleDateFormat fullTimeFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private final static SimpleDateFormat fullDateFmt = new SimpleDateFormat("yyyy-MM-dd");
public Excel(InputStream is){
POIFSFileSystem fs;
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (FileNotFoundException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public Excel(){
wb = new HSSFWorkbook();
}
public Excel(String filePath){
POIFSFileSystem fs;
try {
fs = new POIFSFileSystem(new FileInputStream(filePath));
wb = new HSSFWorkbook(fs);
} catch (FileNotFoundException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
class Point{
public Point(String cellPositionStr){
char[] chars = cellPositionStr.toCharArray();
int i = 0;
for (; i < chars.length; i++) {
if(Character.isDigit(chars[i])){
break;
}
}
row = Integer.parseInt(cellPositionStr.substring(i))-1;
col = cellNumStr2Int(cellPositionStr.substring(0, i));
}
public Point(String colStr,int row){
col = cellNumStr2Int(colStr);
this.row = row;
}
int row;
int col;
}
/**
* 获取sheet数目。
* @return
*/
public int getSheetCnt(){
return this.wb.getNumberOfSheets();
}
/**
* 给Excel中的某个sheet的某个单元格赋值。
*
* @param cellPositionStr 位置参数如A12表示A列,12行。
* @param sheetNo
* @param v
* @return
*/
public HSSFCell setCellValue(String cellPositionStr, int sheetNo, Object v){
Point p = new Point(cellPositionStr);
return setCellValue( p, sheetNo, v);
}
public HSSFCell setCellValue(String cellPositionStr, Object v){
Point p = new Point(cellPositionStr);
return setCellValue( p, 0, v);
}
/**
* 给Excel中的某个sheet的某个单元格赋值。
*
* @param colNumStr 哪一列
* @param rowNum
* @param sheetNo
* @param v
* @return
*/
public HSSFCell setCellValue(String colNumStr, int rowNum, int sheetNo, Object v){
Point p = new Point(colNumStr,rowNum);
return setCellValue( p, sheetNo, v);
}
public HSSFCell setCellValue(Point p, int sheetNo, Object v){
return setCellValue( p.col, p.row, sheetNo, v);
}
/**
* 给Excel中的某个sheet的某个单元格赋值。
*
* @param colNum
* @param rowNum 从0开始。
* @param sheetNo 从0开始。
* @param v
* @return
*/
public HSSFCell setCellValue(int colNum, int rowNum, int sheetNo, Object v){
HSSFCell cell = this.getCell(colNum, rowNum, sheetNo);
if(v == null){
cell.setCellValue(new HSSFRichTextString(""));//TODO 添加的值是以单元格格式为准,还是以数据类型为准?
return cell;
}
if(v.getClass() == Boolean.class){
cell.setCellValue((Boolean)v);
}else if(v.getClass() == Integer.class){
cell.setCellValue((Integer)v);
}else if(v.getClass() == Double.class){
cell.setCellValue((Double)v);
}else if(v.getClass() == Float.class){
cell.setCellValue((Float)v);
}else if(v.getClass() == BigDecimal.class){
cell.setCellValue(((BigDecimal)v).doubleValue());
}else if(v instanceof Date){
cell.setCellValue(new HSSFRichTextString(fullTimeFmt.format((Date)v)));//TODO 权益之计
}else if(v instanceof oracle.sql.TIMESTAMP){
oracle.sql.TIMESTAMP vx = (oracle.sql.TIMESTAMP)v;
try {
cell.setCellValue(new HSSFRichTextString(fullTimeFmt.format(vx.timestampValue())));
} catch (SQLException e) {
throw new RuntimeException(e.getMessage(),e);
}
}else if(v.getClass() == String.class){
String cellStr = (String)v;
if( cellStr.length() >= 32766 ){
cellStr = cellStr.substring(0, 32765);
if(log.isWarnEnabled()){
log.warn("str has been substring(0, 32765) for cell ("+sheetNo+","+rowNum+","+colNum+")");
}
}
cell.setCellValue(new HSSFRichTextString( cellStr ) );
}else{
cell.setCellValue(new HSSFRichTextString(v.toString()));
}
return cell;
}
/**
* 根据指定行列和sheet获取单元。
*
* @param rowNum
* @param cellNum
* @param sheetNo
* @return
*/
public HSSFCell getCell(int colNum,int rowNum,int sheetNo){
HSSFRow row = getRow(rowNum,sheetNo);
HSSFCell cell = row.getCell(colNum);
if(cell == null)cell = row.createCell(colNum);
return cell;
}
public HSSFCell getCell(String colNumStr, int rowNum, int sheetNo){
int colNum = cellNumStr2Int(colNumStr);
return getCell(colNum,rowNum,sheetNo);
}
public HSSFCell getCell(String cellPositionStr,int sheetNo){
Point p = new Point(cellPositionStr);
return getCell(p.col,p.row,sheetNo);
}
public HSSFSheet getSheetAt(int num){
return wb.getSheetAt(num);
}
/**
* 合并。
* @param sheetNum
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
*/
public void addMergedRegion(int sheetNum, int firstRow, int lastRow, int firstCol, int lastCol ){
HSSFSheet sheet = getSheetAt(sheetNum);
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));//指定合并区域
}
/**
* 获取某一行。
*
* @param rowNum
* @param sheetNo
* @return
*/
public HSSFRow getRow(int rowNum,int sheetNo){
HSSFSheet sheet = null;
if(sheetNo >= wb.getNumberOfSheets()){
sheet = wb.createSheet("sheet-"+sheetNo);
}else{
sheet = wb.getSheetAt(sheetNo);
}
HSSFRow row = sheet.getRow(rowNum);
if(row == null)
row = sheet.createRow(rowNum);
return row;
}
/**
* 将列的名称转换为数字。
*
* @param cellNumStr
* @return
*/
private static int cellNumStr2Int(String cellNumStr){
cellNumStr = cellNumStr.toLowerCase();
int cellNum = 0;
char[] chars = cellNumStr.toCharArray();
int j = 0;
for(int i = chars.length-1; i >=0 ; i--){
cellNum += (chars[i]-'a'+1)*Math.pow(26, j);
j++;
}
return cellNum-1;
}
public static String cellNumIntToStr(int colNum){
String colName = "";
// for(int i=0;i<colNum/26+1;i++){
// char c = (char)(colNum%26+'a'-i);
// colName = c + colName;
// colNum = colNum/26;
// }
//colNum++;
do{
char c = (char)(colNum%26+'A');
colName = c + colName;
colNum = colNum/26-1;
}while(colNum >= 0);
return colName;
}
/**
* 将excel写入到某个输出流中。
*
* @param out
*/
public void write(OutputStream out){
try {
wb.write(out);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public void save(String filePath){
try {
OutputStream out = new FileOutputStream(new File(filePath));
write(out);
out.flush();
out.close();
} catch (FileNotFoundException e) {
throw new RuntimeException(e.getMessage(),e);
} catch (IOException e) {
throw new RuntimeException(e.getMessage(),e);
}
}
/**
* 获取某个单元格的值,并做一定的类型判断。
*
* @param cell
* @return
*/
public Object getCellValue(HSSFCell cell){
Object value = null;
if(cell != null){
int cellType = cell.getCellType();
HSSFCellStyle style = cell.getCellStyle();
short format = style.getDataFormat();
switch(cellType){
case HSSFCell.CELL_TYPE_NUMERIC:
double numTxt = cell.getNumericCellValue();
if(format == 22 || format == 14)value = HSSFDateUtil.getJavaDate(numTxt);
else value = numTxt;
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
boolean booleanTxt = cell.getBooleanCellValue();
value = booleanTxt;
break;
case HSSFCell.CELL_TYPE_BLANK:
value = null;
break;
case HSSFCell.CELL_TYPE_FORMULA:
HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
eval.evaluateInCell(cell);
value = getCellValue(cell);
break;
case HSSFCell.CELL_TYPE_STRING:
HSSFRichTextString rtxt = cell.getRichStringCellValue();
if(rtxt == null){
break;
}
String txt = rtxt.getString();
value = txt;
break;
default:
//System.out.println(cell.getColumnIndex()+" col cellType="+cellType);
}
}
return value;
}
public static interface CellCallback{
public void handler(HSSFCell cell);
}
/**
* 遍历所有的单元格。
* @param callback
* @param sheetNo
*/
public void iterator(CellCallback callback,int sheetNo){
HSSFSheet sheet = wb.getSheetAt(sheetNo);
if(sheet == null)return;
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for (int i = firstRowNum; i <= lastRowNum; i++) {
HSSFRow row = sheet.getRow(i);
if(row == null)continue;
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
HSSFCell cell = row.getCell(j);
callback.handler(cell);
}
}
}
/**
* 读取某个excel,然后将其转化为List的List。
*
* @param source
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public List<List> excelToListList(int sheetNo){
//首先是讲excel的数据读入,然后根据导入到的数据库的结构和excel的结构来决定如何处理。
HSSFSheet sheet = wb.getSheetAt(sheetNo);
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
List rows = new ArrayList();
for (int i = firstRowNum; i <= lastRowNum; i++) {
HSSFRow row = sheet.getRow(i);
List cellList = new ArrayList();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
Object value = null;
HSSFCell cell = row.getCell(j);
value = getCellValue(cell);
cellList.add(value);
}
rows.add(cellList);
}
return rows;
}
/**把excel转换成List<Map>格式
* @param sheetNo 需要转换的数据所在的sheet次序号(0,1,2...n)
* @return
*/
public List<Map> excelToMapList(int sheetNo)
{
HSSFSheet sheet = this.wb.getSheetAt(sheetNo);
int firstRowNum = sheet.getFirstRowNum();
return excelToMapList(sheetNo, firstRowNum, firstRowNum + 1);
}
/**把excel转换成List<Map>格式
* @param sheetNo需要转换的数据所在的sheet次序号(0,1,2...n)
* @param keyRowNo 作为key的行号 (0,1,2...n)
* @param dataStartRowNo第一行数据的行号 (1,2...n)
* @return
*/
public List<Map> excelToMapList(int sheetNo, int keyRowNo, int dataStartRowNo)
{
return excelToMapList( sheetNo, keyRowNo, keyRowNo, dataStartRowNo);
}
/**
* 标题从多行进行合并得到。
* @param sheetNo
* @param keyRowNoFrom
* @param keyRowNoTo
* @param dataStartRowNo
* @return
*/
public List<Map> excelToMapList(int sheetNo, int keyRowNoFrom, int keyRowNoTo, int dataStartRowNo)
{
HSSFSheet sheet = this.wb.getSheetAt(sheetNo);
List rowMapList = new ArrayList();
String[] keyList = new String[200];
for (int i = keyRowNoFrom; i <=keyRowNoTo ; i++) {
HSSFRow mapKeyRow = sheet.getRow(i);
String lstKey = null;
for (int j = mapKeyRow.getFirstCellNum(); j < mapKeyRow.getLastCellNum(); j++) {
HSSFCell col = mapKeyRow.getCell(j);
String key = col.getRichStringCellValue().getString();
String keyx = keyList[j];
if(key == null){
key = keyx;
}else if(keyx != null)key = keyx+key;
if(key == null || "".equals(key)){
key = lstKey;
}
lstKey = key;
keyList[j] = key;
}
}
int lastRowNum = sheet.getLastRowNum();
for (int i = dataStartRowNo; i <= lastRowNum; ++i) {
HSSFRow dataRow = sheet.getRow(i);
if (dataRow == null) continue;
Map rowMap = new HashMap();
for (int j = dataRow.getFirstCellNum(); j < dataRow.getLastCellNum(); ++j) {
String key = keyList[j];
if(key == null || key.equals("")){
continue;
}
Object value = getCellValue(dataRow.getCell(j));
rowMap.put(key, value);
}
rowMapList.add(rowMap);
}
return rowMapList;
}
public void mapListToExcel(Excel excel,List<Map> rs, Iterator it){
}
/**
* 复制srcRowNum,然后在targetRowNum处添加一行。
* @param srcRowNum
* @return
*/
public HSSFRow createRow(int srcRowNum){
HSSFSheet sheet = wb.getSheetAt(0);
int targetRowNum = sheet.getLastRowNum();
return createRow(sheet,sheet,srcRowNum, targetRowNum);
}
/**
* 复制srcRowNum,然后在targetRowNum处添加一行。
* @param srcRowNum
* @param targetRowNum
* @return
*/
public HSSFRow createRow(int srcRowNum, int targetRowNum){
HSSFSheet sheet = wb.getSheetAt(0);
return createRow(sheet,sheet,srcRowNum, targetRowNum);
}
/**
* 复制srcRowNum,然后在targetRowNum处添加一行。
* @param sheet
* @param srcRowNum
* @param targetRowNum
* @return
*/
public HSSFRow createRow(HSSFSheet srcSheet,HSSFSheet targetSheet,int srcRowNum, int targetRowNum){
HSSFRow srcRow = srcSheet.getRow(srcRowNum);
HSSFRow newRow = targetSheet.createRow(targetRowNum);
newRow.setHeight(srcRow.getHeight());
int i = 0;
for (Iterator<Cell> cit = srcRow.cellIterator(); cit.hasNext(); ) {
Cell hssfCell = cit.next();
//HSSFCell中的一些属性转移到Cell中
HSSFCell cell = newRow.createCell(i++);
CellStyle s = hssfCell.getCellStyle();
cell.setCellStyle(hssfCell.getCellStyle());
}
return newRow;
}
public void deleteRow(int rowNum){
deleteRow(0,rowNum);
}
public void deleteRow(int sheetNo,int rowNum){
HSSFSheet sheet = wb.getSheetAt(sheetNo);
sheet.shiftRows(rowNum, sheet.getLastRowNum(), -1);
}
/**
* 拷贝行粘帖到指定位置。
* @param sheet
* @param srcRow
* @param rowNum
* @return
*/
public HSSFRow copyAndInsertRow(HSSFSheet sheet, HSSFRow srcRow,int targetRowNum){
sheet.shiftRows(targetRowNum, sheet.getLastRowNum(), 1);
HSSFRow newRow = sheet.getRow(targetRowNum);
newRow.setHeight(srcRow.getHeight());
int j = 0;
for (Iterator<Cell> cit = srcRow.cellIterator(); cit.hasNext(); ) {
Cell hssfCell = cit.next();
//HSSFCell中的一些属性转移到Cell中
HSSFCell cell = newRow.createCell(j++);
cell.setCellStyle(hssfCell.getCellStyle());
}
for( int i = 0; i < sheet.getNumMergedRegions(); i++ ){
CellRangeAddress s = null;s.getFirstColumn();
CellRangeAddress region = sheet.getMergedRegion(i);
if(region.getFirstRow() == srcRow.getRowNum()
&& region.getLastRow() == region.getFirstRow()
){
sheet.addMergedRegion(new CellRangeAddress(targetRowNum,region.getFirstColumn(),
targetRowNum,region.getLastColumn()));
}
}
return newRow;
}
public HSSFRow copyAndInsertRow(int sheetNo,int fromRowNum,int targetRowNum){
HSSFSheet sheet = wb.getSheetAt(sheetNo);
HSSFRow srcRow = sheet.getRow(fromRowNum);
return copyAndInsertRow( sheet, srcRow, targetRowNum);
}
public HSSFRow copyAndInsertRow(int fromRowNum,int targetRowNum){
return copyAndInsertRow(0, fromRowNum, targetRowNum);
}
public HSSFWorkbook getWb() {
return wb;
}
public void setWb(HSSFWorkbook wb) {
this.wb = wb;
}
public void setForceFormulaRecalculation(boolean v){
wb.getSheetAt(0).setForceFormulaRecalculation(v);
}
public static void main(String[] args) throws ParseException, FileNotFoundException, IOException {
//if(true)return;
Excel excel = new Excel("f:/antennaxxx.xls");//如果你用的是2007的话,保存时选择97~2003的格式
List<Map> rs = excel.excelToMapList(0);
for (Map map : rs) {
String log = (String)map.get("F_OPERATION_LOG");
System.out.println("Excel.main()"+log);
}
}
}
1 0
- 文件上传、POI
- poi文件上传
- POI--------文件上传导出
- SSH+POI实现文件上传
- Struts1 文件上传(含poi)
- spingmvc 上传文件, poi解析xls,xlsx
- java poi 上传与下载word文件
- EXCEL文件上传,内容格式验证(POI)
- 关于springMVC+poi上传Excel文件问题解决
- ocupload、struts2实现excel文件上传,poi解析
- 头像上传与使用POI导入导出文件列表
- ocupload 和Apache POI 处理文件上传的问题
- Apache POI实现Excel文件上传、导出,工具类分享
- Java SpringMVC POI上传excel并读取文件内容
- poi上传解析execl
- POI:上传EXCEL解析
- poi上传excel
- SpringMvc+POI上传Excel
- [LeetCode 156] Binary Tree Upside Down
- UVA 10391 - Compound Words
- iOS开发-Xcode 7使用免费证书调试应用
- sql选出层级树(转)
- 软件打包总结
- 文件上传、POI
- 用vbs等实现辅助网站的后台运行工作进程(四)
- 安卓打日志是否记录了手机系统文件里面了?
- java编译找不到符号
- hibernaate 主键生成策略
- 关于C# webbrowser中documentText乱码问题
- vim 一些好用的技巧
- SQLite获取查询结果数
- zip 和 sourceCRT