- public void outPutExcel(List<InvoiceRequisitionSo> result,HttpServletResponse response ){
- OutputStream os = null;
- try{
- os = response.getOutputStream();
- }catch (IOException el){
- }
- response.reset();
- response.setHeader("Content-disposition", "attachment; filename=InvoiceRequisitionListToExcel.xls");
- response.setContentType("application/msexcel");
- WritableWorkbook wwb=null;
- WritableSheet ws=null;
- WritableCellFormat cellFormat = new WritableCellFormat();
- WritableCellFormat cellFormatContent = new WritableCellFormat();
- WritableFont font= new WritableFont(WritableFont.createFont("宋体"),10, WritableFont.NO_BOLD);
- WritableFont font1= new WritableFont(WritableFont.createFont("宋体"),10, WritableFont.NO_BOLD);
- try{
- wwb = Workbook.createWorkbook(os);
- ws = wwb.createSheet("总部发票申请", 0);
- font.setColour(Colour.WHITE);
- WritableCellFormat cellFormatContentt = new WritableCellFormat(font);
- font1.setColour(Colour.RED);
- WritableCellFormat cellFormatContentred = new WritableCellFormat(font1);
- ws.getSettings().setDefaultColumnWidth(18);
- //设置单元格格式
- cellFormat.setBackground(Colour.GRAY_25);
- cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
- cellFormatContent.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
- cellFormatContentt.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
- int i = 0;
- for (InvoiceRequisitionSo so : result) {
- ws.addCell(new Label(0,i,so.getInvoice(),cellFormatContentred ));
- //合并单元格
- ws.mergeCells(1, i , 2, i );
- ws.addCell(new Label(1, i, so.getEno(), cellFormatContent));
- i++;
- }
- }catch (Exception e){
- } finally{
- try {
- wwb.write();
- wwb.close();
- os.close();
- } catch (Exception e) {
- }
- }
- }
----------------------------------------------------------------------------------------------------------------------------------------------------------------
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
public class ExcelRead {
//判断excel版本
static Workbook openWorkbook(InputStream in,String filename,String fileFileName)throws IOException{
Workbook wb = null;
if(fileFileName.endsWith(".xlsx")){
wb = new XSSFWorkbook(in);//Excel 2007
} else {
wb = (Workbook) new HSSFWorkbook(in);//Excel 2003
}
return wb;
}
public static List<String[]> getExcelData(String fileName,String fileFileName) throws Exception {
InputStream in = new FileInputStream(fileName); //创建输入流
Workbook wb = openWorkbook(in, fileName,fileFileName);// 获取Excel文件对象
Sheet sheet = wb.getSheetAt(0);// 获取文件的指定工作表m 默认的第一个
List<String[]> list = new ArrayList<String[]>();
Row row = null;
Cell cell = null;
int totalRows = sheet.getPhysicalNumberOfRows(); // 总行数
int totalCells = sheet.getRow(0).getPhysicalNumberOfCells();//总列数
for (int i = 0; i < totalRows; i++) {
// 创建一个数组 用来存储每一列的值
String[] str = new String[totalRows];
row = sheet.getRow(i);
for (int j = 0; j < totalCells; j++) {
cell = (Cell) sheet.getCellComment(j, i);
cell = row.getCell(j);
System.out.println(j+"DDDDDDDDDD");
//str[j] = cell.getRow();
}
// 把刚获取的列存入list
list.add(str);
}
for(int r=0; r<totalRows; r++) {
row = sheet.getRow(r);
System.out.print("第" + r + "行");
for(int c = 0; c < totalCells; c++){
cell = row.getCell(c);
String cellValue = "";
if(null != cell){
// 以下是判断数据的类型
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
cellValue = cell.getNumericCellValue() + "";
// 时间格式
// if(HSSFDateUtil.isCellDateFormatted(cell)){
// Date dd = cell.getDateCellValue();
// DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// cellValue = df.format(dd);
// }
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
System.out.print(" "+cellValue+"\t");
}
}
System.out.println();
}
// 返回值集合
return list;
}
public static void main(String[] args) throws Exception{
String fileName = "C:/Users/w520-2/Desktop/供应商系统中英文对照.xlsx";
ExcelRead upload = new ExcelRead();
upload.getExcelData(fileName,".xls");
}
}