导出Excel，按照符合jxl的方式导出

来源：互联网发布：压缩文件夹 linux 编辑：程序博客网时间：2024/06/07 00:32

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");
}
}

0 0