Excel poi的使用
常用API
创建工作薄和创建新Sheet页，创建单元格
public static void main(String[] args) throws Exception{
Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet=wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
wb.createSheet("第二个Sheet页"); // 创建第二个Sheet页
Row row=sheet.createRow(0); // 创建一个行
Cell cell=row.createCell(0); // 创建一个单元格 第1列
cell.setCellValue(1); // 给单元格设置值
row.createCell(1).setCellValue(1.2); // 创建一个单元格 第2列 值是1.2
row.createCell(2).setCellValue("这是一个字符串类型"); // 创建一个单元格 第3列 值为一个字符串
row.createCell(3).setCellValue(false); // 创建一个单元格 第4列 值为布尔类型
FileOutputStream fileOut=new FileOutputStream("c:\\用Poi搞出来的Cell.xls");
wb.write(fileOut);
fileOut.close();
}
设置数据类型
Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet=wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row=sheet.createRow(0); // 创建一个行
Cell cell=row.createCell(0); // 创建一个单元格 第1列
cell.setCellValue(new Date()); // 给单元格设置值
row.createCell(1).setCellValue(1);
row.createCell(2).setCellValue("一个字符串");
row.createCell(3).setCellValue(true);
row.createCell(4).setCellValue(HSSFCell.CELL_TYPE_NUMERIC);
row.createCell(5).setCellValue(false);
FileOutputStream fileOut=new FileOutputStream("c:\\工作簿.xls");
try {
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
设置时间
Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet=wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row=sheet.createRow(0); // 创建一个行
Cell cell=row.createCell(0); // 创建一个单元格 第1列
cell.setCellValue(new Date()); // 给单元格设置值
CreationHelper createHelper=wb.getCreationHelper();
CellStyle cellStyle=wb.createCellStyle(); //单元格样式类
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy-mm-dd hh:mm:ss"));
cell=row.createCell(1); // 第二列
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
cell=row.createCell(2); // 第三列
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
FileOutputStream fileOut=new FileOutputStream("c:\\工作簿.xls");
try {
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
遍历工作薄的行和列
public static void main(String[] args) throws Exception{
InputStream is=new FileInputStream("c:\\名单.xls");
POIFSFileSystem fs=new POIFSFileSystem(is);
HSSFWorkbook wb=new HSSFWorkbook(fs);
HSSFSheet hssfSheet=wb.getSheetAt(0); // 获取第一个Sheet页
if(hssfSheet==null){
return;
}
// 遍历行Row
for(int rowNum=0;rowNum<=hssfSheet.getLastRowNum();rowNum++){
HSSFRow hssfRow=hssfSheet.getRow(rowNum);
if(hssfRow==null){
continue;
}
// 遍历列Cell
for(int cellNum=0;cellNum<=hssfRow.getLastCellNum();cellNum++){
HSSFCell hssfCell=hssfRow.getCell(cellNum);
if(hssfCell==null){
continue;
}
System.out.print(" "+getValue(hssfCell));
}
System.out.println();
}
}
private static String getValue(HSSFCell hssfCell){
if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
return String.valueOf(hssfCell.getBooleanCellValue());
}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
return String.valueOf(hssfCell.getNumericCellValue());
}else{
return String.valueOf(hssfCell.getStringCellValue());
}
}
文本提取
public static void main(String[] args) throws Exception{
InputStream is=new FileInputStream("c:\\名单.xls");
POIFSFileSystem fs=new POIFSFileSystem(is);
HSSFWorkbook wb=new HSSFWorkbook(fs);
ExcelExtractor excelExtractor=new ExcelExtractor(wb);
excelExtractor.setIncludeSheetNames(false);// 我们不需要Sheet页的名字
System.out.println(excelExtractor.getText());
}
单元格对齐方式
public static void main(String[] args) throws Exception{
Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet=wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row=sheet.createRow(2); // 创建一个行
row.setHeightInPoints(30);
createCell(wb, row, (short)0, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_BOTTOM);
createCell(wb, row, (short)1, HSSFCellStyle.ALIGN_FILL, HSSFCellStyle.VERTICAL_CENTER);
createCell(wb, row, (short)2, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_TOP);
createCell(wb, row, (short)3, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_TOP);
FileOutputStream fileOut=new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
}
/**
* 创建一个单元格并为其设定指定的对其方式
* @param wb 工作簿
* @param row 行
* @param column 列
* @param halign 水平方向对其方式
* @param valign 垂直方向对其方式
*/
private static void createCell(Workbook wb,Row row,short column,short halign,short valign){
Cell cell=row.createCell(column); // 创建单元格
cell.setCellValue(new HSSFRichTextString("Align It")); // 设置值
CellStyle cellStyle=wb.createCellStyle(); // 创建单元格样式
cellStyle.setAlignment(halign); // 设置单元格水平方向对其方式
cellStyle.setVerticalAlignment(valign); // 设置单元格垂直方向对其方式
cell.setCellStyle(cellStyle); // 设置单元格样式
}
边框
public static void main(String[] args) throws Exception{
Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet=wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row=sheet.createRow(1); // 创建一个行
Cell cell=row.createCell(1); // 创建一个单元格
cell.setCellValue(4);
CellStyle cellStyle=wb.createCellStyle();
cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
cellStyle.setBorderLeft(CellStyle.BORDER_THIN); // 左边边框
cellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex()); // 左边边框颜色
cellStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex()); // 右边边框颜色
cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED); // 上边边框
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边边框颜色
cell.setCellStyle(cellStyle);
FileOutputStream fileOut=new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
}
背景颜色
public static void main(String[] args) throws Exception{
Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet=wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row=sheet.createRow(1); // 创建一个行
Cell cell=row.createCell(1);
cell.setCellValue("XX");
CellStyle cellStyle=wb.createCellStyle();
cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); // 背景色
cellStyle.setFillPattern(CellStyle.BIG_SPOTS);
cell.setCellStyle(cellStyle);
Cell cell2=row.createCell(2);
cell2.setCellValue("YYY");
CellStyle cellStyle2=wb.createCellStyle();
cellStyle2.setFillForegroundColor(IndexedColors.RED.getIndex()); // 前景色
cellStyle2.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell2.setCellStyle(cellStyle2);
FileOutputStream fileOut=new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
}
单元格合并
public static void main(String[] args) throws Exception{
Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet=wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row=sheet.createRow(1); // 创建一个行
Cell cell=row.createCell(1);
cell.setCellValue("单元格合并测试");
sheet.addMergedRegion(new CellRangeAddress(
1, // 起始行
2, // 结束行
1, // 其实列
2 // 结束列
));
FileOutputStream fileOut=new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
}
字体处理
public static void main(String[] args) throws Exception{
Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet=wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row=sheet.createRow(1); // 创建一个行
// 创建一个字体处理类
Font font=wb.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);
CellStyle style=wb.createCellStyle();
style.setFont(font);
Cell cell=row.createCell((short)1);
cell.setCellValue("This is test of fonts");
cell.setCellStyle(style);
FileOutputStream fileOut=new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
}
读取重写工作薄
public static void main(String[] args) throws Exception{
InputStream inp=new FileInputStream("c:\\工作簿.xls");
POIFSFileSystem fs=new POIFSFileSystem(inp);
Workbook wb=new HSSFWorkbook(fs);
Sheet sheet=wb.getSheetAt(0); // 获取第一个Sheet页
Row row=sheet.getRow(0); // 获取第一行
Cell cell=row.getCell(0); // 获取单元格
if(cell==null){
cell=row.createCell(3);
}
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("测试单元格");
FileOutputStream fileOut=new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
}
换行
public static void main(String[] args) throws Exception{
Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet=wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
Row row=sheet.createRow(2); // 创建一个行
Cell cell=row.createCell(2);
cell.setCellValue("我要换行 \n 成功了吗？");
CellStyle cs=wb.createCellStyle();
// 设置可以换行
cs.setWrapText(true);
cell.setCellStyle(cs);
// 调整下行的高度
row.setHeightInPoints(2*sheet.getDefaultRowHeightInPoints());
// 调整单元格宽度
sheet.autoSizeColumn(2);
FileOutputStream fileOut=new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
}
自定义数据格式
public static void main(String[] args) throws Exception{
Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet=wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
CellStyle style;
DataFormat format=wb.createDataFormat();
Row row;
Cell cell;
short rowNum=0;
short colNum=0;
row=sheet.createRow(rowNum++);
cell=row.createCell(colNum);
cell.setCellValue(111111.25);
style=wb.createCellStyle();
style.setDataFormat(format.getFormat("0.0")); // 设置数据格式
cell.setCellStyle(style);
row=sheet.createRow(rowNum++);
cell=row.createCell(colNum);
cell.setCellValue(1111111.25);
style=wb.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.000"));
cell.setCellStyle(style);
FileOutputStream fileOut=new FileOutputStream("c:\\工作簿.xls");
wb.write(fileOut);
fileOut.close();
}
利用poi实现数据批量导出
使用strusts2
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>PoiTest3</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<filter>
<filter-name>StrutsPrepareAndExecuteFilter</filter-name>
<filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>StrutsPrepareAndExecuteFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
struts.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.3//EN"
"http://struts.apache.org/dtds/struts-2.3.dtd">
<struts>
<package name="userInfo" namespace="/" extends="struts-default">
<action name="user" class="com.java.action.UserAction">
</action>
</package>
</struts>
UserAction.java
import java.sql.Connection;
import java.sql.ResultSet;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;
import com.java.dao.UserDao;
import com.java.model.PageBean;
import com.java.model.User;
import com.java.util.DbUtil;
import com.java.util.ExcelUtil;
import com.java.util.JsonUtil;
import com.java.util.ResponseUtil;
import com.java.util.StringUtil;
import com.opensymphony.xwork2.ActionSupport;
public class UserAction extends ActionSupport {
public String export()throws Exception{
Connection con=null;
try {
con=dbUtil.getCon();
Workbook wb=new HSSFWorkbook();
String headers[]={"编号","姓名","电话","Email","QQ"};
ResultSet rs=userDao.userList(con, null);
ExcelUtil.fillExcelData(rs, wb, headers);
ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
}
<script>
var url;
function exportUser(){
window.open('user!export')
}
</script>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser()">导出用户</a>
DbUtil.java
import java.sql.Connection;
import java.sql.DriverManager;
public class DbUtil {
private String dbUrl="jdbc:mysql://localhost:3306/db_easyui";
private String dbUserName="root";
private String dbPassword="123456";
private String jdbcName="com.mysql.jdbc.Driver";
public Connection getCon()throws Exception{
Class.forName(jdbcName);
Connection con=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
return con;
}
public void closeCon(Connection con)throws Exception{
if(con!=null){
con.close();
}
}
}
ExcelUtil.java
import java.sql.ResultSet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class ExcelUtil {
public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{
int rowIndex=0;
Sheet sheet=wb.createSheet();
Row row=sheet.createRow(rowIndex++);
for(int i=0;i<headers.length;i++){
row.createCell(i).setCellValue(headers[i]);
}
while(rs.next()){
row=sheet.createRow(rowIndex++);
for(int i=0;i<headers.length;i++){
row.createCell(i).setCellValue(rs.getObject(i+1).toString());
}
}
}
}
JsonUtil.java
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
public class JsonUtil {
/**
* 把ResultSet集合转换成JsonArray数组
* @param rs
* @return
* @throws Exception
*/
public static JSONArray formatRsToJsonArray(ResultSet rs)throws Exception{
ResultSetMetaData md=rs.getMetaData();
int num=md.getColumnCount();
JSONArray array=new JSONArray();
while(rs.next()){
JSONObject mapOfColValues=new JSONObject();
for(int i=1;i<=num;i++){
mapOfColValues.put(md.getColumnName(i), rs.getObject(i));
}
array.add(mapOfColValues);
}
return array;
}
}
ResponseUtil.java
import java.io.OutputStream;
import java.io.PrintWriter;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;
public class ResponseUtil {
public static void write(HttpServletResponse response,Object o)throws Exception{
response.setContentType("text/html;charset=utf-8");
PrintWriter out=response.getWriter();
out.print(o.toString());
out.flush();
out.close();
}
public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out=response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
}
使用模板批量导入
public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{
InputStream inp=ExcelUtil.class.getResourceAsStream("/com/java/template/"+templateFileName);
POIFSFileSystem fs=new POIFSFileSystem(inp);
Workbook wb=new HSSFWorkbook(fs);
Sheet sheet=wb.getSheetAt(0);
// 获取列数
int cellNums=sheet.getRow(0).getLastCellNum();
int rowIndex=1;
while(rs.next()){
Row row=sheet.createRow(rowIndex++);
for(int i=0;i<cellNums;i++){
row.createCell(i).setCellValue(rs.getObject(i+1).toString());
}
}
return wb;
}public String export2()throws Exception{
Connection con=null;
try {
con=dbUtil.getCon();
ResultSet rs=userDao.userList(con, null);
Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls");
ResponseUtil.export(ServletActionContext.getResponse(), wb, "利用模版导出excel.xls");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
dbUtil.closeCon(con);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}poi实现批量导入数据
<script>
var url;
function openUploadFileDialog(){
$("#dlg2").dialog('open').dialog('setTitle','批量导入数据');
}
function downloadTemplate(){
window.open('template/userExporTemplate.xls');
}
function uploadFile(){
$("#uploadForm").form("submit",{
success:function(result){
var result=eval（'('+result+')');
if(result.errorMsg){
$.messager.alert("系统提示",result.errorMsg);
}else{
$.messager.alert("系统提示","上传成功");
$("#dlg2").dialog("close");
$("#dg").datagrid("reload");
}
}
});
}
</script>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser2()">用模版导出用户</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-import" plain="true" onclick="openUploadFileDialog()">用模版批量导入数据</a>
<div id="dlg" class="easyui-dialog" style="width:400px;height:250px;padding:10px 20px"
closed="true" buttons="#dlg-buttons">
<form id="fm" method="post">
<table cellspacing="10px;">
<tr>
<td>姓名：</td>
<td><input id="name" name="user.name" class="easyui-validatebox" required="true" style="width: 200px;"></td>
</tr>
<tr>
<td>联系电话：</td>
<td><input id="phone" name="user.phone" class="easyui-validatebox" required="true" style="width: 200px;"></td>
</tr>
<tr>
<td>Email：</td>
<td><input id="email" name="user.email" class="easyui-validatebox" validType="email" required="true" style="width: 200px;"></td>
</tr>
<tr>
<td>QQ：</td>
<td><input id="qq" name="user.qq" class="easyui-validatebox" required="true" style="width: 200px;"></td>
</tr>
</table>
</form>
</div>
<div id="dlg2" class="easyui-dialog" style="width:400px;height:180px;padding:10px 20px"
closed="true" buttons="#dlg-buttons2">
<form id="uploadForm" action="user!upload" method="post" enctype="multipart/form-data">
<table>
<tr>
<td>下载模版：</td>
<td><a href="javascript:void(0)" class="easyui-linkbutton" onclick="downloadTemplate()">导入模版</a></td>
</tr>
<tr>
<td>上传文件：</td>
<td><input type="file" name="userUploadFile"></td>
</tr>
</table>
</form>
</div>
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;
private File userUploadFile;
public String upload()throws Exception{
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(userUploadFile));
HSSFWorkbook wb=new HSSFWorkbook(fs);
HSSFSheet hssfSheet=wb.getSheetAt(0); // 获取第一个Sheet页
if(hssfSheet!=null){
for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
HSSFRow hssfRow=hssfSheet.getRow(rowNum);
if(hssfRow==null){
continue;
}
User user=new User();
user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));
user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));
user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));
user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));
Connection con=null;
try{
con=dbUtil.getCon();
userDao.userAdd(con, user);
}catch(Exception e){
e.printStackTrace();
}finally{
dbUtil.closeCon(con);
}
}
}
JSONObject result=new JSONObject();
result.put("success", "true");
ResponseUtil.write(ServletActionContext.getResponse(), result);
return null;
}
public static String formatCell(HSSFCell hssfCell){
if(hssfCell==null){
return "";
}else{
if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
return String.valueOf(hssfCell.getBooleanCellValue());
}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
return String.valueOf(hssfCell.getNumericCellValue());
}else{
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
具体使用场景
根据选中ID导出数据库的数据
var empexcel = _viewer.getButton("empexcel");
if(empexcel){
empexcel.click(function(){
var ids = _grid.getIds();
if(ids){
var DEVICE_ID = _grid.getRowsValues(ids, "DEVICE_ID");
if($("#excelForm").length==0){
debugger;
var url=Leopard.getContextPath() +"/exportExcelModle.act";
var form = $('<form type="post" id="excelForm" action="'+url+'"><input id="DeviceId" name="DeviceId" value="'+DEVICE_ID+'" type="hidden"/></form>');
$("body").append(form);
}
$("#DEVICE_ID").val(DEVICE_ID);
$("#excelForm").submit();
$("#excelForm").remove();
}else{
var main = _viewer.getMainHandler();
var DEVICE_NAME = main.itemValue("DEVICE_NAME");
}
})
}
@Controller
public class ExcelExecuse{
@RequestMapping("/exportExcelModle")
@ResponseBody
public void exportExcelModle(HttpServletRequest request, HttpServletResponse response,String DeviceId) throws UnsupportedEncodingException{
StringBuffer sql = new StringBuffer("select DEVICE_ID,DEVICE_NAME,DEVICE_LOCNO,DEV_CLASS_ID,DEV_GLDW_NAME,DEVICE_LOCATION,DEVICE_STATE,YPOINT,XPOINT from DEV_DEVICE_TB where DEVICE_ID in (");
String fileName = "导出excel.xls";
response.setCharacterEncoding("utf-8");
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition", "attachment;fileName="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
ServletOutputStream out = null;
HSSFWorkbook workbook = null;
try {
out = response.getOutputStream();
// 声明一个工作薄
workbook = new HSSFWorkbook();
StringBuffer temp = new StringBuffer();
for (String ID : DeviceId.split(",")) {
temp.append(",?");
}
sql.append(temp.toString().replaceFirst(",", ""));
sql.append(")");
Object[] param = DeviceId.split(",");
List<IBean> data = SqlUtil.getExecutor().queryForList(sql.toString(),param);
setSheetByData(data, workbook);
workbook.write(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void setSheetByData(List<IBean> data, HSSFWorkbook workbook){
HSSFCell cell = null;
//设置表头数据
String[] head0 = new String[] { "设备ID", "设备名称", "设备位号", "设备类别", "设备管理单位", "设备位置","设备状态","x坐标","y坐标"};
//DEVICE_ID,DEVICE_NAME,DEVICE_LOCNO,DEV_CLASS_ID,DEV_GLDW_NAME,DEVICE_LOCATION,DEVICE_STATE
HSSFSheet sheet = workbook.createSheet();
int rowIndex=0;
HSSFRow row = sheet.createRow(rowIndex++);
// 表头标题样式
HSSFFont headfont = workbook.createFont();
headfont.setFontName("宋体");
headfont.setFontHeightInPoints((short) 22);// 字体大小
HSSFCellStyle headstyle = workbook.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
for (int i = 0; i < head0.length; i++) {
cell = row.createCell(i);
cell.setCellValue(head0[i]);
cell.setCellStyle(headstyle);
}
if(data!=null){
for(int j=0;j<data.size();j++){
row=sheet.createRow(rowIndex++);
IBean sbItems= data.get(j);
String Id = sbItems.get("DEVICE_ID", null);
String name = sbItems.get("DEVICE_NAME", null);
String dw = sbItems.get("DEVICE_LOCNO", null);
String lb = sbItems.get("DEV_CLASS_ID", null);
String gdw = sbItems.get("DEV_GLDW_NAME", null);
String wz = sbItems.get("DEVICE_LOCATION", null);
String zt = sbItems.get("DEVICE_STATE", null);
String xzb = sbItems.get("XPOINT", null);
String yzb = sbItems.get("YPOINT", null);
row.createCell(0).setCellValue(Id);
row.createCell(1).setCellValue(name);
row.createCell(2).setCellValue(dw);
row.createCell(3).setCellValue(lb);
row.createCell(4).setCellValue(gdw);
row.createCell(5).setCellValue(wz);
row.createCell(6).setCellValue(zt);
row.createCell(7).setCellValue(xzb);
row.createCell(8).setCellValue(yzb);
}
}
}样式设置
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller
public class ExcelExecuse {
@RequestMapping("/exportExcelModle")
@ResponseBody
public void exportExcelModle(HttpServletRequest request, HttpServletResponse response, String funcNames,
String funcCodes) {
StringBuffer sql = new StringBuffer("select * from EXCELMODELT_FIELD where FUNCODE = ?");
response.setCharacterEncoding("utf-8");
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition", "attachment;fileName=" + funcCodes + ".xls");
ServletOutputStream out = null;
HSSFWorkbook workbook = null;
try {
out = response.getOutputStream();
// 声明一个工作薄
workbook = new HSSFWorkbook();
List<IBean> dataList = null;
HSSFCellStyle style = getStyle(workbook);
for (String func : funcCodes.split(",")) {
dataList = SqlUtil.getExecutor().queryForList(sql.toString(), func);
setSheetByData(func, dataList, workbook, style);
}
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
@SuppressWarnings("deprecation")
public void setSheetByData(String title, List<IBean> dataList, HSSFWorkbook workbook, HSSFCellStyle style) {
HSSFSheet sheet = workbook.createSheet(title);
sheet.setDefaultColumnWidth((short) 15);
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
String FIELD_NAME = null;
int column = 0;
for (IBean data : dataList) {
FIELD_NAME = data.get("FIELD_NAME", "");
if (!StringUtils.isEmpty(FIELD_NAME)) {
cell = row.createCell(column);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(FIELD_NAME);
cell.setCellValue(text);
column++;
}
}
}
private HSSFCellStyle getStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
return style;
}
}
导入日期的时候有可能直接导入为数字而不是日期格式，转为数字是因为读取excel的是文本类型，通过getCellValue()获取的是字符串，要用getDateCellValue()获取数值类型的数据
//定义一个存储所有字段的list集合
HashMap<Integer,String[]> columnValueList = new HashMap<Integer,String[]>();
String[] columnValue = new String[3];
columnValue[0]=fieldName;
columnValue[2]=fieldDataType;
columnValueList.put(fieldOrder-1, columnValue);
//循环每一个单元格(从这里开始查询特定字段是否入库 把id都查询出来)
for (int cycleInt=templateStartCol;cycleInt<=templateEndCol;cycleInt++ ) {
Cell cell = row.getCell(cycleInt);
Object value = getCellValue(cell);
String[] columnValue = columnValueList.get(cycleInt);
if ("DATE".equalsIgnoreCase(columnValue[0])) {
String StrVal = value.toString().trim();
try {
int cellType = cell.getCellType();
if(cellType==1) {
Date d1 = new SimpleDateFormat("yyyy年MM月dd日").parse(StrVal);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
String time = format.format(d1);
value = time;
}else if(cellType==0){
Date date = cell.getDateCellValue();
value = DateFormatUtils.format(date, "yyyy-MM-dd");
}else{
value="";
}
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
excel poi 获取cell的值
/**
* 获得cell的数值
* @param cell
* @return
*/
public static Object getCellValue(Cell cell) {
if(cell==null){
return "";
}
Object result = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
//如果是date类型则 ，获取该cell的date值
//result = DateUtil.getJavaDate(cell.getNumericCellValue());
result = DateFormatUtils.format(cell.getDateCellValue(), "yyyy/MM/dd");
}else{
// 纯数字
//result =new BigDecimal( String.valueOf(cell.getNumericCellValue())).toString();
result = BigDecimal.valueOf(cell.getNumericCellValue()).stripTrailingZeros().toPlainString();
}
break;
case Cell.CELL_TYPE_STRING :
result = cell.getRichStringCellValue().getString().trim();
break;
case Cell.CELL_TYPE_FORMULA :
/* result = cell.getCellFormula(); 使用下面，直接获取值*/
DecimalFormat df = new DecimalFormat("#.0000");
result = df.format(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN :
result = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_ERROR :
result = cell.getErrorCellValue();
break;
case Cell.CELL_TYPE_BLANK :
result = "";
break;
}
return result;
}
获取非空行
public static boolean isBlankRow(Row row, Integer startCol, Integer endCol) {
if (row == null)
return true;
boolean result = true;
for (int i = startCol; i <= endCol; i++) {
Cell cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
String value = "";
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
value = String.valueOf((int) cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
value = String.valueOf(cell.getCellFormula());
break;
default:
break;
}
if (!value.trim().equals("")) {
result = false;
break;
}
}
}
return result;
}对比数据类型
public String getCellType(Cell cell,String ruleCellType,String dictSql) {
if (cell == null) return "";
String message = "";
String upperRuleType = ruleCellType.toUpperCase();
if(upperRuleType.equals("DATE")){
try{
Object value = getCellValue(cell);
if(value!=null && !value.toString().equals("")){
if(!DateUtil.isCellDateFormatted(cell)){
// 如果是date类型则 ，获取该cell的date值
message = "数据类型应该为日期类型";
}
}
}catch(Exception e){
message = "数据类型应该为日期类型";
}
}
if(upperRuleType.equals("INTEGER")||upperRuleType.equals("NUMBER")){
try{
String cellValue = getCellValue(cell).toString();
Double.parseDouble("".equals(cellValue) ? "0":cellValue);
}catch(Exception e){
message = "数据类型应该为数字类型";
}
}
return message;
}下载excel到客户端
public static void renderExcel(String path,String fileName,HttpServletResponse response) throws Exception{
if(!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase().endsWith(".xlsx")){
throw new Exception(fileName+"不是一个合法的excel文件名..............................");
}
// HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition","attachment;filename=\""+ new String((fileName).getBytes("UTF-8"),"ISO-8859-1") + "\"");
OutputStream os = response.getOutputStream();
FileInputStream fis = new FileInputStream(path);
byte[] buff = new byte[1024];
int readCount = 0;
readCount = fis.read(buff);
while (readCount != -1) {
os.write(buff, 0, readCount);
readCount = fis.read(buff);
}
if (fis != null) {
fis.close();
}
if (os != null) {
os.close();
}
}导出错误的excel
public String downloadExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
File file = new File("D:\\NEWUPLOAD\\\\EXCEL\\result.xlsx");
if(!file.exists()){
savaToExcel(new XSSFWorkbook("D:\\NEWUPLOAD\\EXCEL\\result.xlsx"),"D:\\NEWUPLOAD\\EXCEL\\result.xlsx");//输出到文件
}
renderExcel("D:\\NEWUPLOAD\\EXCEL\\result.xlsx","错误明细.xlsx", response);
return null;
}将excel保存在本地临时目录
public static void savaToExcel( Workbook workbook,String excelPath){
try {
FileOutputStream outputStream = new FileOutputStream(excelPath);
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
System.out.println("It cause Error on WRITTING excel workbook: ");
e.printStackTrace();
}
}刷新Cell 背景颜色
public static void refreshCellBackgroundColor(Workbook wb, Sheet sheet, int startRow, int endRow, int startCell,
int endCell, CellStyle cellStyle) {
Row row = null;
Cell cell = null;
for (int i = startRow; i <= endRow; i++) {
row = sheet.getRow(i);
if (row != null) {
for (int j = startCell; j <= endCell; j++) {
cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
}
if(cell.getCellStyle().getDataFormat()>0){
CellStyle cellStyle2 = wb.createCellStyle();
cellStyle2.cloneStyleFrom(cellStyle);
//cellStyle2.setDataFormat(cell.getCellStyle().getDataFormat());
cell.setCellStyle(cellStyle2);
}else{
cellStyle.setDataFormat(cell.getCellStyle().getDataFormat());
cell.setCellStyle(cellStyle);
}
}
}
}
}
POI框架
前台js
expExcel: function(curDom, event, treeNode){
/** 完整参数传递
* params = {};
params.tplCode = "joinApply";
params.fileName = "导出数据";
var obj = {};
obj.JCRW_BH = "RW-GBQMJY2015050023";
params.whereKeyValue = JSON.stringify(obj);
params.busiData = '{"dynSheet":"YES","checkedTpl":["ZLSCBG","HGJCBG","XLJC","GYGDQMJYML"]}';
&whereKeyValue="+JSON.stringify(obj)
*
*/
var _self = this;
var busiData="";
var paramData = $("#partyOrgId").val();
if(paramData==""){
busiData = $("#paramData").val();
}else{
busiData = $("#partyOrgId").val();
}
var url = $(curDom).attr("url"),
_tplCode = "applyList",
_fileName = "申请对象";
var obj={};
obj.apply_state='2';
var strUrl = $(curDom).attr("url")
+ "?tplCode=" + _tplCode
+ "&fileName=" + encodeURI(_fileName)
+ "&busiData=" + busiData;
var ifm;
if (document.getElementById("empexcel_iframe") == undefined) {
ifm = document.createElement("IFRAME");
ifm.setAttribute("id", "empexcel_iframe");
ifm.setAttribute("name", "empexcel_iframe");
ifm.style.height = "0";
ifm.style.width = "0";
ifm.style.display = "block";
ifm.src = "";
document.body.appendChild(ifm);
} else {
ifm = document.getElementById("empexcel_iframe");
}
ifm.src = strUrl;
}后台controller
@RequestMapping(value="/export/statisticsExcel", method=RequestMethod.GET)
public void exportStatisticsExcel(HttpServletRequest request, HttpServletResponse response,
ExportParams exportParams) {
try {
EmpExcelService.createExcel(request, response, exportParams);
} catch (IOException e) {
e.printStackTrace();
}
}
后台service
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
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 javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
@Service
public class EmpExcelService {
@Autowired
private ExcelTplMapper excelTplMapper;
@Autowired
private ExcelDataMapper excelDataMapper;
@Autowired
private JdbcTemplate jdbcTemplate;
// 导出模型编码
private String tplCode = "";
private JSONObject whereKeyValue = null;
private JSONObject busiData = null;
private String proRealPath = null;
private String busiParamData = "";
//主模板配置的子模板列表
public static String mainModelChildSheetName = "sheetChildNames";
public static String sheetDealClass = "sheetDealClass";
public void createExcel(HttpServletRequest request, HttpServletResponse resp,
ExportParams exportParams) throws IOException{
this.tplCode = exportParams.getTplCode(); //导出的功能模板编码
String fileName = exportParams.getFileName(); //导出execel文件名（同管道名称）
String _whereKeyValue = exportParams.getWhereKeyValue(); //附件查询条件
String isExcelTemp = exportParams.getIsExcelTemp();
if (!StringUtil.isEmpty(_whereKeyValue)) {
try {
_whereKeyValue = java.net.URLDecoder.decode(_whereKeyValue, "UTF-8");
this.whereKeyValue = JSON.parseObject(_whereKeyValue);
} catch (JSONException | UnsupportedEncodingException e) {
e.printStackTrace();
}
}
//获取业务参数
String busiStr = exportParams.getBusiData();
if(null!=busiStr&&busiStr!="") {
busiParamData = busiStr;
}
if(!StringUtil.isEmpty(busiStr)){
try {
this.busiData = JSON.parseObject(busiStr);
} catch (Exception e) {}
}
//TODO 导出前执行逻辑 【后期完善】
exeFuncAct("beforeEmp");
this.proRealPath = request.getServletContext().getRealPath("/");
String path = getDemoDirSource(); // 模板存储路径
String name = getExcelTplName();
if(StringUtil.isEmpty(fileName)){
fileName = name;
} else {
fileName = fileName + name.substring(name.lastIndexOf(".")); //拼接模板后缀
}
String demoSourcre = path + "/" + name;// 模板绝对路径
name = encode(name, "ISO-8859-1");
if(!StringUtil.isEmpty(fileName)) {
name = encode(fileName, "ISO-8859-1");
}
resp.setContentType("application/x-msdownload;charset=GBK");
resp.setHeader("Content-Disposition", "attachment; filename=" + name);
// 输出流
OutputStream out = resp.getOutputStream();
// 获取数据（如果是导入模板则不获取数据）
Map<String, List<Map<String, Object>>> data = null;
if(StringUtil.isEmpty(isExcelTemp)){
if(busiParamData==""&&busiParamData.length()==0) {
data = initDataByFuncExcelConfig();
}else {
data = initDataByStatisFuncExcelConfig(busiParamData);
}
}
CreateExcel excel = new CreateExcel(demoSourcre, out, busiData);
excel.setData(data);
// 创建数据
excel.CreateEx(isExcelTemp);
exeFuncAct("afterEmp");
}
/**
* 通过本功能的Excel配置列表获取数据
* */
private Map<String, List<Map<String, Object>>> initDataByFuncExcelConfig() {
Map<String, List<Map<String, Object>>> data = new HashMap<String, List<Map<String, Object>>>();
//数据源对应的sheet 子模板
List<Map<String,Object>> sheetNames = new ArrayList<Map<String,Object>>();
Map<String,Object> sheetNameMap = new HashMap<String,Object>();
//处理类
List<Map<String,Object>> sheetClass = new ArrayList<Map<String,Object>>();
Map<String,Object> sheetDeal = new HashMap<String,Object>();
List<ExcelData> excelDatas = excelDataMapper.getExcelDataByCode(this.tplCode);
for (ExcelData exData : excelDatas) {
try {
// 获取数据别名
String TEMPLATE_NAME = exData.getDbCode(); //itemiBean.get("TEMPLATE_NAME", "");
// 获取SQL语句
String TEMPLATE_SQL = ReplaceSql(exData.getDbInfo());
if(!isSql(exData.getDbType())){ //通过Java类获取数据
sheetDeal.put(TEMPLATE_NAME, TEMPLATE_SQL);
data.put(TEMPLATE_NAME, getDataByTpl(TEMPLATE_NAME,TEMPLATE_SQL));
}else { //通过sql获取数据
List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
List<Map<String, Object>> queryDts = jdbcTemplate.queryForList(TEMPLATE_SQL);
for (Map<String, Object> ibb : queryDts) {
datas.add(ibb);
}
data.put(TEMPLATE_NAME, datas);
}
sheetNameMap.put(TEMPLATE_NAME,exData.getDbName());
} catch (Exception e) {
e.printStackTrace();
continue;
}
}
sheetNames.add(sheetNameMap);
sheetClass.add(sheetDeal);
data.put(mainModelChildSheetName,sheetNames);
data.put(sheetDealClass, sheetClass);
return data;
}
/**
* 通过本功能的Excel配置统计数据
* */
private Map<String, List<Map<String, Object>>> initDataByStatisFuncExcelConfig(String busiData) {
Map<String, List<Map<String, Object>>> data = new HashMap<String, List<Map<String, Object>>>();
//数据源对应的sheet 子模板
List<Map<String,Object>> sheetNames = new ArrayList<Map<String,Object>>();
Map<String,Object> sheetNameMap = new HashMap<String,Object>();
//处理类
List<Map<String,Object>> sheetClass = new ArrayList<Map<String,Object>>();
Map<String,Object> sheetDeal = new HashMap<String,Object>();
List<ExcelData> excelDatas = excelDataMapper.getExcelDataByCode(this.tplCode);
StringBuffer newStr = new StringBuffer();
for (ExcelData exData : excelDatas) {
try {
// 获取数据别名
String TEMPLATE_NAME = exData.getDbCode(); //itemiBean.get("TEMPLATE_NAME", "");
// 获取SQL语句
String TEMPLATE_SQL = ReplaceSql(exData.getDbInfo());
if(busiParamData!=""&&busiParamData.length()>0) {
if(TEMPLATE_SQL.contains("?")) {
for(int i=0;i<TEMPLATE_SQL.split("\\?").length;i++) {
/*list.add(TEMPLATE_SQL.split("?")[i]);*/
newStr.append(TEMPLATE_SQL.split("\\?")[i]);
newStr.append("\'"+busiData+"\'");
}
/*if(newStr) {
newStr.replace(newStr.length()-1,newStr.length(),"");
}*/
}else {
newStr.append(TEMPLATE_SQL);
}
}
String strSql = new String(newStr);
if(!isSql(exData.getDbType())){ //通过Java类获取数据
sheetDeal.put(TEMPLATE_NAME, strSql);
data.put(TEMPLATE_NAME, getDataByTpl(TEMPLATE_NAME,strSql));
}else { //通过sql获取数据
List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
List<Map<String, Object>> queryDts = jdbcTemplate.queryForList(strSql);
for (Map<String, Object> ibb : queryDts) {
datas.add(ibb);
}
data.put(TEMPLATE_NAME, datas);
}
sheetNameMap.put(TEMPLATE_NAME,exData.getDbName());
} catch (Exception e) {
e.printStackTrace();
continue;
}
}
sheetNames.add(sheetNameMap);
sheetClass.add(sheetDeal);
data.put(mainModelChildSheetName,sheetNames);
data.put(sheetDealClass, sheetClass);
return data;
}
/**
* 通过Java类获取模板配置数据
* @param params, 界面的素有参数
* @return
*/
@SuppressWarnings("unchecked")
private List<Map<String, Object>> getDataByTpl(String tempName, String tplStr){
List<Map<String, Object>> result = null; //objWhere, 界面参数
try{
String[] tpl = tplStr.split(",");
Class<?> cls = Class.forName(tpl[0]); //处理类
Method mf = cls.getMethod(tpl[1], JSONObject.class); //处理方法
whereKeyValue.put("BUSI_DATA", busiData);
whereKeyValue.put("TEMPLATE_NAME", tempName);
result = (List<Map<String, Object>>)mf.invoke(cls.newInstance(), whereKeyValue); // 执行方法
}catch(Exception e){}
return result;
}
/**
* 判断是否为sql模板
* @param tplStr
* @return
*/
private boolean isSql(Integer dbType){
if(Constant.NUMBER_INTEGER_1 == dbType)
return true;
return false;
}
/**
* 替换strSql语句
*
* @param strSql
* @return
*/
private String ReplaceSql(String strSql) {
Pattern reg0 = Pattern.compile("\\{[^\\{\\}]+\\}"); // 匹配{.....} 代表一个条件
Pattern reg1 = Pattern.compile("#[^#]+#"); // 匹配#.....# 代表控件值
// Pattern reg2 = Pattern.compile("\\$[^\\$]+\\$"); //匹配$.....$ 代表一个变量值
// 判断是否有{}的存在
Matcher match0 = reg0.matcher(strSql);
// 存在{} 表示需要替换
while (match0.find()) {
String strkh = match0.group();
String strkhResult = strkh;
Boolean haveChange = false;
Matcher match1 = reg1.matcher(strkhResult);
while (match1.find()) {
if (whereKeyValue == null ) {
// 没有替换过 就将{} 替换为空
strSql = strSql.replace(strkh, "");
} else {
String strjh = match1.group();
String strCode = strjh.replace("VAL_", "").replace("val_",
"").replace("#", "");
String strValue = "";
try {
strValue = whereKeyValue.getString(strCode);
} catch (JSONException e) {
// se.printStackTrace();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
strkh = strkhResult;
// 如果有值 就替换
if (!strValue.equals("")) {
haveChange = true;
strkhResult = strkhResult.replace(strjh, strValue);
}
// Matcher match2 = reg2.matcher(strSql);
if (!haveChange) {
// 没有替换过 就将{} 替换为空
strSql = strSql.replace(strkh, "");
} else {
strSql = strSql.replace(strkh, strkhResult);
}
}
}
}
strSql = strSql .replace( "{", "").replace("}", "");
return strSql;
}
/**
* 模板名称编码
*
* @throws UnsupportedEncodingException
* */
private String encode(String path, String Encode)
throws UnsupportedEncodingException {
return new String(path.getBytes(), Encode);
}
/**
* 获取模板名称
* */
private String getExcelTplName() {
ExcelTpl tpl = excelTplMapper.getExcelTplByCode(this.tplCode);
return tpl.getExcelName() != null ? tpl.getExcelName() : "模板不存在...";
}
/**
* 获取模板存储路径( 暂时写死，后期有需求，可以放置在配置文件或存入数据库中)
* */
private String getDemoDirSource() {
String dir = "application\\excel\\tpl";
return this.proRealPath + dir;
}
/**
* 数据导出执行方法
* 通过反射 执行预定方法
* @param methodStr
*/
private void exeFuncAct(String methodStr){
/*Class<?> cls = Class.forName(act.get("ACT_CLASS", ""));
Method method = cls.getMethod(methodStr, IBean.class);
method.invoke(cls.newInstance(), this.busiData);*/
}
}
上传框架
public AssembleJSON upload(HttpServletRequest request,HttpServletResponse response) {
return uploadCore(request,response,true);
}
private AssembleJSON uploadCore(HttpServletRequest request,HttpServletResponse response,Boolean isAppend){
response.setCharacterEncoding("UTF-8");
if (!(request instanceof MultipartHttpServletRequest)) {
return AssembleJSON.FAILURE("非法请求");
}
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultiValueMap<String, MultipartFile> fileMap = multipartRequest.getMultiFileMap();
List<MultipartFile> files = new ArrayList<MultipartFile>();
for (String key : fileMap.keySet()) {
files.addAll(fileMap.get(key));
}
if (files.isEmpty()) {
return AssembleJSON.FAILURE("请求中没没有包含文件");
}
try{
uploadDir += "/upload/";
if(ObjectUtil.isNotEmpty(companyID)){
uploadDir += companyID + "/";
}
uploadDir += "";
FileUtil.createUploadDir(uploadDir);
File file = null;
String fileName = "";
for(MultipartFile multipartFile : files) {
attachmentName = multipartFile.getOriginalFilename();
fileName = FileUtil.getNewFileName(attachmentName);
String rootFile = uploadDir + File.separatorChar + fileName;
filePath = "/upload/" + (ObjectUtil.isNotEmpty(companyID)?companyID+ "/":"")
+ File.separatorChar + fileName;
file = new File(rootFile);
multipartFile.transferTo(file);
this.saveThumbnail(fileName);
this.save(isAppend);
}
return AssembleJSON.SUCCESS;
}catch(Exception e){
return AssembleJSON.FAILURE(e.getMessage());
}
}
