Java读写Excel文件DEMO

下载⼀定格式的Excel⽂件：

　　@RequestMapping(\"/xxxx/xxxx/xxxx/copyfiledownload\") @ResponseBody

public void copyfiledownload(HttpServletRequest request, HttpServletResponse response, Model model) throws Exception{ response.setContentType(\"text/html; charset=GBK\");

String basePath = request.getSession().getServletContext().getRealPath(\"/\"); File file = new File(basePath + \"xxxx.xls\");

FileOutputStream fos=new FileOutputStream(file); HSSFWorkbook wb=new HSSFWorkbook(); HSSFSheet sheet=wb.createSheet(); wb.setSheetName(0, \"sheet0\"); HSSFRow row=null; HSSFCell cell=null;

row=sheet.createRow(0); cell=row.createCell(0); cell.setCellValue(\"xxID\"); cell=row.createCell(1);

cell.setCellValue(\"每ID的总限量\"); cell=row.createCell(2);

cell.setCellValue(\"每⽤户对此ID最⼤购买量\"); cell=row.createCell(3);

cell.setCellValue(\"xx售卖价格\");

cell=row.createCell(4); cell.setCellValue(\"S时间\"); cell=row.createCell(5); cell.setCellValue(\"X时间\");

CellStyle cellStyle = wb.createCellStyle();

CreationHelper helperDate = wb.getCreationHelper();

cellStyle.setDataFormat(helperDate.createDataFormat().getFormat(\"yy/mm/dd/ hh:mm:ss\")); HSSFRow rowD=null; HSSFCell cellD=null;

for (int i = 1; i < 1000; i++) { rowD=sheet.createRow(i); cellD=rowD.createCell(4); cellD.setCellStyle(cellStyle); cellD=rowD.createCell(5); cellD.setCellStyle(cellStyle); } // 设置时间格式

cell=row.createCell(6);

cell.setCellValue(\"xx平台\");

//CellRangeAddressList(firstRow, lastRow, firstCol, lastCol)设置⾏列范围

CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, 6, 6);

String[] pos = {\"PC\ DataValidationHelper helper = sheet.getDataValidationHelper();

DataValidationConstraint constraint = helper.createExplicitListConstraint(pos); DataValidation dataValidation = helper.createValidation(constraint, addressList); if(dataValidation instanceof XSSFDataValidation) { dataValidation.setSuppressDropDownArrow(true); dataValidation.setShowErrorBox(true); }else {

dataValidation.setSuppressDropDownArrow(false); }

sheet.addValidationData(dataValidation); wb.write(fos); fos.close();

response.setContentType(\"application/x-msdownload\"); response.setContentLength((int) file.length());

response.setHeader(\"Content-Disposition\new String(file.getName().getBytes(\"gbk\"), \"iso-8859-1\")); FileInputStream fis = new FileInputStream(file);

BufferedInputStream buff = new BufferedInputStream(fis); byte[] b = new byte[1024]; long k = 0;

OutputStream myout = response.getOutputStream(); while (k < file.length()) {

int j = buff.read(b, 0, 1024); k += j;

myout.write(b, 0, j); }

myout.flush(); buff.close(); fis.close(); myout.close(); file.delete(); }

Excel⽂件读取：

　　 /**

* 读取excel表头 *

* @param file * @return

* @throws IOException */

@SuppressWarnings(\"unused\")

private String[] readExcelHead(File file) throws IOException {

HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file)); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; row = sheet.getRow(0);

String[] buff = new String[row.getLastCellNum()];

for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { cell = row.getCell(i);

buff[i] = cell.getStringCellValue(); }

return buff; }

　　 /**

* 读取2003excel *

* @param file * @return */

private List> read2003Excel(File file) throws IOException { List> dataList = new ArrayList>();

HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file)); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; Object val = null;

DecimalFormat df = new DecimalFormat(\"0\");// 格式化数字

DecimalFormat df2 = new DecimalFormat(\"#0.00\");// 格式化⼩数

SimpleDateFormat sdf = new SimpleDateFormat(\"yyyy-MM-dd HH:mm:ss\");// 格式化⽇期字符串 for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); if (row == null) { continue; }

List