本文介绍了如何在Flex客户端应用中利用JXL库，在Java服务端生成Excel文件并设置日期、数字单元格格式的具体步骤和代码示例。
Flex端调用代码：
private function export():void
{
if (this.passVehicleList.length <= 0)
{
MessageDlg.confirm("数据为空，不能导出！", null);
return;
}
parentname=parentname.replace(" ","");//去除名称中空格
var startDate:Date = timeStart.appendTimevalue(dateStart.selectedDate);
var stopDate:Date = timeStop.appendTimevalue(dateStop.selectedDate)
var startTimes:String = MyStringUtil.datetimetoString(startDate);
var stopTimes:String = MyStringUtil.datetimetoString(stopDate);
var serarchstr:String = searchStr.text;
inputParams.getParam("parentid").value = parentid;
if(isOver){
inputParams.getParam("isover").value = "over";
}
var contentTitle:String = startTimes+"至"+stopTimes+parentname;
var reportName:String = parentname;
var variables:URLVariables=new URLVariables();
variables.parentid = parentid;
variables.startTime = startTimes;
variables.stopTime = stopTimes;
variables.serarchstr = serarchstr;
var coloumTitle:Array;
if(!isOver){
contentTitle=contentTitle+"日常报表";
reportName=reportName+"日常报表";
variables.isover = "";
coloumTitle = new Array("名称","牌号","时间","类型","自重","载重","货重","备注");
}else{
contentTitle=contentTitle+"超载报表";
reportName=reportName+"超载报表";
variables.isover = "over";
coloumTitle = new Array("名称","牌号","时间","车型","限重","载重","超载","车主","移动电话","备注");
}
variables.fileName=reportName+".xls";
variables.contentTitle=contentTitle;
variables.coloumTitle=coloumTitle;
var u:URLRequest=new URLRequest(MyStringUtil.getUrlPath(Application.application.loaderInfo.url) + '/rest/ProjectAction.action');
u.data=variables; //Pass the variables
u.method=URLRequestMethod.POST; //Don't forget that we need to send as POST
navigateToURL(u, "_self");
}Java服务端代码：
说明：需要jxl.jar包
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.DateFormat;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import net.zdsoft.keel.action.ActionContext;
/*
* Excel 导出使用 文件名 标题 行标题 导出内容
*/
String fileName;//文件名
String contentTitle;//内容标题
String[] coloumTitle;//列标题
/******* 查询条件 ********/
String parentid;
String startTime;
String stopTime;
String serarchstr;
String isover;
public String exportExcel() {
// 以下开始输出到EXCEL
List<PassVehicle> contentList = new ArrayList<PassVehicle>();
InputParamList params = new InputParamList();
List<InputParam> paramList = new ArrayList<InputParam>();
if(parentid!=null&&!parentid.equals("")){
InputParam param = new InputParam();
param.setName("parentid");
param.setValue(parentid);
paramList.add(param);
}
if(startTime!=null&&!startTime.equals("")){
InputParam param = new InputParam();
param.setName("startTime");
param.setValue(startTime);
paramList.add(param);
}
if(stopTime!=null&&!stopTime.equals("")){
InputParam param = new InputParam();
param.setName("endTime");
param.setValue(stopTime);
paramList.add(param);
}
if(isover!=null&&!isover.equals("")){
InputParam param = new InputParam();
param.setName("isover");
param.setValue(isover);
paramList.add(param);
}
if(serarchstr!=null&&!serarchstr.equals("")){
InputParam param1 = new InputParam();
param1.setName("type");
param1.setValue("plateno");
paramList.add(param1);
InputParam param = new InputParam();
param.setName("data");
param.setValue(serarchstr);
paramList.add(param);
}
params.setList(paramList);
contentList = SpringUtils.getPassVehicleService().queryBySQL(params);
try {
// 定义输出流，以便打开保存对话框
ActionContext.getRequest().setCharacterEncoding("UTF-8");
System.out.println("文件名："+fileName);
HttpServletResponse response = ActionContext.getResponse();
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
response.setCharacterEncoding("UTF-8");
response.setHeader("pragma", "no-cache");
response.setHeader("cache-control", "no-cache");
response.setDateHeader("Expires", 0);
// response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment; filename="
+ new String(fileName.getBytes("GBK"), "ISO8859-1"));
// 设定输出文件头
response.setContentType("application/msexcel");// 定义输出类型
// 设定输出文件头
response.setContentType("application/msexcel");// 定义输出类型
// 定义输出流，以便打开保存对话框_______________________end
/** **********创建工作簿************ */
WritableWorkbook workbook = Workbook.createWorkbook(os);
/** **********创建工作表************ */
WritableSheet sheet = workbook.createSheet("Sheet1", 0);
/** **********设置行高列宽************ */
// sheet.setRowView( 0 , 100 );
sheet.setColumnView( 0 , 20 );
sheet.setColumnView( 1 , 20 );
sheet.setColumnView( 2 , 30 );
sheet.setColumnView( 3 , 10 );
sheet.setColumnView( 4 , 15 );
sheet.setColumnView( 5 , 15 );
sheet.setColumnView( 6 , 15 );
sheet.setColumnView( 7 , 20 );
if(isover!=null&&!isover.equals("")){
sheet.setColumnView( 8 , 20 );
sheet.setColumnView( 9 , 20 );
sheet.mergeCells(0, 0, 9, 0);
}else{
sheet.mergeCells(0, 0, 7, 0);
}
/** **********设置纵横打印（默认为纵打）、打印纸***************** */
jxl.SheetSettings sheetset = sheet.getSettings();
sheetset.setProtected(false);
/** ************设置单元格字体************** */
WritableFont TitleFont = new WritableFont(WritableFont.createFont("宋体"), 16,
WritableFont.BOLD);
WritableFont HeadFont = new WritableFont(WritableFont.createFont("宋体"), 14,
WritableFont.BOLD);
WritableFont NormalFont = new WritableFont(WritableFont.createFont("宋体"), 12);
/** ************设置时间、数字 格式************** */
DateFormat df=new jxl.write.DateFormat("yyyy/MM/dd HH:mm:ss");
NumberFormat nf = new jxl.write.NumberFormat("#.##");
/** ************以下设置三种单元格样式，灵活备用************ */
// 用于标题居中
WritableCellFormat wcf_title = new WritableCellFormat(TitleFont);
wcf_title.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_title.setAlignment(Alignment.CENTRE); // 文字水平对齐
wcf_title.setWrap(false); // 文字是否换行
// 用于标题头居中
WritableCellFormat wcf_head = new WritableCellFormat(HeadFont);
wcf_head.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_head.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_head.setAlignment(Alignment.CENTRE); // 文字水平对齐
wcf_head.setWrap(false); // 文字是否换行
// 用于正文居左
WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
wcf_left.setWrap(false); // 文字是否换行
// 用于时间单元格
WritableCellFormat wcf_date = new WritableCellFormat(NormalFont,df);
wcf_date.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_date.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_date.setAlignment(Alignment.LEFT); // 文字水平对齐
wcf_date.setWrap(false); // 文字是否换行
// 用于数字单元格
WritableCellFormat wcf_num = new WritableCellFormat(NormalFont,NumberFormats.FLOAT);
wcf_num.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_num.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_num.setAlignment(Alignment.LEFT); // 文字水平对齐
wcf_num.setWrap(false); // 文字是否换行
/** ***************以下是EXCEL开头大标题********************* */
if(contentTitle!=null&&!contentTitle.equals("")){
sheet.addCell(new Label(0, 0, contentTitle, wcf_title));
}
/** ***************以下是EXCEL第一行列标题********************* */
for (int i = 0; i < coloumTitle.length; i++) {
sheet.addCell(new Label(i, 1, coloumTitle[i], wcf_head));
}
/** ***************以下是EXCEL正文数据********************* */
int i = 2;
if(contentList!=null){
if(isover==null||isover.equals("")){
for (PassVehicle obj : contentList) {
sheet.addCell(new Label(0, i, obj.getName(), wcf_left));
sheet.addCell(new Label(1, i, obj.getPlateNo(), wcf_left));
sheet.addCell(new jxl.write.DateTime(2, i, obj.getPassTime(), wcf_date));
sheet.addCell(new Label(3, i, obj.getVehicleType(), wcf_left));
sheet.addCell(new jxl.write.Number(4, i, obj.getSelfWeight(), wcf_left));
sheet.addCell(new jxl.write.Number(5, i, obj.getLoadWeight(), wcf_left));
sheet.addCell(new jxl.write.Number(6, i, obj.getCommodityWeight(), wcf_num));
sheet.addCell(new Label(7, i, obj.getRemarks(), wcf_left));
i++;
}
}else{
for (PassVehicle obj : contentList) {
sheet.addCell(new Label(0, i, obj.getName(), wcf_left));
sheet.addCell(new Label(1, i, obj.getPlateNo(), wcf_left));
sheet.addCell(new jxl.write.DateTime(2, i, obj.getPassTime(), wcf_date));
sheet.addCell(new Label(3, i, obj.getVehicleType(), wcf_left));
sheet.addCell(new jxl.write.Number(4, i, obj.getLimitWeight(), wcf_num));
sheet.addCell(new jxl.write.Number(5, i, obj.getLoadWeight(), wcf_num));
sheet.addCell(new jxl.write.Number(6, i, obj.getOverWeight(), wcf_num));
sheet.addCell(new Label(7, i, obj.getCarOwer(), wcf_left));
sheet.addCell(new Label(8, i, obj.getMobile(), wcf_left));
sheet.addCell(new Label(9, i, obj.getRemarks(), wcf_left));
i++;
}
}
}
/** **********将以上缓存中的内容写到EXCEL文件中******** */
workbook.write();
/** *********关闭文件************* */
workbook.close();
} catch (Exception e) {
System.out.println("系统提示：Excel文件导出失败，原因："+ e.toString());
e.printStackTrace();
}
return SUCCESS;
}
相关资源下载地址：
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class JExcelUtils {
/**
* 生成Excel文件
* @param path 文件路径
* @param sheetName 工作表名称
* @param dataTitles 数据标题
*/
public void createExcelFile(String path,String sheetName,String[] dataTitles){
WritableWorkbook workbook;
try{
OutputStream os=new FileOutputStream(path);
workbook=Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0); //添加第一个工作表
initialSheetSetting(sheet);
Label label;
for (int i=0; i<dataTitles.length; i++){
//Label(列号,行号,内容,风格)
label = new Label(i, 0, dataTitles[i],getTitleCellFormat());
sheet.addCell(label);
}
//插入一行
insertRowData(sheet,1,new String[]{"200201001","张三","100","60","100","260"},getDataCellFormat(CellType.STRING_FORMULA));
//一个一个插入行
label = new Label(0, 2,"200201002",getDataCellFormat(CellType.STRING_FORMULA));
sheet.addCell(label);
label = new Label(1, 2,"李四",getDataCellFormat(CellType.STRING_FORMULA));
sheet.addCell(label);
insertOneCellData(sheet,2,2,70.5,getDataCellFormat(CellType.NUMBER));
insertOneCellData(sheet,3,2,90.523,getDataCellFormat(CellType.NUMBER));
insertOneCellData(sheet,4,2,60.5,getDataCellFormat(CellType.NUMBER));
insertFormula(sheet,5,2,"C3+D3+E3",getDataCellFormat(CellType.NUMBER_FORMULA));
//插入日期
mergeCellsAndInsertData(sheet, 0, 3, 5, 3, new Date(), getDataCellFormat(CellType.DATE));
workbook.write();
workbook.close();
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 初始化表格属性
* @param sheet
*/
public void initialSheetSetting(WritableSheet sheet){
try{
//sheet.getSettings().setProtected(true); //设置xls的保护，单元格为只读的
sheet.getSettings().setDefaultColumnWidth(10); //设置列的默认宽度
//sheet.setRowView(2,false);//行高自动扩展
//setRowView(int row, int height);--行高
//setColumnView(int col,int width); --列宽
sheet.setColumnView(0,20);//设置第一列宽度
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 插入公式
* @param sheet
* @param col
* @param row
* @param formula
* @param format
*/
public void insertFormula(WritableSheet sheet,Integer col,Integer row,String formula,WritableCellFormat format){
try{
Formula f = new Formula(col, row, formula, format);
sheet.addCell(f);
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 插入一行数据
* @param sheet 工作表
* @param row 行号
* @param content 内容
* @param format 风格
*/
public void insertRowData(WritableSheet sheet,Integer row,String[] dataArr,WritableCellFormat format){
try{
Label label;
for(int i=0;i<dataArr.length;i++){
label = new Label(i,row,dataArr[i],format);
sheet.addCell(label);
}
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 插入单元格数据
* @param sheet
* @param col
* @param row
* @param data
*/
public void insertOneCellData(WritableSheet sheet,Integer col,Integer row,Object data,WritableCellFormat format){
try{
if(data instanceof Double){
jxl.write.Number labelNF = new jxl.write.Number(col,row,(Double)data,format);
sheet.addCell(labelNF);
}else if(data instanceof Boolean){
jxl.write.Boolean labelB = new jxl.write.Boolean(col,row,(Boolean)data,format);
sheet.addCell(labelB);
}else if(data instanceof Date){
jxl.write.DateTime labelDT = new jxl.write.DateTime(col,row,(Date)data,format);
sheet.addCell(labelDT);
setCellComments(labelDT, "这是个创建表的日期说明！");
}else{
Label label = new Label(col,row,data.toString(),format);
sheet.addCell(label);
}
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 合并单元格，并插入数据
* @param sheet
* @param col_start
* @param row_start
* @param col_end
* @param row_end
* @param data
* @param format
*/
public void mergeCellsAndInsertData(WritableSheet sheet,Integer col_start,Integer row_start,Integer col_end,Integer row_end,Object data, WritableCellFormat format){
try{
sheet.mergeCells(col_start,row_start,col_end,row_end);// 左上角到右下角
insertOneCellData(sheet, col_start, row_start, data, format);
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 给单元格加注释
* @param label
* @param comments
*/
public void setCellComments(Object label,String comments){
WritableCellFeatures cellFeatures = new WritableCellFeatures();
cellFeatures.setComment(comments);
if(label instanceof jxl.write.Number){
jxl.write.Number num = (jxl.write.Number)label;
num.setCellFeatures(cellFeatures);
}else if(label instanceof jxl.write.Boolean){
jxl.write.Boolean bool = (jxl.write.Boolean)label;
bool.setCellFeatures(cellFeatures);
}else if(label instanceof jxl.write.DateTime){
jxl.write.DateTime dt = (jxl.write.DateTime)label;
dt.setCellFeatures(cellFeatures);
}else{
Label _label = (Label)label;
_label.setCellFeatures(cellFeatures);
}
}
/**
* 读取excel
* @param inputFile
* @param inputFileSheetIndex
* @throws Exception
*/
public ArrayList<String> readDataFromExcel(File inputFile, int inputFileSheetIndex){
ArrayList<String> list = new ArrayList<String>();
Workbook book = null;
Cell cell = null;
WorkbookSettings setting = new WorkbookSettings();
java.util.Locale locale = new java.util.Locale("zh","CN");
setting.setLocale(locale);
setting.setEncoding("ISO-8859-1");
try{
book = Workbook.getWorkbook(inputFile, setting);
}catch(Exception e){
e.printStackTrace();
}
Sheet sheet = book.getSheet(inputFileSheetIndex);
for (int rowIndex = 0; rowIndex < sheet.getRows(); rowIndex++) {//行
for (int colIndex = 0; colIndex < sheet.getColumns(); colIndex++) {//列
cell = sheet.getCell(colIndex, rowIndex);
//System.out.println(cell.getContents());
list.add(cell.getContents());
}
}
book.close();
return list;
}
/**
* 得到数据表头格式
* @return
*/
public WritableCellFormat getTitleCellFormat(){
WritableCellFormat wcf = null;
try {
//字体样式
WritableFont wf = new WritableFont(WritableFont.TIMES,12, WritableFont.NO_BOLD,false);//最后一个为是否italic
wf.setColour(Colour.RED);
wcf = new WritableCellFormat(wf);
//对齐方式
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//边框
wcf.setBorder(Border.ALL,BorderLineStyle.THIN);
//背景色
wcf.setBackground(Colour.GREY_25_PERCENT);
} catch (WriteException e) {
e.printStackTrace();
}
return wcf;
}
/**
* 得到数据格式
* @return
*/
public WritableCellFormat getDataCellFormat(CellType type){
WritableCellFormat wcf = null;
try {
//字体样式
if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA){//数字
NumberFormat nf = new NumberFormat("#.00");
wcf = new WritableCellFormat(nf);
}else if(type == CellType.DATE || type == CellType.DATE_FORMULA){//日期
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd hh:mm:ss");
wcf = new jxl.write.WritableCellFormat(df);
}else{
WritableFont wf = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);//最后一个为是否italic
wcf = new WritableCellFormat(wf);
}
//对齐方式
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//边框
wcf.setBorder(Border.LEFT,BorderLineStyle.THIN);
wcf.setBorder(Border.BOTTOM,BorderLineStyle.THIN);
wcf.setBorder(Border.RIGHT,BorderLineStyle.THIN);
//背景色
wcf.setBackground(Colour.WHITE);
wcf.setWrap(true);//自动换行
} catch (WriteException e) {
e.printStackTrace();
}
return wcf;
}
/**
* 打开文件看看
* @param exePath
* @param filePath
*/
public void openExcel(String exePath,String filePath){
Runtime r=Runtime.getRuntime();
String cmd[]={exePath,filePath};
try{
r.exec(cmd);
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args){
String[] titles = {"学号","姓名","语文","数学","英语","总分"};
JExcelUtils jxl = new JExcelUtils();
String filePath = "E:/test.xls";
jxl.createExcelFile(filePath," 成绩单",titles);
jxl.readDataFromExcel(new File(filePath),0);
jxl.openExcel("C:/Program Files/Microsoft Office/OFFICE11/EXCEL.EXE",filePath);
}
}
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import jxl.Cell;
import jxl.CellView;
import jxl.Sheet;
import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
* jxl操作excel的工具类.
*
*/
public class JxlTool {
public static int count = 1;
//存储带有级别信息的内容到位置的映射关系.
private static Map levelToLocation = new HashMap();
public static void readExcel(String fileName) {
Workbook wb = null;
try {
wb = Workbook.getWorkbook(new File(fileName));
Sheet[] sheets = wb.getSheets();
for(int i=0;i<sheets.length;i++){
Sheet ii = sheets[i];
System.out.println("第"+i+"个sheet的名字是"+ii.getName());
}
} catch (Exception e) {
System.out.println("出现异常" + e);
e.printStackTrace();
} finally {
wb.close();
}
}
private static String allChar = "abcdefghijklmnopqrstuvwxyz";
/**
* 从字符中得到列数.例如K-->10,A-->0,AA-->27
* @return
*/
public static int getNumFromExcelStr(String code)
{
int result = 0;
code = code.toLowerCase();
if(code.length()>1){
char[] c = code.toCharArray();
int len = c.length;
for(int i=0;i<len;i++){
if(i<len-1){
result+=(allChar.indexOf(c[i])+1)*26;
}else{
result+=allChar.indexOf(c[i])+1;
}
}
result-=1;
}
else
return allChar.indexOf(code);
return result;
}
/**
* 根据行号和列号得到所在的单元格.例如(3,4)-->"E4"
* @param vNum 纵坐标
* @param hNum 横坐标
* @return
*/
public static String getCellInfo(int hNum,int vNum){
char[] cs = allChar.toCharArray();
String hStr = "";
if(vNum>25){
hStr = String.valueOf(cs[vNum/26-1])+String.valueOf(cs[vNum%26-1]);
}else{
hStr = String.valueOf(cs[vNum]);
}
return (hStr+Integer.toString((hNum+1))).toUpperCase();
}
/**
* 得到一个字符串里面的字符.A12-->A
* @param oldStr
* @return
*/
public static String getCodeFromStr(String oldStr){
return oldStr.replaceAll("\\d", "");
}
/**
* 得到一个字符串里面的字符.A12-->12
* @param oldStr
* @return
*/
public static int getNumFromStr(String oldStr){
return Integer.parseInt(oldStr.replaceAll("[a-zA-Z]", ""))-1;
}
/**
* 读取指定excel中的指定sheet的某一块的数据....用于模板里面读取单元格.
* @param fileName
* @param sheetIndex
* @param startRow
* @param endRow
* @param startColumn
* @param endColumn
*/
public static List readExcel(String fileName, int sheetIndex, int startRow,
int endRow, int startColumn, int endColumn) {
Workbook wb = null;
List allData = new ArrayList();
Cell cell = null;
try {
wb = Workbook.getWorkbook(new File(fileName));
Sheet sheet = wb.getSheet(sheetIndex);
int rowCount = sheet.getRows();
int columnCount = sheet.getColumns();
for (int r = startRow; r < rowCount && r <= endRow; r++) {// 行
for (int c = startColumn; c < columnCount && c <= endColumn; c++) {// 列
cell = sheet.getCell(c, r);
// System.out.println(cell.getContents());
allData.add(cell.getContents());
}
}
} catch (Exception e) {
System.out.println("出现异常" + e);
e.printStackTrace();
} finally {
wb.close();
}
return allData;
}
/**
* 读取指定excel中的指定sheet的某一块的数据....用于模板里面读取单元格.
* @param fileName
* @param sheetIndex
* @param startCell
* @param endCell
* @return
*/
public static List readExcel(String fileName, int sheetIndex,String startCell, String endCell) {
int startRow = getNumFromStr(startCell);
int endRow = getNumFromStr(endCell);
int startColumn=getNumFromExcelStr(getCodeFromStr(startCell));
int endColumn = getNumFromExcelStr(getCodeFromStr(endCell));
return readExcel(fileName, sheetIndex, startRow, endRow, startColumn,
endColumn);
}
/**
* 设置excel中的sheet页全部隐藏
* @param fileName
*/
public static void setAllHiddenSheet(String fileName) {
Workbook wb = null;
try {
wb = Workbook.getWorkbook(new File(fileName));
// 打开一个文件副本,并指定数据写回原文件.
WritableWorkbook book = Workbook.createWorkbook(new File(fileName),
wb);
Sheet[] sheets = book.getSheets();
for(int i=3;i<sheets.length;i++){
Sheet ii = sheets[i];
ii.getSettings().setHidden(true);
}
book.write();
book.close();
} catch (Exception e) {
System.out.println("出现异常" + e);
e.printStackTrace();
} finally {
wb.close();
System.out.print(111);
}
}
/**
* 从行号和列号，得到所在的位置字符串，例如：row=7，col=7--->i8
*/
public static String getcodefromRC(int row,int col){
char[] cc = allChar.toCharArray();
return String.valueOf(cc[col])+(++row);
}
/**
* 添加一个新的sheet到指定excel文件
* @param fileName
* @param sheetName sheet的name
*/
public static void addNewSheet(String fileName,String sheetName) {
Workbook wb = null;
try {
wb = Workbook.getWorkbook(new File(fileName));
// 打开一个文件副本,并指定数据写回原文件.
WritableWorkbook book = Workbook.createWorkbook(new File(fileName),
wb);
// 创建一个新的sheet到第2页的位置
String[] sheetNames = wb.getSheetNames();
for(int i=0;i<sheetNames.length;i++){
if(sheetNames[i].equals(sheetName)){
System.out.println("已经存在了,不用添加了." );
return ;
}
}
WritableSheet sheet = book.createSheet(sheetName, 1);
sheet.addCell(new Label(0, 0, "新加的测试数据"));
book.write();
book.close();
} catch (Exception e) {
System.out.println("出现异常" + e);
e.printStackTrace();
} finally {
wb.close();
}
}
/**
* 得到单元格的double内容，不可以直接使用cell.getContents()，因为这个方法是直接打印单元格内容
，单元格内容可能隐藏了后面的小数点！！
*/
public static double getNumber(Cell cell){
NumberCell numberCell = (NumberCell)cell;
double namberValue = numberCell.getValue();
return namberValue ;
}
/**
* 如果是公式返回公式的内容，否则返回单元格字符串表面内容
* @param c
* @return
*/
public static String getForJmulaStr(Cell c) {
String ans = "";
try {
System.out.println(c.getType());
if (c.getType() == CellType.NUMBER_FORMULA
|| c.getType() == CellType.STRING_FORMULA
|| c.getType() == CellType.BOOLEAN_FORMULA
|| c.getType() == CellType.DATE_FORMULA
|| c.getType() == CellType.FORMULA_ERROR) {
FormulaCell nfc = (FormulaCell) c;
ans = nfc.getFormula();
} else {
ans = c.getContents();
}
} catch (FormulaException e) {
return "出现异常" + e.getMessage();
}
return ans;
}
//得到指定位置单元格的值(普通单元格,数字单元格,日期单元格)
private String getValue(Sheet sheet,int row,int col){
Cell cell=sheet.getCell(col, row);
CellType cellType=cell.getType();
NumberCell numberCell = null;
String cellValue = "";
//得到单元格的值
if (cellType == CellType.NUMBER) {
numberCell = (NumberCell) cell;
cellValue = String.valueOf(numberCell.getValue());
} else if (cellType == CellType.DATE) {
cellValue = df.format(((DateCell) cell).getDate());
} else if (cellType == CellType.NUMBER_FORMULA) {
// 形如：=123.232+3423.12
// 或者 =B2+123.12
NumberFormulaCell numberFormulaCell = (NumberFormulaCell) cell;
cellValue = String.valueOf(numberFormulaCell.getValue());
} else {
cellValue = cell.getContents();
}
cellValue=cellValue.replace(" ", "");
return cellValue;
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
