Java excel导入导出-CSDN博客

周末闲来无事就先把Java导入导出的整理出来

一、导出excel模板(只是创建excel格式，供别人填写，我刚学的方法还比较笨，希望谅解哈，以后有比较好的再补上)

1.1 代码如下我的都是应用于springmvc的示例

public class ExcelView extends AbstractExcelView {

String [] list={"男","女"};//生成的excel模板，对于性别就有下拉框可以选择了

@Override

protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {

//To change body of implemented methods use File | Settings | File Templates.

HSSFSheet hssfSheet = workbook.createSheet("客户基本信息");

hssfSheet.setDefaultColumnWidth(20);// 设置的每一列的默认宽度

HSSFCell hssfCell = getCell(hssfSheet,0,0);//声明第一行第一列

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);//该单元格的类型为String

setText(hssfCell,"客户名称");//该单元格的文本

hssfCell = getCell(hssfSheet,0,1);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"省(福建省)");

hssfCell = getCell(hssfSheet,0,2);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"市(厦门市)");

hssfCell = getCell(hssfSheet,0,3);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"区/悬");

hssfCell = getCell(hssfSheet,0,4);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"公司名称");

hssfSheet.setColumnWidth(4, 30);

hssfCell = getCell(hssfSheet,0,5);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"公司详细地址");

hssfSheet.setColumnWidth(5, 100);

hssfCell = getCell(hssfSheet,0,6);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell, "职工人数");

hssfCell = getCell(hssfSheet,0,7);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"所属行业");

hssfSheet.setColumnWidth(7,50);

hssfCell = getCell(hssfSheet,0,8);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"所属社区");

hssfCell = getCell(hssfSheet,0,9);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"登记人姓名");

hssfCell = getCell(hssfSheet,0,10);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

CellRangeAddressList regions = new CellRangeAddressList(0,1000,10,10);

DVConstraint constraint = DVConstraint.createExplicitListConstraint(list);//创建下拉选择

HSSFDataValidation data_validation = new HSSFDataValidation(regions,constraint);

hssfSheet.addValidationData(data_validation);

setText(hssfCell, "登记人性别");

hssfCell = getCell(hssfSheet,0,11);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"***号");

hssfCell = getCell(hssfSheet,0,12);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"生日");

hssfCell = getCell(hssfSheet,0,13);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"户籍");

hssfCell = getCell(hssfSheet,0,14);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"学历");

hssfCell = getCell(hssfSheet,0,15);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"毕业院校");

hssfCell = getCell(hssfSheet,0,16);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"政治面貌");

hssfCell = getCell(hssfSheet,0,17);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"手机号码");

hssfCell = getCell(hssfSheet,0,18);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"电话号码");

hssfCell = getCell(hssfSheet,0,19);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"邮箱");

hssfCell = getCell(hssfSheet,0,20);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"邮编");

hssfCell = getCell(hssfSheet,0,21);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"传真");

hssfCell = getCell(hssfSheet,0,22);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"爱好");

hssfCell = getCell(hssfSheet,0,23);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"职位");

hssfCell = getCell(hssfSheet,0,24);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"加入的社团或组织");

hssfCell = getCell(hssfSheet,0,25);

hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);

setText(hssfCell,"工作经历");

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-disposition", "p_w_upload;filename=" + URLEncoder.encode("客户基本信息模板.xls","utf-8"));

OutputStream ouputStream = response.getOutputStream();

workbook.write(ouputStream);

ouputStream.flush();

ouputStream.close();

}

Controller下的代码：

@RequestMapping(value = "download_template_excel.do")

public ModelAndView downLoadTemplateExcel(ModelMap modelMap){

ExcelView excelView = new ExcelView();

return new ModelAndView(excelView);

//如果我们也要传数据过去也是可以的，只需要将查询出来的LIST放在modelMap里面，然后通过new ModelAndView(excelView,modelMap)将数据传给excelView，在该类里面进行数据添加就可以了。

}

二、导入excel并解析得到数据

目前做的文件上传是直接点击button按钮就可以选择文件上传，通过iframe嵌入上传文件的表单

所引用的JS文件：

