===============================================================导出excel========================================================
/*
* 创建日期 -10-28
*
* 更改所生成文件模板为
* 窗口 > 首选项 > Java > 代码生成 > 代码和注释
*/
package com.yutong.util;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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;
/**
* @author Administrator
*
* 更改所生成类型注释的模板为 窗口 > 首选项 > Java > 代码生成 > 代码和注释
*/
public class ExportToExcel {
/**
*
* @param response response
* @param filename filename
* @param sheetname sheetname
* @param titles titles
* @param reportList reportList
* @param width width
* @return
* @throws Exception Exception
*/
public void exportToExcel(HttpServletResponse response, String filename, String sheetname, String[] titles,
List reportList, int width) throws Exception {
OutputStream os = null;
try {
HSSFWorkbook wb = (HSSFWorkbook) writeToWorkbook(reportList, width);
response.reset(); // 清空输出流
// filename = new String(filename.getBytes("gbk"), "ISO-8859-1");
response.setHeader("Content-disposition", "attachment; filename=" + filename + ".xls");
// 设定输出文件头
// response.setCharacterEncoding("utf-8");
response.setContentType("APPLICATION/OCTET-STREAM"); // 定义输出类型
os = response.getOutputStream(); // 取得输出流
// 生成excel文件
wb.write(os);
// 立即输出
response.flushBuffer();
} catch(IOException ex) {
ex.printStackTrace();
}
}
/**
* 导出excel.
* @param reportList
* 数据信息.
* @param width
* 列数.
* @return Object Object.
* @throws Exception Exception.
*/
private Object writeToWorkbook(List reportList, int width) throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
short rows = 0; // 行号
HSSFRow rowObj;
HSSFCellStyle style = wb.createCellStyle();
if(reportList != null && reportList.size() > 0) {
for(int i = 0; i < reportList.size(); i++) {
String[] vo = (String[]) reportList.get(i);
rowObj = sheet.createRow(rows++);
for(int j = 0; j < vo.length; j++) {
this.generatorRows(rowObj, width, style);
}
for(int j = 0; j < vo.length; j++) {
(rowObj.getCell((short) j)).setCellValue(vo[j]);
}
}
}
return wb;
}
/*
* 生成列单元格 columns 一行的列数
*/
/**
* 生成列单元格 columns 一行的列数.
* @param rowObj rowObj
* @param columns columns
* @param style style
*/
private void generatorRows(HSSFRow rowObj, int columns, HSSFCellStyle style) {
// 设置边框
this.setCellStyle(style, HSSFCellStyle.BORDER_THIN);
if(columns > 0) {
for(int i = 0; i < columns; i++) {
HSSFCell csCell = rowObj.createCell((short) i);
//csCell.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell.setCellStyle(style);
}
}
}
/**
* @param style style
* @param type type
*/
public void setCellStyle(HSSFCellStyle style, short type) {
style.setBorderBottom(type);// 下边框
style.setBorderLeft(type);// 左边框
style.setBorderRight(type);// 右边框
style.setBorderTop(type);// 上边框
}
public void getWorkbook(HttpServletResponse response,List<Map<String,String>> listVals,String[] listCols,String[] listFlds){
OutputStream os = null;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("类信息");
HSSFRow row = sheet.createRow(0); //创建第1行，也就是输出表头
HSSFCell cell;
try {
String filename="";
int row1=-1;
if(listCols!=null && listCols.length>0){
row1=row1+1;
for(int i=0;i<listCols.length;i++){
cell = row.createCell(i); //创建第i列
cell.setCellValue(new HSSFRichTextString(listCols[i]));
}
}
//下面是输出各行的数据
for (int i = 0; i < listVals.size(); i++) {
Map<String,String> map=listVals.get(i);
row=sheet.createRow(i+1+row1);//创建第i+1行
for(int j=0;j<listFlds.length;j++){
cell=row.createCell(j);
Object val=map.get(listFlds[j]);
if(val!=null){
cell.setCellValue(val.toString());
}else
{
cell.setCellValue("");
}
}
}
Calendar c = Calendar.getInstance();
int year = c.get(Calendar.YEAR);
int month = c.get(Calendar.MONTH) + 1;
String month_ = new String("" + month);
if (month < 10) {
month_ = "0" + month;
}
int day = c.get(Calendar.DAY_OF_MONTH);
String day_ = new String("" + day);
if (day < 10) {
day_ = "0" + day;
}
filename=year + "-" + month_ + "-" + day_ + "";
response.reset(); // 清空输出流
// filename = new String(filename.getBytes("gbk"), "ISO-8859-1");
response.setHeader("Content-disposition", "attachment; filename=" + filename + ".xls");
// 设定输出文件头
// response.setCharacterEncoding("utf-8");
response.setContentType("APPLICATION/OCTET-STREAM"); // 定义输出类型
os = response.getOutputStream(); // 取得输出流
// 生成excel文件
workbook.write(os);
// 立即输出
response.flushBuffer();
} catch(IOException ex) {
ex.printStackTrace();
}
}
/**
* 对excel表格的值进行处理.
*
* @param cell
* cell
* @return str
*/
public static String getCellValue(HSSFCell cell) {
String str = "";
// System.err.println("***************TYPE is "+cell.getCellType());
if(cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
str = String.valueOf(cell.getStringCellValue()); // .trim();
}
if(cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
str = cell.getStringCellValue(); // .trim();
}
if(cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
str = String.valueOf(cell.getBooleanCellValue()); // .trim();
}
if(cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
str = ""; // .trim();
}
return str.trim();
}
public static String getDeptNameByCode(String key){
Map map=new HashMap();
map.put("ZJ", "制件一车间");
map.put("ZA", "制件二车间");
map.put("1000", "ZJ");
map.put("1200", "ZA");
return map.get(key).toString();
}
}
==========================================================================导入excel==================================================
public ActionForward insertByexcel(ActionMapping mapping, ActionForm form, HttpServletRequest request,
HttpServletResponse response) {
request.getSession().removeAttribute("paramquery");
request.getSession().removeAttribute("mapForexecut");
request.getSession().removeAttribute("batchNo");
PrintWriter out = null;
Hashtable files = (Hashtable) form.getMultipartRequestHandler().getFileElements();
Iterator it = files.values().iterator();
String isSizeBig = "0";
Object fileObj = null;
try {
while (it.hasNext() && !"1".equals(isSizeBig)) {
fileObj = it.next();
isSizeBig = "1";
}
// ajax返回用到的out
out = response.getWriter();
// 把文件转成excel数据
HSSFWorkbook book = createWorkBook(((FormFile[]) fileObj)[0]);
// 判断有几张活动的sheet表
int size = 2;//book.getActiveSheetIndex();
HSSFSheet sheet = null;
AbstractManager manager = getEntityManager();
// 存取版本号
Map batchNos = new HashMap();
// 参数map,到后面放到session里，执行的时候用
Map param = new HashMap();
//批次号只生成一次
String batchNo ="";
for (int s = 0; s < size; s++) {
sheet = book.getSheetAt(s);
// 第一个sheet保存着基本信息，如工厂ID，专业组编码和专业组开始时间
if (s == 0) {
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
try {
HSSFRow ros = sheet.getRow(i);
String facId = ExportToExcel.getCellValue(ros.getCell(0));
// 根据工厂ID获取部门
String deptCode = ExportToExcel.getDeptNameByCode(facId);
// 工作组
String wrkgrpCode = ExportToExcel.getCellValue(ros.getCell(1));
//专业组开始时间
Date startDate = ros.getCell(2).getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Format format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
param.put("iv_factory_id", facId);
param.put("iv_dept", deptCode);
// 生成工作组日历
if ((null != facId && !"".equals(facId) && deptCode != null && !"".equals(deptCode)) && batchNo.equals("")){
// 调用存储过程，返回批次号
paichanDataManager.queryVersionByWrkgrpWorkDay2(param);
batchNo = param.get("o_versionid").toString(); // 生成批次号
}
param.put("iv_batch_no", batchNo);
param.put("iv_depart", deptCode);
// 把值放到排产参数表中
PaichanParam pPara = new PaichanParam();
pPara.setFactoryId(facId);
pPara.setStartDate(startDate);
pPara.setBatchNo(batchNo);
pPara.setDeptCode(deptCode);
pPara.setWrkgrpCode(wrkgrpCode);
pPara.setCreateUser(getUserName(request));
pPara.setIsuse("Y");
manager.insertByFullStatementName("com.yutong.pc.paichan.model.PaichanParam.insert", pPara);
// 把批次号存起来，在sheet2页中用
// batchNos.put((facId.trim() + wrkgrpCode.trim()), batchNo);
} catch (Exception e) {
//out.println("<script>parent.callbackUpload('"+e.getMessage()+"')</script>");
saveDirectlyMessage(request, e.getMessage());
e.printStackTrace();
return mapping.findForward(EXPORT);
}
}
} else {
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
HSSFRow ros = sheet.getRow(i);
PaichanData paichan = new PaichanData();
paichan.setFactoryId(ExportToExcel.getCellValue(ros.getCell(0)));
paichan.setWrkgrpCode(ExportToExcel.getCellValue(ros.getCell(1)));
// 获取batchNos的key
String key = paichan.getFactoryId().trim() + paichan.getWrkgrpCode().trim();
// String batchNo = batchNos.get(key).toString();
paichan.setBatchNo(batchNo);
paichan.setDepartCode(ExportToExcel.getDeptNameByCode(paichan.getFactoryId()));
paichan.setUnitCode(ExportToExcel.getCellValue(ros.getCell(2)));
paichan.setDeliveryQuantity(Integer.parseInt(ExportToExcel.getCellValue(ros.getCell(3))));
String day = "";
String time = "";
if (0 == ros.getCell(4).getCellType()) {
// 判断是否为日期类型
if (HSSFDateUtil.isCellDateFormatted(ros.getCell(4))) {
// 用于转化为日期格式
Date d = ros.getCell(4).getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
day = formater.format(d);
} else {
// 用于格式化数字，只保留数字的整数部分
DecimalFormat df = new DecimalFormat("########");
day = df.format(ros.getCell(4).getNumericCellValue());
}
} else {
day = ExportToExcel.getCellValue(ros.getCell(4));
}
if (0 == ros.getCell(5).getCellType()) {
// 判断是否为日期类型
if (HSSFDateUtil.isCellDateFormatted(ros.getCell(5))) {
// 用于转化为日期格式
Date d = ros.getCell(5).getDateCellValue();
DateFormat formater = new SimpleDateFormat("HH:mm:ss");
time = formater.format(d);
} else {
// 用于格式化数字，只保留数字的整数部分
DecimalFormat df = new DecimalFormat("########");
time = df.format(ros.getCell(5).getNumericCellValue());
}
} else {
time = ExportToExcel.getCellValue(ros.getCell(5));
}
String datestr = day.trim() + " " + time.trim();
// ros.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
String hourCy = ExportToExcel.getCellValue(ros.getCell(6));
int houri = Integer.parseInt("-" + hourCy);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = sdf.parse(datestr);
Calendar cal = Calendar.getInstance();
cal.setTime(date);
cal.add(Calendar.HOUR, houri);
Format format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date deliveryDate = sdf.parse(sdf.format(cal.getTime()));
paichan.setDeliveryDate(deliveryDate);
paichan.setMaterialDesc("");
paichan.setMrpController("G86");
paichan.setOrderCode("1");
paichan.setSchedulerStaff("Z02");
paichan.setCreateUser(getUserName(request));
paichan.setCreateDate(new Date());
paichan.setIsuse("Y");
// paichan.setVersionId(versionId);版本号有后台更新
paichanDataManager.excelUpload(paichan);
}
}
}
if(param!=null && !batchNo.equals("")){
// request.getSession().removeAttribute("mapForexecut");
request.getSession().setAttribute("mapForexecut", param);
//批量导出排产结果时用到的
request.getSession().setAttribute("batchNo", batchNo);
out.println("<script>parent.callbackUpload('" + batchNo+ "')</script>");
}else{
}
} catch (Exception e) {
e.printStackTrace();
out.println("<script>parent.callbackUpload('error')</script>");
}
return null;
}
======================================================导出csv===============================================================
package com.yutong.util;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.supercsv.io.CsvBeanWriter;
import org.supercsv.io.CsvMapWriter;
import org.supercsv.io.ICsvBeanWriter;
import org.supercsv.io.ICsvMapWriter;
import org.supercsv.prefs.CsvPreference;
public class ExportToCsv {
public static void exportMapToExcel(HttpServletResponse response, String filename, String[] header,String[] keys, List<Map<String,Object>> reportList ){
ICsvMapWriter writer = null;
try {
response.reset(); // 清空输出流
filename = new String(filename.getBytes("gbk"), "ISO-8859-1");
response.setHeader("Content-disposition", "attachment; filename=" + filename + ".csv");
// 设定输出文件头
response.setCharacterEncoding("gbk");
response.setContentType("APPLICATION/OCTET-STREAM"); // 定义输出类型
writer = new CsvMapWriter(response.getWriter(), CsvPreference.EXCEL_PREFERENCE);
writer.writeHeader(header);
for(int i=0;i<reportList.size();i++){
writer.write((Map<String, Object>)reportList.get(i), keys);
}
// 立即输出
response.flushBuffer();
writer.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally{
try {
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
@SuppressWarnings("unchecked")
public static void exportBeanToExcel(HttpServletResponse response, String filename, String[] header,String[] keys, List reportList ){
ICsvBeanWriter writer = null;
try {
response.reset(); // 清空输出流
filename = new String(filename.getBytes("gbk"), "ISO-8859-1");
response.setHeader("Content-disposition", "attachment; filename=" + filename + ".csv");
// 设定输出文件头
response.setCharacterEncoding("gbk");
response.setContentType("APPLICATION/OCTET-STREAM"); // 定义输出类型
writer = new CsvBeanWriter(response.getWriter(), CsvPreference.EXCEL_PREFERENCE);
writer.writeHeader(header);
for(int i=0;i<reportList.size();i++){
Object values = reportList.get(i);
writer.write(values, keys);
}
// 立即输出
response.flushBuffer();
writer.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally{
try {
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
