package com;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import javax.swing.JOptionPane;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* @author 李高松
* @function 使用poi读取Excel文件完成对资产表的读取生成设备跟踪表和人员跟踪表
* @date 2012年6月27日 15:36:18
* @version 1.0
*/
public class ReadExcel {
public static void main(String[] args) {
Properties prop = new Properties();
InputStream in = ReadExcel.class
.getResourceAsStream("/path.properties");
String url = "";
try {
prop.load(in);
url = prop.getProperty("path").trim();
JOptionPane.showMessageDialog(null, "点击确定执行！");
WriteExcelSBGZ(url);// 生成设备文件
WriteExcelRYGZ(url);// 生成人员文件
JOptionPane.showMessageDialog(null, "请稍候！处理中......");
JOptionPane.showMessageDialog(null, "处理完成！");
} catch (Exception e) {
e.printStackTrace();
}
}
public static List<Property> ReadExcel(String url) {
File f = new File(url + "资产总表.xls");// 读取excel文件
try {
FileInputStream is = new FileInputStream(f);// 创建文件流
HSSFWorkbook wbs = new HSSFWorkbook(is);// 加载文件流
HSSFSheet childSheet = wbs.getSheetAt(0);// 读取第一个Sheet
// System.out.println("总行数：" + childSheet.getLastRowNum());
List<Property> lt = new ArrayList<Property>();
for (int i = 1; i < childSheet.getLastRowNum() + 1; i++) {// 遍历行
HSSFRow row = childSheet.getRow(i);// 获得行对象
if (null != row) {
HSSFCell cell = row.getCell(0);
// System.out.println("总列数：" + row.getLastCellNum());
Property ppt = new Property();
ppt.setSid(row.getCell(0).toString());
ppt.setWlgs(row.getCell(1).toString());
ppt.setSbtype(row.getCell(2).toString());
ppt.setPp(row.getCell(3).toString());
ppt.setGgxh(row.getCell(4).toString());
ppt.setSbid(row.getCell(5).toString());
ppt.setCcid(row.getCell(9).toString());
ppt.setChushi(row.getCell(12).toString());
ppt.setName(row.getCell(14).toString());
lt.add(ppt);
}
}
is.close();
return lt;
} catch (FileNotFoundException e) {
JOptionPane.showMessageDialog( null,"路径错误！请检查路径");
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
public static void WriteExcelSBGZ(String url) {
try {
List<Property> lt = ReadExcel( url);
for (int i = 0; i < lt.size(); i++) {
File f = new File(url+"设备跟踪表.xls");// 读取excel文件
FileInputStream is = new FileInputStream(f);// 创建文件流
HSSFWorkbook wb = new HSSFWorkbook(is);
HSSFSheet sheet = wb.getSheetAt(0); // 第一个工作表
HSSFRow row = sheet.getRow(3);// 获得行对象
row.getCell(2).setCellValue(lt.get(i).getSbid());
row.getCell(6).setCellValue(lt.get(i).getSid());
row.getCell(10).setCellValue(lt.get(i).getSbtype());
HSSFRow row1 = sheet.getRow(4);// 获得行对象
row1.getCell(2).setCellValue(lt.get(i).getPp());
row1.getCell(6).setCellValue(lt.get(i).getGgxh());
row1.getCell(10).setCellValue(lt.get(i).getCcid());
FileOutputStream fileOut = new FileOutputStream(
url+"设备跟踪表-" + lt.get(i).getSbid() + ".xls");
wb.write(fileOut);
is.close();
fileOut.close();
}
} catch (FileNotFoundException e) {
JOptionPane.showMessageDialog( null,"路径错误！请检查路径");
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
@SuppressWarnings("unused")
public static void WriteExcelRYGZ(String url) {
HSSFWorkbook wb = null;
File f;
FileInputStream is = null;
FileOutputStream fileOut = null;
try {
List<Property> lt = ReadExcel(url);
for (int i = 0; i < lt.size(); i++) {
if (i == 0) {
f = new File(url + "人员跟踪表.xls");// 读取excel文件
is = new FileInputStream(f);// 创建文件流
} else {
try {
f = new File(url + "人员跟踪表-" + lt.get(i).getChushi()
+ "-" + lt.get(i).getName() + ".xls");// 读取excel文件
is = new FileInputStream(f);// 创建文件流
} catch (Exception e) {
f = new File(url + "人员跟踪表.xls");// 读取excel文件
is = new FileInputStream(f);// 创建文件流
}
}
if (null != is) {
wb = new HSSFWorkbook(is);
HSSFSheet sheet = wb.getSheetAt(0); // 第一个工作表
HSSFRow row1 = sheet.getRow(1);
if (!lt.get(i).getName().equals(row1.getCell(7).toString())) {
row1.getCell(1).setCellValue(lt.get(i).getChushi());
row1.getCell(7).setCellValue(lt.get(i).getName());
HSSFRow row = sheet.getRow(3);// 获得行对象
row.getCell(1).setCellValue(lt.get(i).getSbtype());
row.getCell(2).setCellValue(lt.get(i).getWlgs());
row.getCell(3).setCellValue(lt.get(i).getSbid());
row.getCell(4).setCellValue(lt.get(i).getCcid());
fileOut = new FileOutputStream(url + "人员跟踪表-"
+ lt.get(i).getChushi() + "-"
+ lt.get(i).getName() + ".xls");
wb.write(fileOut);
is.close();
fileOut.close();
} else {
int n = 5;
if (!(sheet.getRow(n).getCell(1).toString() == "")) {
n = n + 2;
}
HSSFRow row = sheet.getRow(n);// 获得行对象
row.getCell(1).setCellValue(lt.get(i).getSbtype());
row.getCell(2).setCellValue(lt.get(i).getWlgs());
row.getCell(3).setCellValue(lt.get(i).getSbid());
row.getCell(4).setCellValue(lt.get(i).getCcid());
fileOut = new FileOutputStream(url + "人员跟踪表-"
+ lt.get(i).getChushi() + "-"
+ lt.get(i).getName() + ".xls");//另存文件
wb.write(fileOut);
is.close();
fileOut.close();
}
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
|
/**
* 设置下载文件中文件的名称
*
* @param filename
* @param request
* @return
*/
public static String encodeFilename(String filename, HttpServletRequest request) {
/**
* 获取客户端浏览器和操作系统信息
* 在IE浏览器中得到的是：User-Agent=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Maxthon; Alexa Toolbar)
* 在Firefox中得到的是：User-Agent=Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.7.10) Gecko/20050717 Firefox/1.0.6
*/
String agent = request.getHeader("USER-AGENT");
try {
if ((agent != null) && (-1 != agent.indexOf("MSIE"))) {
String newFileName = URLEncoder.encode(filename, "UTF-8");
newFileName = StringUtils.replace(newFileName, "+", "%20");
if (newFileName.length() > 150) {
newFileName = new String(filename.getBytes("GB2312"), "ISO8859-1");
newFileName = StringUtils.replace(newFileName, " ", "%20");
}
return newFileName;
}
if ((agent != null) && (-1 != agent.indexOf("Mozilla")))
return MimeUtility.encodeText(filename, "UTF-8", "B");
return filename;
} catch (Exception ex) {
return filename;
}
}
|
