调查POI方法的使用以及注意的地方
Apache POI官网
http://poi.apache.org/
-
单元格着色：
第一种情况:
//创建工作簿
HSSFWorkbook workbook =new HSSFWorkbook();
//创建样式对象
CellStyle cellStyle = workbook.createCellStyle();
//设置单元格填充样式
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置单元格前景色
style.setFillForegroundColor(HSSFColor.RED.index);
上面的代码就可以使当前单元格着色为红色(如下图)
第二种情况:
//创建工作簿
HSSFWorkbook workbook =new HSSFWorkbook();
//创建样式对象
CellStyle cellStyle = workbook.createCellStyle();
//设置单元格填充样式
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置单元格前景色
style.setFillForegroundColor(HSSFColor.RED.index);
//设置单元格背景色
style.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);
以上的代码就可以使当前单元格被红蓝交替的格子填充。(如下图)
第三种情况:
如果去掉setFillPattern设置单元格填充样式,同时设置前景色和背景色,生成的excel文件没有填充颜色,此时既不会用前景色填充，也不会用背景色填充。就相当于设置填充单元格模式为不填充:setFillPattern(HSSFCellStyle.NO_FILL) (如下图)
第四种情况:
去掉setFillForegroundColor设置单元格的前景色,同时设置填充的模式和填充的背景色，生成的excel文件(如下图所示),相当于setFillForegroundColor(HSSFColor.BLACK.index);
-
冻结窗口：
方法一:参数1列，参数2行
sheet.createFreezePane(intcolSplit,introwSplit);
示例:
sheet.createFreezePane(0,0);
sheet.createFreezePane(1,2);
sheet.createFreezePane(0,2);
方法二:
@param colSplit Horizonatal分割的位置。
* @param rowSplit分裂的垂直位置。
* @param leftmostColumn左列显示在右窗格中。
* @param topRow顶部排在底部窗格
sheet.createFreezePane(int colSplit, int rowSplit,int leftmostColumn,int topRow);
程序示例:
sheet.createFreezePane(1, 2, 2,1);
topRow参数必须不小于leftmostColumn参数
sheet.createFreezePane(1,2, 1,2 );
sheet.createFreezePane(1,2, 0,0 );
leftmostColumn参数必须不小于colSplit参数
sheet.createFreezePane(1,2, 2,2 );
sheet.createFreezePane(1,2, 2,4 );
-
导出公式：
使用Cell接口下的:
void setCellFormula(Stringformula)throws FormulaParseException;方法
HSSFCELL、XSSFCELL、SXSSFCELL都实现了该接口
注意这个方法只设置字符串公式,不计算公式值
SXSSFCELL：
专门用来处理大量数据写入Excel 2007时,”内存溢出”的问题。
读取Excel仍然是“XSSFWorkbook”，写入则为“SXSSFWorkbook ”。
程序示例:
package com.excel.poi.gz10000;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* <b>数据匹配</b> <br>
* <ul>
* <li>作者：C_Dream </li>
* <li>当前版本：4.0 </li>
* <li>修改时间： 2012-12-6 17:50 </li>
* <li>修改内容：<ol>
* <li>更换Excel2007写入方式，大幅提升性能。</li>
* <li>引入 log4j，局部提升性能。</li>
* <li>部分代码优化，CMD输出提示可选。</li>
*</ol></li>
* <li>创建时间： 2012-11-08 19:22 </li>
* </ul>
*/
public class Config extends HttpServlet{
privatestatic final long serialVersionUID = 1L;
//Config c =new Config();//此处决不能 new本类，否则报错：java.lang.StackOverflowError
Stringclass_path = this.getClass().getResource("").getPath();
public voiddoPost(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException
{
this.doGet(request,response);//将表单 post方法传过来的参数，转给 get方法去处理
}
public voiddoGet(HttpServletRequest request,HttpServletResponse response)throwsServletException, IOException
{
request.setCharacterEncoding("UTF-8"); //转码
Stringforms=(String)request.getParameter("forms");
if(forms.equals("if_3g")){
try{
this.read_Excel(request,response);
}catch (SQLException e) {
e.printStackTrace();
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
response.sendRedirect("index.jsp?done=true");
}
@SuppressWarnings("rawtypes")
public voidread_Excel(HttpServletRequest request,
HttpServletResponseresponse) throws UnsupportedEncodingException, SQLException,ClassNotFoundException {
PropertyConfigurator.configure(class_path+"log4j.properties");//获取 log4j配置文件
Loggerlogger = Logger.getLogger(Config.class ); //获取log4j的实例
StringstartTmie = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(newDate());
logger.debug("\n**********【准备处理，正在加载文件】**********");//7
logger.debug("\n\n**********每行数据间请不要有空行，以免程序误判**********");//7
logger.debug("\n\n**********由于处理Excel的插件本身性能局限，请控制文件大小，以免影响加载效率。**********");//7
logger.debug("\n\n**********大小在\t 7 MB \t以内，大约\t 21 万 \t行数据。**********");//7
logger.debug("\n\n**********本程序每分钟约处理 7000条数据，但根据文件大小会有影响。**********");//7
request.setCharacterEncoding("UTF-8"); //转码
Stringxls_read_Address=(String)request.getParameter("xls_read_Address");//读取
Stringxls_write_Address=(String)request.getParameter("xls_write_Address");//写入
String count_rows=(String)request.getParameter("count_rows");//自动编号
String tips_cmd=(String)request.getParameter("tips_cmd");//CMD窗口的提示方式
try {
DataConvertdc = new DataConvert();//数据转换工具
DecimalFormatdf = (DecimalFormat) NumberFormat.getPercentInstance();
ArrayList<ArrayList>ls = new ArrayList<ArrayList>();
Fileexcel_file = new File(xls_read_Address);//读取的文件路径
FileInputStreaminput = new FileInputStream(excel_file); //读取的文件路径
XSSFWorkbook wb = new XSSFWorkbook(newBufferedInputStream(input));
intsheet_numbers = wb.getNumberOfSheets();//获取表的总数
logger.debug("\n\n**********共有工作表总数**********："+sheet_numbers);//7
String[] sheetnames=new String[sheet_numbers];
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
String s_3g=null;
Class.forName("oracle.jdbc.driver.OracleDriver");
con =DriverManager.getConnection("jdbc:oracle:thin:@12.12.12.123:1521:gz10000","gz12345","12345");
stmt = con.createStatement();
for(inti=0;i<sheet_numbers;i++){//遍历所有表
ArrayList<String[]> ls_a = new ArrayList<String[]>(); //用来存储某个表读取出来的数据
Sheet sheet = wb.getSheetAt(i); //获取某个表
sheetnames[i] = sheet.getSheetName();//获取表名，存入数组
logger.debug("\n\n---正在读取和匹配工作表\t《"+sheetnames[i]+"》\t的数据---\n");//7
introws_num = sheet.getLastRowNum();//获取行数
logger.debug("\n\n---表\t《"+sheetnames[i]+"》\t共有数据---：\t"+rows_num+"\t行");//7
for( int rows=0;rows<rows_num;rows++){
Row row = sheet.getRow(rows);//取得某一行 对象
if(row!=null&&!(row.equals(""))){
int columns_num = row.getLastCellNum();//获取列数
String[] s =new String[5];//初始化数组长度
for( intcolumns=0;columns<columns_num;columns++){
Cell cell = row.getCell(columns);
if(cell!=null){
switch ( cell.getCellType()){
case XSSFCell.CELL_TYPE_STRING: //字符串
s[columns] = cell.getStringCellValue();
if(s[columns]==null){
s[columns]=" ";
}
break;
case XSSFCell.CELL_TYPE_NUMERIC: //数字
double strCell =cell.getNumericCellValue();
if(String.valueOf(strCell)==null){
s[columns]=" ";
}
df.applyPattern("0");
s[columns] =df.format(strCell);
if(Double.parseDouble(s[columns])!=strCell){
df.applyPattern(Double.toString(strCell));
s[columns] =df.format(strCell);
}
break;
case XSSFCell.CELL_TYPE_BLANK: //空值
s[columns]=" ";
break;
default:
logger.debug("\n---单元格格式不支持---");
break;
}
}
}
if(count_rows.equals("是")&&rows>0){
s[0]=dc.intToString(rows);//自动编号
}
/* ******** 访问数据库，并判断是否3G ******** */
String sql="select busiattr1from ap_t_si_cus_spec_info where cus_phone='"+s[1]+"' andrownum=1";
rs = stmt.executeQuery(sql);
if(rs.next()){
if(rs.getString("busiattr1")!=null){
s_3g= rs.getString("busiattr1").toString().toUpperCase();
}
else{
s_3g="";
}
}
else{
s_3g=" ";
}
/* ******** 访问结束 ******** */
if(s_3g.contains("3G")){
s[4]="是";//写入“是否3G”这一列的值，比如“是”
}
if(s[4]==null){
s[4]="\t";
}
/* CMD窗口提示方式 */
if(!(tips_cmd.equals("none"))&&tips_cmd!=null&&!(tips_cmd.equals(""))){
if(tips_cmd.equals("all")){
logger.debug("\n匹配中："+s[0]+"\t"+s[1]+"\t"+s[2]+"\t"+s[3]+"\t"+s[4]);
}else
if(rows%DataConvert.stringToInt(tips_cmd)==0){
logger.debug("\n匹配中："+s[0]+"\t"+s[1]+"\t"+s[2]+"\t"+s[3]+"\t"+s[4]);
}
}
ls_a.add(s);//添加每行数据到 ls_a
}
}
ls.add(ls_a); //添加每个表到 ls
input.close();
write_Excel( xls_write_Address, ls, sheetnames ,tips_cmd) ;
}
} catch(IOException ex) {
ex.printStackTrace();
}
StringendTmie = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(newDate());
logger.debug("\n\n***************【处理完成，程序结束】***************");//7
logger.debug("\n开始时间："+startTmie);
logger.debug("\n结束时间："+endTmie);
logger.debug("\n新文件输出路径为："+xls_write_Address);
}
@SuppressWarnings({"rawtypes", "unchecked" })
public voidwrite_Excel( String xls_write_Address,ArrayList<ArrayList> ls,String[]sheetnames,String tips_cmd ) throws IOException {
PropertyConfigurator.configure(class_path+"log4j.properties");//获取 log4j配置文件
Loggerlogger = Logger.getLogger(Config.class ); //获取log4j的实例
FileOutputStreamoutput = new FileOutputStream(new File(xls_write_Address)); //读取的文件路径
SXSSFWorkbook wb = new SXSSFWorkbook(10000);//内存中保留 10000 条数据，以免内存溢出，其余写入硬盘
for(intsn=0;sn<ls.size();sn++){
Sheet sheet = wb.createSheet(String.valueOf(sn));
wb.setSheetName(sn, sheetnames[sn]);
ArrayList<String[]>ls2 = ls.get(sn);
for(inti=0;i<ls2.size();i++){
Row row = sheet.createRow(i);
String[] s = ls2.get(i);
for(int cols=0;cols<s.length;cols++){
Cell cell =row.createCell(cols);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);//文本格式
sheet.setColumnWidth(cols, s[cols].length()*384);//设置单元格宽度
cell.setCellValue(s[cols]);//写入内容
}
/* CMD窗口提示方式 */
if(!(tips_cmd.equals("none"))&&tips_cmd!=null&&!(tips_cmd.equals(""))){
if(tips_cmd.equals("all")){
logger.debug("\n写入中："+s[0]+"\t"+s[1]+"\t"+s[2]+"\t"+s[3]+"\t"+s[4]);
}else
if(i%DataConvert.stringToInt(tips_cmd)==0){
logger.debug("\n写入中："+s[0]+"\t"+s[1]+"\t"+s[2]+"\t"+s[3]+"\t"+s[4]);
}
}
}
}
wb.write(output);
output.close();
}
}
-
非模版读取，带公式导出：
excel里面公式：ROUND(AE6-AF6-AH6-AG6,2)
java程序里面的公式:
HSSFRow oneRow = sheet.getRow(0);
四舍五入例：
String sixBuf ="ROUND(AE6-AF6-AH6 -AG6
",2)";
oneRow.getCell(6).setCellFormula(sixBuf.toString());
合计公式例：
oneRow.getCell(28).setCellFormula("SUM(AC6:AC"+oneRowNum+")");//合计 =SUM(AC6:AC10)
复杂公式的如：
String t29 ="IF(ISNA(VLOOKUP(E"
+ (oneRowNum + 1)
+ ",离入职!A:E,4,FALSE))=TRUE,\"\",CONCATENATE(MONTH(VLOOKUP(E"
+ (oneRowNum + 1)
+ ",离入职!A:E,4,FALSE)),\"月\",DAY(VLOOKUP(E"
+ (oneRowNum + 1)
+ ",离入职!A:E,4,FALSE)),\"日\",VLOOKUP(E"
+ (oneRowNum + 1) + ",离入职!A:E,5,FALSE)))";
oneRow.getCell(29).setCellFormula(t29);
第二种，读取固定模版的公式
红色部分表示获取模板的公式
oneRow.getCell(29).setCellFormula(oneRow.getCell(29).getCellFormula())；
设置计算公式获取值的方法:
setForceFormulaRecalculation(boolean value);在打开工作簿时是否重新计算所有的公式,如果计算所有的公式就设置为true,如下图
sheetOne.setForceFormulaRecalculation(true);
-
打网格：
/**
* 功能：创建带边框的CellStyle样式
* @param wb Workbook
* @param backgroundColor背景色
* @param foregroundColor前置
* @return CellStyle
*/
publicstatic CellStylecreateBorderCellStyle(Workbook wb,short backgroundColor,short foregroundColor,short halign){
CellStyle cellStyle =wb.createCellStyle();
//设置填充模式
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
/**
* 设置水平对齐的单元格类型：7种
* @see #ALIGN_GENERAL
* @see #ALIGN_LEFT
* @see #ALIGN_CENTER
* @see #ALIGN_RIGHT
* @see #ALIGN_FILL
* @see #ALIGN_JUSTIFY
* @see #ALIGN_CENTER_SELECTION
*/
cellStyle.setAlignment(halign);
//设置垂直对齐的单元格类型：4种
VERTICAL_TOP
VERTICAL_CENTER
VERTICAL_BOTTOM
VERTICAL_JUSTIFY
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
示例图:
//设置前景色
cellStyle.setFillForegroundColor(foregroundColor);
//设置背景色
cellStyle.setFillBackgroundColor(backgroundColor);
//设置填充模式 :有17种模式
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
//生成一个字体
HSSFFontfont = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);//把字体应用到当前的样式
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//设置边框左边
cellStyle.setBorderRight(CellStyle.BORDER_THIN);//设置边框右边
cellStyle.setBorderTop(CellStyle.BORDER_THIN);//设置边框上面
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);//设置边框下面
return cellStyle;
}
-
数据格式化:
保留一位小数
HSSFCellStyle cellDigitStyle = workbook.createCellStyle();
cellDigitStyle.setDataFormat(workbook.createDataFormat().getFormat("0.0"));
解析:DataFormat dataFormat = woekbook.createDataFormat();
DataFormat是importjava.text.DateFormat包下的类
保留两位小数
HSSFCellStyle cellDigitStyle2 = workbook.createCellStyle();
cellDigitStyle2.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00")); 或cellDigitStyle2.setDataFormat((short) 2);
注意:如果 getBuiltinFormat 返回不了参数，格式化就会失效。
例如HSSFDataFormat.getBuiltinFormat("00")只会返回-1，这样格式化就会失效。
百分数:
cell.setCellValue(20);
CellStylecellStyle = workBook.createCellStyle();
cellStyle.setDataFormat(DataFormat.getBuiltinFormat("0.00%"));
cell.setCellStyle(cellStyle);
日期格式化:
cell.setCellValue(newDate(2008,5,5));
CellStylecellStyle = workBook.createCellStyle();
DataFormatformat= workBook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd"));
cell.setCellStyle(cellStyle);
-
合并单元格：
方法一:
//(参数：起始行号，终止行号， 起始列号，终止列号)
终止行号必须等于或大于起始行号；终止列号必须等于或大于起始列号；
CellRangeddress(int firstRow, intlastRow, int firstCol, int lastCol)
方法二：
参数RecordInputStream记录输入流
CellRangeAddress(RecordInputStream in)
//声明合并单元格对象
CellRangeAddress cellRegion=new CellRangeAddress(int firstRow, int lastRow, intfirstCol, int lastCol);
sheet.addMergedRegion(cellRegion);
或
sheet.addMergedRegion(newCellRangeAddress(1, 1, 0, 2));
重点注意事项：
1.单元格CELL和ROW对象下标都是从0开始的。
2.单元格合并时addMergedRegion (1,2,3,4)第1个值的行号必须要比3位置的行号小，如果大于3就不能正常合并单元格
3.合并单元格的时候要合并的单元格必须先创建，这样方便后面再次获取这个单元格来填充数据，主要就是因为合并时不能由后向前进行合并引起的。
《程序示例》:
/**
* 获取合并单元格的值
*@param sheet
*@param row
*@param column
*@return
*/
public String getMergedRegionValue(Sheetsheet ,int row , int column){
int sheetMergeCount =sheet.getNumMergedRegions();
for(int i = 0 ; i <sheetMergeCount ; i++){
CellRangeAddress ca =sheet.getMergedRegion(i);
int firstColumn =ca.getFirstColumn();
int lastColumn =ca.getLastColumn();
int firstRow =ca.getFirstRow();
int lastRow =ca.getLastRow();
if(row >= firstRow&& row <= lastRow){
if(column >=firstColumn && column <= lastColumn){
Row fRow =sheet.getRow(firstRow);
Cell fCell =fRow.getCell(firstColumn);
returngetCellValue(fCell) ;
}
}
}
return null ;
}
/**
* 判断指定的单元格是否是合并单元格
*@param sheet
*@param row
*@param column
*@return
*/
public boolean isMergedRegion(Sheet sheet, int row , int column){
int sheetMergeCount =sheet.getNumMergedRegions();
for(int i = 0 ; i <sheetMergeCount ; i++ ){
CellRangeAddress ca =sheet.getMergedRegion(i);
int firstColumn =ca.getFirstColumn();
int lastColumn =ca.getLastColumn();
int firstRow =ca.getFirstRow();
int lastRow =ca.getLastRow();
if(row >= firstRow&& row <= lastRow){
if(column >=firstColumn && column <= lastColumn){
return true ;
}
}
}
return false ;
}
/**
* 获取单元格的值
*@param cell
*@return
*/
public String getCellValue(Cell cell){
if(cell == null) return"";
if(cell.getCellType() ==Cell.CELL_TYPE_STRING){
returncell.getStringCellValue();
}else if(cell.getCellType() ==Cell.CELL_TYPE_BOOLEAN){
returnString.valueOf(cell.getBooleanCellValue());
}else if(cell.getCellType() ==Cell.CELL_TYPE_FORMULA){
returncell.getCellFormula() ;
}else if(cell.getCellType() ==Cell.CELL_TYPE_NUMERIC){
returnString.valueOf(cell.getNumericCellValue());
}
return "";
}
-
调整列宽适应内容：
有的excel表打开后显示的不是数据而是######,需要双击才可以显示值（其实是因为单元格宽度不够导致的无法显示格式化之后的内容）
HSSFSheet sheet = workbook.createSheet(); //创建一个工作对象
方法一:
sheet.autoSizeColumn(intarg0);
此方法在合并单元格时并不好使
计算字符串的长度：
sheet.setColumnWidth(m, “列名”.getBytes().length*2*256);
方法二:
//参数1：列索引,从0开始；参数2boolean值:是否使用的内容合并单元格在计算列的宽度
sheet.autoSizeColumn(column,useMergedCells);
公式单元格处理列宽适应内容:
HSSFFormulaEvaluator evaluator = newHSSFFormulaEvaluator(sheet.getWorkbook());//格式化数据
CellValue cell71Val = evaluator.evaluate(cell71);
cell71.setCellValue(cell71Val.getNumberValue());//设值
5198
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
