周末闲来无事就先把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 class="tableCon2" style="background:#fff;">
<tbody><tr>
<td><div class="importUserLayer">
<table>
<tbody><tr>
<td width="154" height="57" align="right" valign="top">选择需要导入的文件:</td>
<td width="434"><p>
<input type="file" id="myfiles" style="border: 1px solid #CCCCCC; height: 24px;background: #FFFFFF" name="myfiles" size="45">
</p>
<p>支持<font style="color:#FF0000">.XLS</font>格式 </p></td>
</tr>
<tr>
<td height="40" align="right">导入到组:</td>
<td>
<select name="groupId" id="group" class="textfield citySelect" style="width:82px;height:22px; text-indent:0px;">
</select></td>
</tr>
<tr>
<td colspan="2"><div class="Contactsindex">
<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