`
aa860326
  • 浏览: 95739 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

JSP 页面使用JXL读取excel

    博客分类：
  • J2EE
ExcelJSPJ#
阅读更多

1、index.jsp页面
<body>
  <form action="excel_view.jsp" method="post">
    <input type="file" name="filepath">
    <input type="submit" name="submit" value="提交">
  </form>
  </body>
----------------------------------------------------------------------
2、excel_view.jsp页面
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ page import="java.io.*" %>
<%@ page import="jxl.Cell"%>
<%@ page import="jxl.CellType"%>
<%@ page import="jxl.Sheet"%>
<%@ page import="jxl.Workbook"%>
<%@ page import="jxl.read.biff.BiffException"%>
<%@ page import="com.method.CellStyle" %>



<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

<title>查看excel文件</title>

<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->

</head>

<body>
<%
request.setCharacterEncoding("UTF-8");
try {
InputStream is = new FileInputStream(request.getParameter("filepath"));
Workbook read_workbook = Workbook.getWorkbook(is);
Sheet sheet = read_workbook.getSheet(0);
CellStyle cell_style = new CellStyle();
int rows = sheet.getRows();
int columns = sheet.getColumns();
int rowspan = 0;
int colspan = 0;
out.print("<table border='1' cellpadding='0' cellspacing='0' style='font-size: 12px;border-collapse:collapse' bordercolor='#000000'>");
for (int i = 0; i < rows; i++) {
out.print("<tr>");
for (int j = 0; j < columns ; j++) {
Cell cell = sheet.getCell(j, i);
cell_style.ReadRange(sheet,i,j);
rowspan = cell_style.getRowspan();
colspan = cell_style.getColspan();
String contents = null;
if(cell.getType() == CellType.EMPTY){
cell_style.setBetweenRowColumn(i,j);
int min_row = cell_style.getMin_row();
int max_row = cell_style.getMax_row();
int min_col = cell_style.getMin_column();
int max_col = cell_style.getMax_column();
if( min_row < i && i <= max_row && min_col <= j && j<= max_col){
j = max_col;
continue;
}
contents = "&nbsp;";
out.print("<td width='"+(40*colspan)+"' rowspan='"+rowspan+"' colspan='"+colspan+"'>"+contents+"</td>");
j += colspan-1;
continue;
} else {
contents = cell.getContents();
out.print("<td align='center'"
+"rowspan='"+rowspan+"'"
+"colspan='"+colspan+"'"
+"bGcolor='"+cell_style.getBgcolor(cell)+"'"
//+" height='"+sheet.getSettings().getDefaultRowHeight()+"'"
+"height='25'"
+" width='"+(60*colspan)+"'>"
+"<Font color='"+cell_style.getFontColor(cell)+"'"
//+"size='"+cell.getCellFormat().getFont().getPointSize()+"'"
+"face='"+cell.getCellFormat().getFont().getName()+"'"
+">"+contents+"</Font></td>");
j += colspan-1;
continue;
}
}
out.print("</tr>");
}
read_workbook.close();
is.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
%>
</body>
</html>
----------------------------------------------------------------------
3、CellStyle.java
package com.method;

import jxl.Cell;
import jxl.Range;
import jxl.Sheet;

/**
* 获取Excel 里被合并单元格的样式
* @author JinYunHe
*
*/
public class CellStyle {
private int topleft_row ; //起始行
private int topleft_column; //起始列
private int bottomright_row = 0; //终止行
private int bottomright_column = 0; //终止列
private int rowspan = 1; //行
private int colspan = 1; //列
private Range[] ranges = null; //合并单元格集合
private int min_row ; //最小行
private int max_row ; //最大行
private int min_column; //最小列
private int max_column; //最大列
/**
* 获取本sheet里合并单元格的集合
* @param sheet
*/
public void ReadRange(Sheet sheet,int row,int column){
ranges = sheet.getMergedCells();
for(Range space:ranges){

//System.out.print(space.getTopLeft().getRow()+"行,");
    //System.out.print(space.getTopLeft().getColumn()+"列\t");
    //System.out.print(space.getBottomRight().getRow()+"行,");
    //System.out.print(space.getBottomRight().getColumn()+"列\n");
    //System.out.println("\n-------------------------");
    ///*
this.topleft_row = space.getTopLeft().getRow();
this.topleft_column = space.getTopLeft().getColumn();
if(row != topleft_row || column != topleft_column){
this.setRowspan(1);
this.setColspan(1);
}
if(row == topleft_row && column == topleft_column){
this.setTopleft_row(this.topleft_row);
this.setTopleft_column(this.topleft_column);
this.setBottomright_row(space.getBottomRight().getRow());
this.setBottomright_column(space.getBottomRight().getColumn());
this.setRowspan(2);
this.setColspan(2);
break;
}
//*/
}
//System.out.println("xxxxxxxxxxxxxxxxxxxxxxxxxx");
}
/**
*
* @return
*/
public void setBetweenRowColumn(int row ,int column){
for(Range space:ranges){
//获取行的范围
int min_row = space.getTopLeft().getRow(); // min row
int max_row = space.getBottomRight().getRow(); // max row
int min_column = space.getTopLeft().getColumn(); // min column
int max_column = space.getBottomRight().getColumn(); // max column
if(min_row <= row
&& row <= max_row){
//获取列的范围
if(min_column <= column
&& column <= max_column){
this.setMin_row(min_row); // min row
this.setMax_row(max_row); // max row
this.setMin_column(min_column); // min column
this.setMax_column(max_column); // max column
break;
}
}
}
}
/**
* 获取背景颜色的值
* @param cell: 当前单元格
* @return
*/
public String getBgcolor(Cell cell){
int r = cell.getCellFormat().getBackgroundColour().getDefaultRGB().getRed();
int g = cell.getCellFormat().getBackgroundColour().getDefaultRGB().getGreen();
int b = cell.getCellFormat().getBackgroundColour().getDefaultRGB().getBlue();
return "#"+Integer.toHexString(r<<16|g<<8|b);
}
/**
* 获取字体的颜色
* @param cell: 当前单元格
* @return
*/
public String getFontColor(Cell cell){
int r = cell.getCellFormat().getFont().getColour().getDefaultRGB().getRed();
int g = cell.getCellFormat().getFont().getColour().getDefaultRGB().getGreen();
int b = cell.getCellFormat().getFont().getColour().getDefaultRGB().getBlue();
return "#"+Integer.toHexString(r<<16|g<<8|b);
}
public int getTopleft_row() {
return topleft_row;
}
public void setTopleft_row(int topleft_row) {
this.topleft_row = topleft_row;
}
public int getTopleft_column() {
return topleft_column;
}
public void setTopleft_column(int topleft_column) {
this.topleft_column = topleft_column;
}
public int getBottomright_row() {
return bottomright_row;
}
public void setBottomright_row(int bottomright_row) {
this.bottomright_row = bottomright_row;
}
public int getBottomright_column() {
return bottomright_column;
}
public void setBottomright_column(int bottomright_column) {
this.bottomright_column = bottomright_column;
}
public int getRowspan() {
return rowspan;
}
public void setRowspan(int rowspan) {
// rowspan :1 表示无; 2 表示有;
if(rowspan == 1){
this.rowspan = 1;
}
if(rowspan == 2){
this.rowspan =  this.getBottomright_row() - this.getTopleft_row() +1;
}
}
public int getColspan() {
return colspan;
}
public void setColspan(int colspan) {
// colspan :1 表示无; 2 表示有;
if(colspan == 1){
this.colspan = 1;
}
if(colspan == 2){
this.colspan =  this.getBottomright_column() - this.getTopleft_column() +1;
}
}
//------------------------获取所合并的单元格的的所表示范围的行和列-----------
public Range[] getRanges() {
return ranges;
}
public void setRanges(Range[] ranges) {
this.ranges = ranges;
}
public int getMin_row() {
return min_row;
}
public void setMin_row(int min_row) {
this.min_row = min_row;
}
public int getMax_row() {
return max_row;
}
public void setMax_row(int max_row) {
this.max_row = max_row;
}
public int getMin_column() {
return min_column;
}
public void setMin_column(int min_column) {
this.min_column = min_column;
}
public int getMax_column() {
return max_column;
}
public void setMax_column(int max_column) {
this.max_column = max_column;
}
}

注： 这个例子虽能把excel里的数据输出到jsp页面上，但还不够完美： 还未实现读公式，布尔型等类型的值，同时字体大小不能很好的控制。字体颜色、单元格的背景色，都是通过计算得出的(*_o只因不会直接使用Colour 这个类。)，同时代码也比较烦琐，希望有兴趣的高手，帮忙修改，先谢谢了！

分享到：
| 使用JXL 读取Excel合并单元格方法
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics