一、Excel文件的导入
1、action内容
/**
* <基站信息导出excel>
* <功能详细描述>
* @param request
* @param response
* @see [类、类#方法、类#成员]
*/
@RequestMapping("/exportAllForExcel.action")
public void exportAllForExcel(HttpServletRequest request, HttpServletResponse response)
{
try
{
// 设置excel表格的名称
String name = Global.getTime("yyyyMMdd", new Date());
// 设置响应消息头
response.setContentType("text/excel");
response.setHeader("Content-disposition", "attachment; filename=" + name + ".xlsx");
// 登录名
String spName = request.getParameter("spName");
// 定义输出流
OutputStream os = null;
// 创建excel表格类,调用导出方法
Workbook workBook = contactService.exportContactForExcel(spName);
// 写出文件
os = response.getOutputStream();
workBook.write(os);
os.flush();
os.close();
}
catch (Exception e)
{
logger.info(Global.EXCEPTION_NAME, e);
}
}
2、service代码
/**
* {@inheritDoc}
*/
@Override
public Workbook exportContactForExcel(String spName)
{
// 声明一个工作薄
XSSFWorkbook workBook = new XSSFWorkbook();
// 生成一个工作表
XSSFSheet sheet = workBook.createSheet();
// 给sheet赋值
workBook.setSheetName(0, "通讯录");
// 生成一行
Row row = sheet.createRow(0);
// 锁定0行一列
sheet.createFreezePane(0, 1);
// 设置列单元格宽度(根据创建的单元格多少来设定多少列)
for (int i = 0; i < 10; ++i)
{
sheet.setColumnWidth(i, 7200);
}
// 给exlce表格头的字段赋值
createCell(row, 0, "姓名");
createCell(row, 1, "号码1");
createCell(row, 2, "号码2");
createCell(row, 3, "号码3");
createCell(row, 4, "传真1");
createCell(row, 5, "传真2");
createCell(row, 6, "公司名称");
createCell(row, 7, "邮箱1");
createCell(row, 8, "邮箱2");
createCell(row, 9, "QQ号码");
// 获取待导出的通讯录对象
List<TDTM_SPID_CONTACTS> conList = Global.contactMap.get(spName);
if (null != conList && conList.size() > 0)
{
// 行对象
Row row2 = null;
// 通讯录对象
TDTM_SPID_CONTACTS tdtm = null;
// 循环创建EXCEL行
for (int i = 0; i < conList.size(); i++)
{
// 创建一个空白行
row2 = sheet.createRow(i + 1);
// 通讯录对象
tdtm = conList.get(i);
// 姓名
if (Global.isEmpty(tdtm.getContactName()))
{
createCell(row2, 0, " ");
}
else
{
createCell(row2, 0, tdtm.getContactName());
}
// 号码集合
List<String> msnList = tdtm.getMsnList();
// 构造有效号码列
if (null != msnList)
{
for (int j = 0; j < msnList.size(); j++)
{
if (Global.isEmpty(msnList.get(j)))
{
createCell(row2, (1 + j), " ");
}
else
{
createCell(row2, (1 + j), msnList.get(j));
}
}
}
// 传真集合
List<String> faxList = tdtm.getFaxList();
// 构造有效传真列
if (null != faxList)
{
for (int j = 0; j < faxList.size(); j++)
{
if (Global.isEmpty(faxList.get(j)))
{
createCell(row2, (4 + j), " ");
}
else
{
createCell(row2, (4 + j), faxList.get(j));
}
}
}
// 公司名称
if (Global.isEmpty(tdtm.getCompany()))
{
createCell(row2, 6, " ");
}
else
{
createCell(row2, 6, tdtm.getCompany());
}
// 邮箱列表
List<String> emailList = tdtm.getEmailList();
// 构造有效邮箱列
if (null != emailList)
{
for (int j = 0; j < emailList.size(); j++)
{
if (Global.isEmpty(emailList.get(j)))
{
createCell(row2, (7 + j), " ");
}
else
{
createCell(row2, (7 + j), emailList.get(j));
}
}
}
// QQ号码
if (Global.isEmpty(tdtm.getQq()))
{
createCell(row2, 9, " ");
}
else
{
createCell(row2, 9, tdtm.getQq());
}
}
}
return workBook;
}
/**
* <创建表格公共方法>
* <功能详细描述>
* @param row
* @param col
* @param value
* @see [类、类#方法、类#成员]
*/
private void createCell(Row row, int col, String value)
{
// 创建单元格
Cell cell = row.createCell(col);
// 设置所有单元格为字符串格式
cell.setCellType(Cell.CELL_TYPE_STRING);
// 给单元格赋值
cell.setCellValue(value);
}
二、Excel文件的导入
1、利用ajax导入,控件使用的是ajaxFileUpload
<input type="button" value="导入" οnclick="importContact();" /> // 导入通讯录窗口 var importBox = null; /** * <通过excel导入通讯录> * <功能详细描述> * @see [类、类#方法、类#成员] */ function importContact() { // 通讯录导入表单 var html_content = '<div align="center" style="font-size:12px;width: 320px; height: 100px;background-color: #A5CBF7;margin: 0;padding: 0;">'; html_content += '</br>'; html_content += '类型: '; html_content += '<input type="radio" id="insertCon" name="conType" checked />追加'; html_content += ' '; html_content += '<input type="radio" id="coverCon" name="conType" />覆盖'; html_content += '</br></br>'; html_content += '<input type="file" id="file_upload" name="file_upload" value="" />'; html_content += '<input type="button" id="fileUpload" value="上传" />'; html_content += '</div>'; // 构造导入通讯录窗口 importBox = new Boxy(html_content, { title : '<table width="100%"><tr><td style="color:#ccc"><b>导入通讯录</b></td>' + '<td width="70%" align="right"><img style="cursor:pointer" class="createContactClose" title="关闭" src="' + path + '/images/exit.png" /></td></tr></table>', closeable : false, modal : true }); // 关闭通讯录窗口 $(".createContactClose").click(function() { importBox.hide(); }); // 绑定上传按钮单击事件 $("#fileUpload").click(function() { ajaxFileUpload(); }); } /** * <上传文件> * <功能详细描述> * @see [类、类#方法、类#成员] */ function ajaxFileUpload() { // 导入类型 cover-覆盖 insert-追加 var conType = 'insert'; if($('#coverCon').attr('checked')) { conType = 'cover'; } // 文件 var file_upload = $('#file_upload').val(); if(null == file_upload || '' == file_upload) { alert('请选择需要导入的文件'); return; } // 判断文件类型 var file_suffix = file_upload.split('.'); var suffix_name = file_suffix[file_suffix.length - 1].toLowerCase(); if('xls' != suffix_name && "xlsx" != suffix_name) { alert('导入的文件必须是EXCEL'); return; } // 绑定文件选择框控件 $.ajaxFileUpload( { url : path + '/dataFileUpload.action?spName=' + loginName + '&type=' + conType, secureuri:false, fileElementId:'file_upload', dataType: 'json', success: function (data, status) { // 转换成json对象 var json = data[0]; // 实际操作结果 var result = json['result']; // 根据返回结果提示用户 if("0" == result) { imgContactPage("first", "import") alert("导入成功"); importBox.hide(); } else if("1" == result) { alert("服务器异常"); } }, error: function (data, status, e) { } }); }
2、servlet代码(利用ajax控件导入文件不能使用spring接收,必须自己写servlet)
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItemIterator;
import org.apache.commons.fileupload.FileItemStream;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.blingtel.service.impl.ContactsServiceImpl;
import com.blingtel.util.Global;
/**
* <数据文件批量上传Servlet>
* <功能详细描述>
* @author Administrator
* @version [版本号, 2012-11-28]
* @see [相关类/方法]
* @since [产品/模块版本]
*/
public class DataFileUpLoadServlet extends HttpServlet
{
/**
* 序列号
*/
private static final long serialVersionUID = 1L;
/**
* 日志对象
*/
private Log logger = LogFactory.getLog(DataFileUpLoadServlet.class);
// 上传文件的保存路径,相对于应用的根目录
byte[] imgBufTemp = new byte[102401];
/**
* SERVLET初始化方法
* {@inheritDoc}
*/
public void init(ServletConfig config)
throws ServletException
{
}
/**
* 接收POST请求
* {@inheritDoc}
*/
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
// 响应对象
PrintWriter out = null;
try
{
// 页面输出对象
out = response.getWriter();
// 设置输出内容类型
response.setContentType("text/html;charset=UTF-8");
// 解析文件上传请求并把文件保存到本地
defaultProcessFileUpload(request, response);
out.write("[{\"data\":[],\"result\":\"0\"}]");
out.flush();
out.close();
}
catch (Exception e)
{
// 输出日志
e.printStackTrace();
logger.error(Global.EXCEPTION_NAME, e);
out.write("[{\"data\":[],\"result\":\"1\"}]");
out.flush();
out.close();
}
}
/**
* 接收GET请求
* {@inheritDoc}
*/
@Override
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
doPost(request, response);
}
/**
* <通讯录文件上传方法>
* <功能详细描述>
* @param request
* @param response
* @throws IOException
* @see [类、类#方法、类#成员]
*/
private String defaultProcessFileUpload(HttpServletRequest request, HttpServletResponse response)
throws Exception
{
// 文件上传对象
ServletFileUpload upload = new ServletFileUpload();
upload.setHeaderEncoding("UTF-8");
// SP Name
String spName = request.getParameter("spName");
// 操作类型
String type = request.getParameter("type");
// 输入输出流
InputStream stream = null;
BufferedOutputStream bos = null;
// 组装文件保存路径
String folderPath = request.getServletContext().getRealPath("/WEB-INF/");
String savePath = folderPath + "/temp/";
// 判断请求类型是否是文件上传
if (ServletFileUpload.isMultipartContent(request))
{
// 文件上传集合
FileItemIterator iter = upload.getItemIterator(request);
// 输出文件对象
File file = null;
// 迭代该集合,获取所有上传的文件
while (iter.hasNext())
{
// 获取上传的文件流
FileItemStream item = iter.next();
stream = item.openStream();
// 如果是文件上传
if (!item.isFormField())
{
// 构造输出流
file = new File(savePath + item.getName());
bos = new BufferedOutputStream(new FileOutputStream(file));
// 把上传文件流内容写入到输出流
int length = -1;
while ((length = stream.read(imgBufTemp)) != -1)
{
bos.write(imgBufTemp, 0, length);
}
}
}
// 关闭流操作
if (null != stream)
{
stream.close();
}
if (null != bos)
{
bos.close();
}
// 导入通讯录业务处理
ContactsServiceImpl contact = new ContactsServiceImpl();
return contact.importContact(type, spName, file);
}
return "[{\"data\":[],\"result\":\"1\"}]";
}
}
注:在web.xml中的配置如下:
<servlet> <servlet-name>fileupload</servlet-name> <servlet-class>com.blingtel.action.DataFileUpLoadServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>fileupload</servlet-name> <url-pattern>/dataFileUpload.action</url-pattern> </servlet-mapping>
3、service业务处理
/**
* {@inheritDoc}
* @throws IOException
* @throws FileNotFoundException
*/
@Override
public String importContact(String type, String spName, File file)
throws FileNotFoundException, IOException
{
// SP名称不为空且文件存在
if (!Global.isEmpty(spName) && null != file)
{
// 获取登录的SP对象
SP_USER_CONFIG sp = Global.spLoginUserMap.get(spName);
if (null != sp)
{
// 当前通讯录列表
List<TDTM_SPID_CONTACTS> conList = Global.contactMap.get(spName);
if (null == conList)
{
conList = new ArrayList<TDTM_SPID_CONTACTS>();
}
// 添加更新通讯录消息类
OperateContactsMessage operate = new OperateContactsMessage();
// 覆盖类型,需要先删除原有数据
if ("cover".equals(type))
{
clearAllContacts(sp.getSpID(), spName, operate, conList);
}
// 创建对Excel工作簿文件的引用
Workbook workbook = createWorkbook(new FileInputStream(file));
// 获取第一个sheet页
Sheet sheet = workbook.getSheetAt(0);
// 获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
// 通讯录操作响应对象
DWS_TDTM_OPERATE_CONTACTS_RSP_ST rsp = null;
// 循环构造通讯录对象
TDTM_SPID_CONTACTS tdtm = null;
for (int i = 1; i < rows; i++)
{
// excel行对象
Row row = sheet.getRow(i);
// 组装通讯录对象
tdtm = assemblingContact(row);
// 添加通讯录
rsp = operate.operateContacts(sp.getSpID(), spName, 1, tdtm);
// 如果不为空说明添加成功
if (null != rsp && 0 == rsp.getUlResult())
{
conList.add(rsp.getStContacts());
}
}
// 把更新后的通讯录列表放到上下文中
Global.contactMap.put(spName, conList);
// 返回成功JSON串
return "[{\"data\":[],\"result\":\"0\"}]";
}
}
return "[{\"data\":[],\"result\":\"1\"}]";
}
/**
* <根据输入流创建excel工作薄引用>
* <功能详细描述>
* @param ins
* @return
* @throws IOException
* @see [类、类#方法、类#成员]
*/
private Workbook createWorkbook(InputStream ins)
throws IOException
{
// 测试此输入流是否支持 mark 和 reset 方法
// InputStream 的 markSupported 方法返回 false
if (!ins.markSupported())
{
// PushbackInputStream 为另一个输入流添加性能,
// 即“推回 (push back)”或“取消读取 (unread)”一个字节的能力
ins = new PushbackInputStream(ins, 8);
}
// 工作薄引用
Workbook workbook = null;
// 符合该项说明Excel是2003
if (POIFSFileSystem.hasPOIFSHeader(ins))
{
workbook = new HSSFWorkbook(ins);
}
// 符合该项说明Excel是2007
else if (POIXMLDocument.hasOOXMLHeader(ins))
{
workbook = new XSSFWorkbook(ins);
}
return workbook;
}
/**
* <根据EXCEL中一行数据组装通讯录对象>
* <功能详细描述>
* @param row 行对象
* @return 通讯录对象
* @see [类、类#方法、类#成员]
*/
private TDTM_SPID_CONTACTS assemblingContact(Row row)
{
if (null == row)
{
return null;
}
// 整型格式化对象
DecimalFormat df = new DecimalFormat("#");
// 通讯录对象
TDTM_SPID_CONTACTS tdtm = new TDTM_SPID_CONTACTS();
// 姓名
int cell_type = Cell.CELL_TYPE_STRING;
if (!Global.isEmpty(row.getCell(0)))
{
cell_type = row.getCell(0).getCellType();
if (Cell.CELL_TYPE_NUMERIC == cell_type)
{
tdtm.setContactName(df.format(row.getCell(0).getNumericCellValue()));
}
else
{
tdtm.setContactName(row.getCell(0).toString());
}
}
else
{
tdtm.setContactName("");
}
// 号码集合
List<String> msnList = new ArrayList<String>();
// 号码1
if (!Global.isEmpty(row.getCell(1)))
{
cell_type = row.getCell(1).getCellType();
if (Cell.CELL_TYPE_NUMERIC == cell_type)
{
msnList.add(df.format(row.getCell(1).getNumericCellValue()));
}
else
{
msnList.add(row.getCell(1).toString());
}
}
// 号码2
if (!Global.isEmpty(row.getCell(2)))
{
cell_type = row.getCell(2).getCellType();
if (Cell.CELL_TYPE_NUMERIC == cell_type)
{
msnList.add(df.format(row.getCell(2).getNumericCellValue()));
}
else
{
msnList.add(row.getCell(2).toString());
}
}
// 号码3
if (!Global.isEmpty(row.getCell(3)))
{
cell_type = row.getCell(3).getCellType();
if (Cell.CELL_TYPE_NUMERIC == cell_type)
{
msnList.add(df.format(row.getCell(3).getNumericCellValue()));
}
else
{
msnList.add(row.getCell(3).toString());
}
}
tdtm.setMsnList(msnList);
// 传真集合
List<String> faxList = new ArrayList<String>();
// 号码1
if (!Global.isEmpty(row.getCell(4)))
{
cell_type = row.getCell(4).getCellType();
if (Cell.CELL_TYPE_NUMERIC == cell_type)
{
faxList.add(df.format(row.getCell(4).getNumericCellValue()));
}
else
{
faxList.add(row.getCell(4).toString());
}
}
// 号码2
if (!Global.isEmpty(row.getCell(5)))
{
cell_type = row.getCell(5).getCellType();
if (Cell.CELL_TYPE_NUMERIC == cell_type)
{
faxList.add(df.format(row.getCell(5).getNumericCellValue()));
}
else
{
faxList.add(row.getCell(5).toString());
}
}
tdtm.setFaxList(faxList);
// 公司名称
if (!Global.isEmpty(row.getCell(6)))
{
cell_type = row.getCell(6).getCellType();
if (Cell.CELL_TYPE_NUMERIC == cell_type)
{
tdtm.setCompany(df.format(row.getCell(6).getNumericCellValue()));
}
else
{
tdtm.setCompany(row.getCell(6).toString());
}
}
else
{
tdtm.setCompany("");
}
// 传真集合
List<String> emailList = new ArrayList<String>();
// Email1
if (!Global.isEmpty(row.getCell(7)))
{
cell_type = row.getCell(7).getCellType();
if (Cell.CELL_TYPE_NUMERIC == cell_type)
{
emailList.add(df.format(row.getCell(7).getNumericCellValue()));
}
else
{
emailList.add(row.getCell(7).toString());
}
}
// Email2
if (!Global.isEmpty(row.getCell(8)))
{
cell_type = row.getCell(8).getCellType();
if (Cell.CELL_TYPE_NUMERIC == cell_type)
{
emailList.add(df.format(row.getCell(8).getNumericCellValue()));
}
else
{
emailList.add(row.getCell(8).toString());
}
}
tdtm.setEmailList(emailList);
// QQ号码
if (!Global.isEmpty(row.getCell(9)))
{
cell_type = row.getCell(9).getCellType();
if (Cell.CELL_TYPE_NUMERIC == cell_type)
{
tdtm.setQq(df.format(row.getCell(9).getNumericCellValue()));
}
else
{
tdtm.setQq(row.getCell(9).toString());
}
}
else
{
tdtm.setQq("");
}
return tdtm;
}
/**
* <清空该SP用户的通讯录>
* <功能详细描述>
* @param spID
* @param spName
* @param operate
* @param conList
* @see [类、类#方法、类#成员]
*/
private void clearAllContacts(String spID, String spName, OperateContactsMessage operate,
List<TDTM_SPID_CONTACTS> conList)
{
if (null != conList)
{
for (TDTM_SPID_CONTACTS tdtm : conList)
{
operate.operateContacts(spID, spName, 3, tdtm);
}
conList.clear();
}
}
以上就是Excel文件的导入和导出,该代码是针对特定业务写的,有些代码没有拷贝,请各位看官在看的时候针对自己有帮助的地方看吧,附件是ajax文件上传控件和servlet代码