(function($) {

var noop = function(){ return true; };

var frameCount = 0;

$.uploadDefault = {

url: '',

fileName: 'filedata',

dataType: 'json',

params: {},

onSend: noop,

onComplate: noop

};

$.upload = function(options) {

var opts = $.extend(jQuery.uploadDefault, options);

if (opts.url == '') {

return;

}

var canSend = opts.onSend();

if (!canSend) {

return;

}

var frameName = 'upload_frame_' + (frameCount++);

var iframe = $('<iframe style="position:absolute;top:-9999px" />').attr('name', frameName);

var form = $('<form method="post" style="display:none;" enctype="multipart/form-data" />').attr('name', 'form_' + frameName);

form.attr("target", frameName).attr('action', opts.url);

// form中增加数据域

var formHtml = '<input type="file" name="' + opts.fileName + '">';

for (key in opts.params) {

formHtml += '<input type="hidden" name="' + key + '" value="' + opts.params[key] + '">';

}

form.append(formHtml);

iframe.appendTo("body");

form.appendTo("body");

// iframe 在提交完成之后

iframe.load(function() {

var contents = $(this).contents().get(0);

var data = $(contents).find('body').html();

var first = data.indexOf("{");

var last = data.lastIndexOf("}");

data = data.substring(first,last+1);

if ('json' == opts.dataType) {

data = window.eval('(' + data + ')');

}

opts.onComplate(data);

setTimeout(function() {

iframe.remove();

form.remove();

}, 5000);

});

// 文件框

var fileInput = $('input[type=file][name=' + opts.fileName + ']', form);

fileInput.change(function() {

form.submit();

});

fileInput.click();

};

})(jQuery);

页面的JS：

function doImportExcel(){

$.upload({

url:"import_crmCustBas.json",

params:{},

onComplate:function(data){

doSearch();

ChtUtil.getMessage().show("导入成功...");

}

});

}

action:

@RequestMapping(value = "import_crmCustBas.json",method = RequestMethod.POST)

