import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
/**
* 操作Excel表格的功能类
*/
public class ExcelReader {
private Workbook wb;
private Sheet sheet;
private Row row;
/**
* 读取Excel表格表头的内容
* @param InputStream
* @return String 表头内容的数组
* @throws InvalidFormatException
*/
public String[] readExcelTitle(InputStream is) throws InvalidFormatException {
try {
wb = WorkbookFactory.create(is);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
String[] title = new String[colNum];
for (int i = 0; i < colNum; i++) {
title[i] = getStringCellValue(row.getCell(i));
}
return title;
}
/**
* 读取Excel数据内容
* @param InputStream
* @return Map 包含单元格数据内容的Map对象
* @throws InvalidFormatException
*/
public List<String[]> readExcelContent(InputStream is) throws InvalidFormatException {
try {
wb = WorkbookFactory.create(is);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
List<String[]> list = new ArrayList<String[]>();
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
String content[] = new String[colNum];
int j = 0;
while (j < colNum) {
content[j] = this.getStringCellValue(row.getCell(j));
j++;
}
list.add(content);
}
return list;
}
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private String getStringCellValue(Cell cell) {
String strCell = "";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue().trim();
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
strCell = sdf.format(cell.getDateCellValue());
}else{
strCell = String.valueOf(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
return strCell;
}
/**
* 写入内容
* @param is
* @param sheetName
* @param list
* @throws IOException
* @throws InvalidFormatException
*/
public void writeExcelContent(InputStream is,String sheetName,List<String[]> list,OutputStream os) throws Exception{
wb = WorkbookFactory.create(is);
sheet= wb.getSheetAt(0);
CellStyle style = wb.createCellStyle();
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setAlignment(CellStyle.ALIGN_CENTER);
Font font = wb.createFont();
font.setFontHeightInPoints((short) 9);
style.setFont(font);
for(int i=0;i<list.size();i++){
String[] obj = list.get(i);
row = sheet.createRow(i+1);
for(int j=0;j<obj.length;j++){
Cell cell = row.createCell(j);
cell.setCellStyle(style);
cell.setCellValue(obj[j]);
}
}
wb.write(os);
os.close();
}
public static void main(String[] args) throws Exception{
try {
// 对读取Excel表格标题测试
InputStream is = new FileInputStream("d:\\test.xlsx");
ExcelReader excelReader = new ExcelReader();
String[] title = excelReader.readExcelTitle(is);
System.out.println("获得Excel表格的标题:");
for (String s : title) {
System.out.print(s + " ");
}
// 对读取Excel表格内容测试
InputStream is2 = new FileInputStream("d:\\test.xlsx");
List<String[]> list = excelReader.readExcelContent(is2);
System.out.println("获得Excel表格的内容:");
for (int i = 0; i < list.size(); i++) {
String[] s = list.get(i);
for(int j=0;j<s.length;j++){
System.out.print(s[j] + " ");
}
System.out.println("\n");
}
} catch (FileNotFoundException e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
}
}
}
/**
*
* @Title: export
* @Description: 导出Excel
*/
public void export(Context context){
HttpServletRequest request = context.request;
HttpServletResponse response = context.response;
String filePath = request.getSession().getServletContext().getRealPath("/template/detail.xlsx");
OutputStream os = null;
FileInputStream fis = null;
try {
response.reset();
response.setCharacterEncoding("gb2312");
String fileName = "转账明细.xlsx";
String name = new String(fileName.getBytes("gb2312"), "ISO8859-1");
response.setHeader( "Content-Disposition","attachment; filename=" + name);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
fis = new FileInputStream(filePath);
os = response.getOutputStream();
List<String[]> list = service.findAll();
ExcelReader er = new ExcelReader();
er.writeExcelContent(fis, "转账明细", list, os);
os.flush();
response.flushBuffer();
} catch (Exception e) {
logger.error(e.getMessage(), e);
} finally {
try {
if (fis != null) fis.close();
if (os != null) os.close();
} catch (Exception e2) {
logger.error(e2.getMessage(), e2);
}
}
}