正文
数据库数据以Excel的方式导出
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
import java.io.Serializable;
import java.util.List; import com.cfets.cwap.s.util.db.TableColumn;
/**
*
* @ClassName: ParamVO
* <b>Copyright 2018 中国XX中心 All Rights Reserved</b>
* @Description: TODO
* @author liuhanlin
* @date 2018年8月21日 下午2:15:45
*
*/ public class ParamVO implements Serializable{ /**
* @Fields serialVersionUID : TODO
*/
private static final long serialVersionUID = -1613650619973876968L;
// @TableColumn(name = "DL_TCKT_CD")
private String floorName;
private String productCode;
private String userCode;
private String statu;
public String getFloorName() {
return floorName;
}
public void setFloorName(String floorName) {
this.floorName = floorName;
}
public String getProductCode() {
return productCode;
}
public void setProductCode(String productCode) {
this.productCode = productCode;
}
public String getUserCode() {
return userCode;
}
public void setUserCode(String userCode) {
this.userCode = userCode;
}
public String getStatu() {
return statu;
}
public void setStatu(String statu) {
this.statu = statu;
} }
2、Excel导出方法
/**
* @description:excel导出方法
* @param fileName 导出的excel名称
* @param titleColumn 导出的excel列对应的VO类的属性名称数组（VO类属性名称）
* @param titleNames 导出的excel列标题数组（表头）
* @param columnWidth 导出的excel列宽
* @param dataList 传入的数据的列表
* @return String
* @throws MscQueryException
* @author liuhanlin
* @create on 2016年12月29日
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public static String excelExport(HttpServletResponse response,
String[] titleColumn, String[] titleNames,
List<?> dataList) throws MscQueryException {
logger.info("titleNames:{}, titleColumn:{}", titleNames, titleColumn);
long begin = System.currentTimeMillis(); ServletOutputStream out = null;
WritableWorkbook workbook = null;
try {
out = response.getOutputStream();
//本地测试
FileOutputStream outTest = new FileOutputStream(new File("C:/Users/Liuhl.LIUHL-PC/Desktop/190/Imix") + "/"
+ "xxx.xls");
workbook = Workbook.createWorkbook(outTest);
if(CollectionUtils.isEmpty(dataList)){
WritableSheet sheet = workbook.createSheet("sheet1", 0);
for (int i = 0; i < titleColumn.length; i++) {
Label label = new Label(i, 0, titleNames[i]);
sheet.addCell(label);
}
}else{
int row = 650;
int total = dataList.size();
int page = 0;//sheet页数 if(total % row == 0){
page = total/row;
}else{
page = total/row+1;
}
for(int k = 0;k < page;k++){ WritableSheet sheet = workbook.createSheet("sheet"+(k+1), 0); //写入表头内容
for (int i = 0; i < titleNames.length; i++) {
Label label = new Label(i, 0, titleNames[i]);
sheet.addCell(label);
} if (!CollectionUtils.isEmpty(dataList)) {
List<?> list = new ArrayList(); int cols = 0;
if(k == page-1){
cols = total % row;
list = dataList.subList(k*row, total);
} else{
cols = row;
list = dataList.subList(k*row, row*(k+1));
} for (int i = 1; i <= cols; i++) { Object obj = list.get(i - 1);
Class c = obj.getClass(); for (int j = 0; j < titleColumn.length; j++) {
String columnName = titleColumn[j];
String title = Character.toUpperCase(columnName.charAt(0))
+ columnName.substring(1);
String methodName = "get" + title;
Method method = c.getDeclaredMethod(methodName);
String data = "null".equalsIgnoreCase(method.invoke(obj)+"") ? "--" : method
.invoke(obj).toString();
Label label = new Label(j, i, data);
sheet.addCell(label);
}
}
}
}
}
workbook.write();
} catch (Exception e) {
logger.error("导出excel失败!", e);
throw new MscQueryException("导出excel失败!" + e);
} finally {
try {
workbook.close();
out.close();
} catch (IOException e) {
logger.error("导出excel失败!", e);
}
}
long end = System.currentTimeMillis();
logger.debug("导出XXX数据时间为：{}",(end-begin));
return null;
}
3、测试类
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap; import javax.servlet.http.HttpServletResponse; import org.apache.commons.collections.CollectionUtils;
import org.junit.runner.RunWith;
import org.springframework.context.annotation.Configuration;
import org.springframework.mock.web.MockHttpServletRequest;
import org.springframework.mock.web.MockHttpServletResponse;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import com.cfets.cwap.s.spi.SpiConfig;
import com.cfets.cwap.s.spi.SpiConfig.Env;
import com.cfets.ts.s.deal.exception.MscQueryException;
import com.cfets.ts.u.dealmgmt.entity.vo.ParamVO;
import com.cfets.ts.u.dealmgmt.service.ExcelExportUtil;
import com.cfets.ts.u.dealmgmt.service.FxDealMapService;
import com.cfets.ts.u.dealmgmt.util.GenerateExcelService;
import com.google.gson.Gson;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations ="/cwap-spring-context-app.xml")
//@ContextConfiguration(locations = { "classpath:/cwap-spring-context-app.xml"})
@Configuration
public class Test {
FxDealMapService service = new FxDealMapService();
static {
SpiConfig.env = Env.TEST;
}
@org.junit.Test
public void test(){
List<Map<String, Object>> lists = service.queryMapData();
List<ParamVO> list = new ArrayList<ParamVO>();
for (Map<String, Object> map : lists) {
for (Map.Entry<String, Object> entry : map.entrySet()) {
ParamVO vo = new ParamVO();
vo.setFloorName((String)map.get("floorName"));
vo.setProductCode((String)map.get("productCode"));
vo.setUserCode((String)map.get("userCode"));
vo.setStatu((String)map.get("statu"));
list.add(vo);
}
}
System.err.println(new Gson().toJson(list));
MockHttpServletResponse response = new MockHttpServletResponse();
String titleColumn[] = {"floorName", "productCode","userCode","statu"};
String titleNames[] = {"floorName", "productCode", "userCode", "statu"};
try {
ExcelExportUtil.excelExport(response, titleColumn, titleNames, list);
} catch (MscQueryException e) {
e.printStackTrace();
}
}
备注：（jexcelapi-jxl.jar）