public ModelMap importExcel(ModelMap modelMap,MultipartHttpServletRequest request)throws IOException, BasException {

List<MultipartFile> files = request.getFiles("filedata");

if(files!=null){

for (MultipartFile file : files) {

InputStream in = file.getInputStream();

//解析Excel

crmCustBasServiceImpl.importExcel(in);

}

modelMap = SpringModelTools.convertAjaxSuccess(modelMap);

return modelMap;

}

service:

public boolean importExcel(InputStream inputStream)throws IOException, BasException {

boolean flag = true;

List<Map<String,String>> list = UtilExcel.resolveExcel("custName,provinceId,cityId,townId,enterpriseName,enterpriseAddress,countEmployer,industry,community,username,sex,idCardNo,birthday,houseHold,degreeEducation,graduateSchool, politics,mobilePhone,phone,email,zip,chuanZhen,enjoy,position,joinOrgnise,workExperience", inputStream, 0, new HashMap<String,String>(), 1);

if(list.size()>0){

for(Map<String,String> map :list){

CrmCustBas crmCustBas = new CrmCustBas();

for(String key:map.keySet()){

if("custName".equals(key)){

crmCustBas.setCustName(map.get(key));

}else if("provinceId".equals(key)){

BsmArea bsmAreaProvinceId = basDao.queryOne(BsmArea.class,"name",map.get(key));

if(bsmAreaProvinceId!=null){

crmCustBas.setProvinceId(bsmAreaProvinceId.getId());

}else {

crmCustBas.setProvinceId("0");

}

}else if("cityId".equals(key)){

BsmArea bsmAreaCityId = basDao.queryOne(BsmArea.class,"name",map.get(key));

if(bsmAreaCityId!=null){

crmCustBas.setCityId(bsmAreaCityId.getId());

}else {

crmCustBas.setCityId("0");

}

}else if("townId".equals(key)){

BsmArea bsmAreaTownId = basDao.queryOne(BsmArea.class,"name",map.get(key));

if(bsmAreaTownId!=null){

crmCustBas.setTownId(bsmAreaTownId.getId());

}else {

crmCustBas.setTownId("0");

}

}else if("countEmployer".equals(key)){

String count = map.get(key);

Integer countEmployer = Integer.parseInt(count);

crmCustBas.setCountEmployer(countEmployer);

}else if("community".equals(key)){

crmCustBas.setCommunity(map.get(key));

}else if("username".equals(key)){

crmCustBas.setUsername(map.get(key));

}else if("sex".equals(key)){

if("男".equals(map.get(key))){

crmCustBas.setSex("0");

}else {

crmCustBas.setSex("1");

}

}else if("idCardNo".equals(key)){

crmCustBas.setIdCardNo(map.get(key));

}else if("birthday".equals(key)){

String str = map.get(key);

Date birthday = UtilDate.parseDate(str);

crmCustBas.setBirthday(birthday);

}else if("houseHold".equals(key)){

crmCustBas.setHouseHold(map.get(key));

}else if("degreeEducation".equals(key)){

crmCustBas.setDegreeEducation(map.get(key));

}else if("graduateSchool".equals(key)){

crmCustBas.setGraduateSchool(map.get(key));

}else if("politics".equals(key)){

crmCustBas.setPolitics(map.get(key));

}else if("mobilePhone".equals(key)){

crmCustBas.setMobilePhone(map.get(key));

}else if("phone".equals(key)){

crmCustBas.setPhone(map.get(key));

}else if("email".equals(key)){

crmCustBas.setEmail(map.get(key));

}else if("zip".equals(key)){

crmCustBas.setZip(map.get(key));

}else if("chuanZhen".equals(key)){

crmCustBas.setChuanZhen(map.get(key));

}else if("enjoy".equals(key)){

crmCustBas.setEnjoy(map.get(key));

}else if("position".equals(key)){

crmCustBas.setPosition(map.get(key));

}else if("joinOrgnise".equals(key)){

crmCustBas.setJoinOrgnise(map.get(key));

}else if("workExperience".equals(key)){

crmCustBas.setWorkExperience(map.get(key));

}

if(!"".equals(crmCustBas.getMobilePhone())&&crmCustBas.getMobilePhone()!=null){

QueryCondition queryCondition = new QueryCondition();

queryCondition.setQueryObject(CrmCustBas.class);

queryCondition.addField(new QueryField("mobilePhone",crmCustBas.getMobilePhone(),QueryField.CONDITION_EQ));

queryCondition.addField(new QueryField("custName",crmCustBas.getCustName(),QueryField.CONDITION_EQ));

List<CrmCustBas> haveList = basDao.query(queryCondition);

if(haveList.size()>0){

continue;

}else{

crmCustBas.setCustNo(getNo());

crmCustBas.setCustPoint(0);

basDao.save(crmCustBas);

}

return flag;

}

UtilExcel:

public static List resolveExcel(String arrayCloumns,InputStream inputStream,int sheetCount,Object object,int startRows) throws BasException{

List list=new ArrayList();

Workbook book =null;

try {

//解析模板为,号分割串

book = Workbook.getWorkbook(inputStream);

Sheet sheet = book.getSheet(sheetCount);

//列数

int columnCount=sheet.getColumns();

//行数

int rowCount=sheet.getRows();

Cell cell = null;

String[] arraycol=arrayCloumns.split(",");

for (int i = startRows; i <rowCount; i++) {

Object obj=UtilBean.getNewClass(object.getClass());

for (int j = 0; j <columnCount; j++) {

String arrcol=arraycol[j];

cell =sheet.getCell(j, i);

String val=cell.getContents();

//如果是日期类型

if(cell.getType() == CellType.DATE){

DateCell datec11 = (DateCell)cell;

Date date = datec11.getDate();

val = UtilDate.formatDate(date);

}

//如果是金额类型

if(val.contains("$")){

val = val.replace("$", "").replace(",", "");

}

if(object instanceof Map){

((Map)obj).put(arrcol,val);

}

else{

BeanUtils.setProperty(obj, arrcol, val);

}

list.add(obj);

}

}catch (Exception e) {

log.error("excel解析错误,错误消息："+e.getMessage());

throw new BasRuntimeException("excel解析错误",e);

}finally{

if(book!=null){

book.close();

}

return list;

}

以上一二是我在CRM用的方式，接下来是我在活动平台项目所用的方式，其实也差不多，但是为了让自己可以记住，还是将其写下来了。

三、导入excel

<form id="formUpload" name="formUpload" action="<%=request.getContextPath()%>/import_linkman_excel.do" method="post" enctype="multipart/form-data">

<table>

<td width="154" height="57" align="right" valign="top">选择需要导入的文件：</td>

</p>

</tr>

<tr>

<td>

</select></td>

</tr>

<tr>

<p><strong> 提示：导入文件需满足三个条件：</strong></p>

<p style=" padding-left:20px;"> a) 文件第一行必须为每列数据的名称，例如：姓名、手机、邮件地址、职位、部门等。</p>

<p style=" padding-left:20px;"> b) 导入信息必须包含姓名、手机或mail。</p>

<p style=" padding-left:20px;"> c) 文件为.csv或.xls格式。目前不支持Excel2007或更高版本的xlsx文件，请另存为Excel2003格式或者.csv格式。</p>

</div></td>

</tr>

</tbody></table>

</div></td>

</tr>

</tbody></table>

</form>

function saveFile(){

$("#formUpload").submit();

}

action:

@RequestMapping(value = "import_linkman_excel.do")

public String importExcel(@RequestParam(value = "groupId",required = false)String groupId,@RequestParam MultipartFile[] myfiles,HttpServletRequest request,HttpServletResponse response){

MultipartFile imgFileName = myfiles[0];

String booleanString = "";

String fileName = imgFileName.getOriginalFilename();

if(fileName!=null||!"".equals(fileName)){

String ext = fileName.substring(fileName.lastIndexOf(".")+1,fileName.length());

//对扩展名进行小写转换

ext = ext.toLowerCase();

if("xls".equals(ext)){

try {

List<CommLinkmanInfoVO> list = UtilExcel.resolveExcel("linkmanName,sex,email,phone,mobilePhone,fax,zipCode,company,deptName,industryTypeNo,position,address,qq",imgFileName.getInputStream(),0,new CommLinkmanInfoVO(),1);

if(list.size()>0){

for(CommLinkmanInfoVO commLinkmanInfoVO : list){

List<CommLinkmanInfoVO> haveList = commLinkmanInfoServiceImpl.queryListByGroupId(groupId);

if(haveList.size()>0){

for(CommLinkmanInfoVO equalVO : haveList){

if(equalVO.getPhone()==commLinkmanInfoVO.getPhone()){

//预留已经存在的返回消息

continue;

}else {

commLinkmanInfoVO.setGroupId(groupId);

commLinkmanInfoServiceImpl.saveLinkman(commLinkmanInfoVO);

}

}else{

commLinkmanInfoVO.setGroupId(groupId);

commLinkmanInfoServiceImpl.saveLinkman(commLinkmanInfoVO);

}

} catch (Exception e) {

e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.

}

return "redirect:/comm_linkman_index.do?groupId="+groupId;

}

四导出excel

@RequestMapping(value = "export_select_link.do")

public String exportSelect(@RequestParam(value="delistJson",required = false)String delistJson,@RequestParam(value="groupId",required = false)String groupId,

@RequestParam(value="groupName",required = false)String groupName,HttpServletResponse response,HttpServletRequest request

){

List<IdVO> idVOs = JsonUtils.getList(delistJson,IdVO.class);

List<CommLinkmanInfoVO> list = new ArrayList<CommLinkmanInfoVO>();

if(idVOs.size()>0){

List<String> eventListId = new ArrayList<String>();

for(int i=0;i< idVOs.size();i++){

String id = idVOs.get(i).getId();

eventListId.add(id);

}

list = commLinkmanInfoServiceImpl.queryListByListId(eventListId);

try {

groupName = new String(groupName.getBytes("iso-8859-1"),"utf-8");

if(groupName==null||"".equals(groupName)){

groupName = "所选联系人";

}

} catch (UnsupportedEncodingException e) {

e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.

}

String uploadPath = "/upload";

String realUploadFilePath = request.getSession().getServletContext().getRealPath(uploadPath);

String filePath = realUploadFilePath+ File.separator+groupName + "名单.xls";

try {

UtilExcel.writeListToExcel(groupName + "名单", list, filePath, "linkmanName,sex,email,phone,mobilePhone,fax,zipCode,company,deptName,industryTypeNo,position,address,qq;", "名字,性别,邮箱,电话,手机,传真,邮编,公司,部门,行业,职位,地址,QQ");

String filename = groupName+"名单"+".xls";

filename = new String(filename.getBytes("utf-8"),"iso-8859-1");

response.reset();

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-disposition", "p_w_upload;filename="+filename);

OutputStream ouputStream = response.getOutputStream();

InputStream inputStream = new FileInputStream(new File(filePath));

byte buffer[]=new byte[4*1024];

int len = 0;

while((len = inputStream.read(buffer)) != -1)//

{

ouputStream.write(buffer,0,len);

}

ouputStream.flush();

ouputStream.close();

UtilFile.delFile(filePath);

} catch (Exception e) {

e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.

}

return null;

}

public static void writeListToExcel(String titleName,List list, String filename, String sheetName, String columnNames,

String columnDisplayNames, boolean isDeleteFile, String sumOnColumn, String promptStringForSum,String conditions){

//入参校验

if (list == null || UtilString.isEmpty(filename) || UtilString.isEmpty(columnNames)

|| UtilString.isEmpty(columnDisplayNames)){

throw new BasRuntimeException("参数错误，请保证参数为非空。");

}

String[] columnName = columnNames.split(",");

String[] columnDisplayName = columnDisplayNames.split(",");

Workbook rwBook=null;

WritableWorkbook book = null;

if(UtilString.isEmpty(sheetName)){

sheetName = "sheet1";

}

int numberOfFields = columnName.length, result = 0;

int[] columnWidths = new int[numberOfFields];

try {

String path =filename.substring(0,filename.lastIndexOf("\\"));

if(path!=null&&!"".equals(path)){

File tmp = new File(path);

if(!tmp.isDirectory()){

tmp.mkdirs();

}

File file = new File(filename);

if(isDeleteFile && file.exists())

file.delete();

//创建一个可写的工作簿

if(file.exists()) {

rwBook = Workbook.getWorkbook(file);

book = Workbook.createWorkbook(file, rwBook);

} else{

book = Workbook.createWorkbook(file);

}

int workingSheetNo = file.exists() ? book.getNumberOfSheets() + 1 : 1;

//添加一个工作表

WritableSheet ws = book.createSheet(sheetName, workingSheetNo);

//输出大标题

ws.mergeCells(0, 0, numberOfFields-1, 0);

ws.setRowView(0, 500);

Label labelC00 = new Label(0, 0, titleName, getBigTitleStyle(16));

ws.addCell(labelC00);

//添加带有字型Formatting的对象,用来输出标题行

int k = 1,row=2;

if(!UtilString.isEmpty(conditions)){

k=2;

row=3;

ws.mergeCells(0, 1, numberOfFields-1, 1);

ws.setRowView(0, 500);

Label labelC01 = new Label(0, 1, conditions, getBigTitleStyle(16));

ws.addCell(labelC01);

}

//输出列头

for (int j = 0; j < numberOfFields; j++) {

//设置列宽度

jxl.write.Label label = new jxl.write.Label(j, k, columnDisplayName[j], getColumnHeadStyle());

ws.addCell(label);

columnWidths[j] = columnDisplayName[j].length();

}

//创建数字单元格式对象

jxl.write.NumberFormat nf = new jxl.write.NumberFormat(DECIMAL_FORMAT);

jxl.write.WritableCellFormat wcfNumber = new jxl.write.WritableCellFormat(nf);

//创建TimeStamp单元格式对象

jxl.write.DateFormat dfTS = new jxl.write.DateFormat(DATE_FORMAT+ " hh:mm:ss");

jxl.write.WritableCellFormat wcfTimestamp = new jxl.write.WritableCellFormat(dfTS);

//创建日期单元格式对象

jxl.write.DateFormat dfDate = new jxl.write.DateFormat(DATE_FORMAT);

jxl.write.WritableCellFormat wcfDate = new jxl.write.WritableCellFormat(dfDate);

int indexForSumColumn = 0;

double total = 0;

boolean isNeededSum = !UtilString.isEmpty(sumOnColumn);

//将List中的对象输出到excel

for (int i = 0; i < list.size(); i++) {

//Map map = (HashMap) list.get(i);//List中的每个对象都是hashMap,map包含字段名和值

Object _object = list.get(i);

if (_object == null)

continue;

Map map=null;

Object vo = null;

if(_object instanceof Map){//判断该List对象是Map还是VO

map = (HashMap) list.get(i);

}else{

vo = list.get(i);

}

for (int column = 0; column < numberOfFields; column++) {

//当对象为空时，如果继续调用jxl的API，经常会出异常，所以先检测

Object obj = null;

if(map!=null){

obj= map.get(columnName[column]);

} else if(vo!=null){

obj = UtilBean.getProperty(vo, columnName[column]);

}

if (obj == null)

continue;

//如果需要统计，将从map中读到的值累加到total,同时设置统计列的位置，用于最后输出统计信息

if (isNeededSum) {

if (columnName[column].equals(sumOnColumn)) {

total += Double.parseDouble(obj.toString());

indexForSumColumn = column;

}

//设置列的宽度值

if(obj.toString().length() > columnWidths[column])

columnWidths[column] = obj.toString().length();

if (obj instanceof String) {

jxl.write.Label label = new jxl.write.Label(column, row, (String) obj);

ws.addCell(label);

} else if ((obj instanceof Integer)) {

jxl.write.Number number = new jxl.write.Number(column, row, ((Integer) obj).doubleValue(), wcfNumber);

ws.addCell(number);

} else if (obj instanceof Timestamp) {

jxl.write.DateTime datetime = new jxl.write.DateTime(column, row, (Timestamp) obj, wcfTimestamp);

ws.addCell(datetime);

} else if (obj instanceof BigDecimal) {

jxl.write.Number number = new jxl.write.Number(column, row, ((BigDecimal) obj).doubleValue(), wcfNumber);

ws.addCell(number);

} else if (obj instanceof Double) {

jxl.write.Number number = new jxl.write.Number(column, row, ((Double) obj).doubleValue(), wcfNumber);

ws.addCell(number);

} else if (obj instanceof Float) {

jxl.write.Number number = new jxl.write.Number(column, row, ((Float) obj).doubleValue(), wcfNumber);

ws.addCell(number);

} else if ((obj instanceof Long)) {

jxl.write.Number number = new jxl.write.Number(column, row, ((Long) obj).doubleValue(), wcfNumber);

ws.addCell(number);

} else if ((obj instanceof Short)) {

jxl.write.Number number = new jxl.write.Number(column, row, ((Short) obj).doubleValue(), wcfNumber);

ws.addCell(number);

} else if (obj instanceof Date) {

jxl.write.DateTime datetime = new jxl.write.DateTime(column, row, (Date) obj, wcfDate);

ws.addCell(datetime);

} else if (obj instanceof Boolean) {

jxl.write.Boolean labelB = new jxl.write.Boolean (column, row, ((Boolean)obj).booleanValue());

ws.addCell(labelB);

} else {

//把其他类型都作为字符串处理

jxl.write.Label label = new jxl.write.Label(column, row, (String) obj);

ws.addCell(label);

}

row++;

}

//输出统计信息

if (isNeededSum) {

jxl.write.Label labelPrompt = new jxl.write.Label(indexForSumColumn - 1, row + 1, promptStringForSum);

ws.addCell(labelPrompt);

jxl.write.Number number = new jxl.write.Number(indexForSumColumn, row + 1, total, wcfNumber);

ws.addCell(number);

//更新汇总信息的列宽度

if(promptStringForSum.length() > columnWidths[indexForSumColumn - 1])

columnWidths[indexForSumColumn - 1] = promptStringForSum.length();

if(String.valueOf(total).length() > columnWidths[indexForSumColumn])

columnWidths[indexForSumColumn] = String.valueOf(total).length();

}

//调整列的宽度

for (int i = 0; i < columnWidths.length; i++) {

//设定列的最大最小宽度为200和6

if(columnWidths[i] > 100)

columnWidths[i] = 100;

if(columnWidths[i] < 6){

columnWidths[i] = 6;

}

ws.setColumnView(i, columnWidths[i]+1);

}

} catch (Exception e) {

log.error("写Excel失败。" + e.getMessage());

throw new BasRuntimeException("写入Excel失败",e);

} finally {

try {

if (book != null) {

book.write();

book.close();

if(rwBook!=null){

rwBook.close();

}

} catch (Exception e) {

}

转载于:https://blog.51cto.com/9686567/1591993