/*
现在可以导出excel
现在我想实现 类别为一类的放在一个sheet里面
输出到table里面的数据的类别在数据库以经
*/
ORDER by lb asc </form>
<td colspan="9">
<form name="ExcelExportor" method="POST" action="ExcelExportor">
<input type="hidden" name="c" />
<input onclick="exportExcel('MAIN_TABLE')" type="button" class="mybutton" value="导出Excel">
</td><table width="980" align="center" id="MAIN_TABLE" cellspacing="0" summary="The technical specifications of the Apple PowerMac G5 series">
<tr class="title">
<td>規格</td>
<td>數量</td>
<td>日期</td>
<td>類別</td>
</tr>
<c:forEach items="${list}" var ="u">
<tr>
<td>${u.gg }</td>
<td>${u.sl}</td>
<td>${u.date}</td>
<td>${u.lb}</td>
</tr>
</c:forEach>
</table>
</form>
package com.name.servletexcle;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* 解析页面并导出为Excel格式。
*
* @author
*
*/
public class ExcelExportor extends HttpServlet {
private static final long serialVersionUID = 8563623076707865788L;
public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException {
request.setCharacterEncoding("GBK");
String content = request.getParameter("c");
if (content == null) {
Writer out = response.getWriter();
response.setCharacterEncoding("GBK");
out.write("<html><body>No Content</body></html>");
return;
}
try {
export(content, response);
} catch (Exception ex) {
ex.printStackTrace();
}
}
private WritableWorkbook wwb = null;
private WritableSheet sheet = null;
private void export(String content, HttpServletResponse response) throws IOException, RowsExceededException, WriteException {
response.setContentType("application/ms-excel");
String sheetName = getCaption(content);
if (sheetName == null) {
sheetName = "Sheet1";
}
sheetName = sheetName.replaceAll(":", "").replaceAll("[)]", "").replaceAll("[(]", "");
response.addHeader("Content-Disposition", "attachment; filename=" + new String(sheetName.getBytes("GBK"), "ISO-8859-1")
+ ".xls");
OutputStream os = response.getOutputStream();
wwb = Workbook.createWorkbook(os);
wwb.setProtected(true);
sheet = wwb.createSheet(sheetName, 0);
int row = 0;
int col = 0;
Label label = null;
if (sheetName.trim().length() > 30) {
label = new Label(col, row, sheetName);
sheet.addCell(label);
row++;
}
int column = 0;
//设置行的高度
// sheet.setRowView(5,60,true);
sheet.setRowView(1,400);
//设置列的宽度
sheet.setColumnView(column++, 30);
sheet.setColumnView(column++, 30);
sheet.setColumnView(column++, 30);
//sheet.setColumnView(column++, 30); List<TD> listBody = getContent(content);
Map<String, Boolean> map = new HashMap<String, Boolean>();
for (TD td : listBody) {
if (td == null) {
row++;
col = 0;
continue;
}
while (map.get(col + "-" + row) != null) {
col++;
}
if (td.colspan > 1 || td.rowspan > 1) {
sheet.mergeCells(col, row, col + td.colspan - 1, row + td.rowspan - 1);
for (int i = col; i <= col + td.colspan - 1; i++) {
for (int j = row; j <= row + td.rowspan - 1; j++) {
map.put(i + "-" + j, true);
}
}
}
label = new Label(col, row, td.content);
sheet.addCell(label); map.put(col + "-" + row, true);
col += td.colspan;
}
wwb.write();
wwb.close();
}
private String getCaption(String content) {
int begin = content.indexOf("<CAPTION");
int end = content.indexOf("</CAPTION>");
if (begin == -1 || end == -1) {
return null;
}
begin = content.indexOf(">", begin);
if (begin == -1) {
return null;
}
return content.substring(begin + 1, end);
}
public List<TD> getContent(String content) throws UnsupportedEncodingException {
int begin = -1;
int end = -1;
int index = -1;
String numberStr;
int number;
String[] tables = content.split("</TABLE>");
List<TD> list = new ArrayList<TD>();
for (String table : tables) {
String[] trs = table.split("</TR>");
for (String tr : trs) {
number = 1;
String[] ss = tr.split("</TD>");
for (String s : ss) {
begin = s.indexOf("<TD");
if (begin == -1) {
continue;
}
s = s.substring(begin + 3);
index = s.indexOf(">");
TD td = new TD();
begin = s.indexOf("rowSpan=");
if (begin != -1) {
end = s.indexOf(" ", begin);
if (end == -1) {
end = index;
}
numberStr = s.substring(begin + 8, end).replace('"', ' ').replace('\'', ' ').trim();
number = Integer.parseInt(numberStr);
td.rowspan = number;
}
begin = s.indexOf("colSpan=");
if (begin != -1) {
end = s.indexOf(" ", begin);
index = s.indexOf(">", begin);
if (end == -1) {
end = index;
}
if (end > index) {
end = index;
}
numberStr = s.substring(begin + 8, end).replace('"', ' ').replace('\'', ' ').trim();
number = Integer.parseInt(numberStr);
td.colspan = number;
}
td.content = s.substring(index + 1).replaceAll("\\<.*?\\>", "").replaceAll(" ", "").trim();
list.add(td);
}
list.add(null);
}
list.add(null);
list.add(null);
}
return list;
}
}
class TD {
int rowspan = 1;
int colspan = 1;
String content;
}
顶！
为每个类别元素建立一个sheet判断类别写入到不同的sheet里去
import jxl.*;
import jxl.format.*;
import jxl.write.*;
/**
* <p>Title: 个人工具包</p>
* <p>Description: 示例代码, 向Excel文件中写入工作表</p>
* <p>Copyright: Copyright (c) 2002</p>
* <p>Company: Tiger, Ltd.</p>
* @author Tim
* @version 1.0
*/
class ExcelWriting
{
public static void main(String[] args)
{
if(!(args.length == 1))
{
System.out.println("Usage: java ExcelWriting targetfile");
System.exit(0);
}
String targetfile = args[0];
try
{
//创建可写入的Excel工作薄
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile));
//将Excel工作薄对象写入到输出流
/*
OutputStream os = new FileOutputStream(targetfile);
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);
*/
//创建Excel工作表 这里可以加上循环创建多个Sheet表
jxl.write.WritableSheet ws = wwb.createSheet("Test Sheet 1", 0);
//向Excel工作表添加内容
//1.添加Label对象
jxl.write.Label labelC = new jxl.write.Label(0, 0, "This is a Label cell");
ws.addCell(labelC);
//添加带有字型Formatting的对象
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
jxl.write.Label labelCF = new jxl.write.Label(1, 0, "This is a Label Cell", wcfF);
ws.addCell(labelCF);
//添加带有字体颜色Formatting的对象
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
jxl.write.Label labelCFC = new jxl.write.Label(1, 0, "This is a Label Cell", wcfFC);
ws.addCell(labelCF);
//2.添加Number对象
jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926);
ws.addCell(labelN);
//添加带有formatting的Number对象
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
ws.addCell(labelNF);
//3.添加Boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(0, 2, false);
ws.addCell(labelB);
//4.添加DateTime对象
jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3, new java.util.Date());
ws.addCell(labelDT);
//添加带有formatting的DateFormat对象
jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 3, new java.util.Date(), wcfDF);
ws.addCell(labelDTF);
//写入Exel工作表
wwb.write();
//关闭Excel工作薄对象
wwb.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
private void export(String content, HttpServletResponse response) throws IOException, RowsExceededException, WriteException {
response.setContentType("application/ms-excel");
String sheetName = getCaption(content);
if (sheetName == null) {
sheetName = "Sheet1";
}
sheetName = sheetName.replaceAll(":", "").replaceAll("[)]", "").replaceAll("[(]", "");
response.addHeader("Content-Disposition", "attachment; filename=" + new String(sheetName.getBytes("GBK"), "ISO-8859-1")
+ ".xls");
OutputStream os = response.getOutputStream();
wwb = Workbook.createWorkbook(os);
wwb.setProtected(true);
//这个地方循环类别！来创建多个Sheet
for (int i = 0; i < TypeList.size(); i++) { WritableSheet sheet = wwb.createSheet(GuideType.getName(), i);
sheet = wwb.createSheet(sheetName, 0);
int row = 0;
int col = 0;
Label label = null;
if (sheetName.trim().length() > 30) {
label = new Label(col, row, sheetName);
sheet.addCell(label);
row++;
}
int column = 0;
//设置行的高度
// sheet.setRowView(5,60,true);
sheet.setRowView(1,400);
//设置列的宽度
sheet.setColumnView(column++, 30);
sheet.setColumnView(column++, 30);
sheet.setColumnView(column++, 30);
//sheet.setColumnView(column++, 30); List<TD> listBody = getContent(content);
Map<String, Boolean> map = new HashMap<String, Boolean>();
for (TD td : listBody) {
if (td == null) {
row++;
col = 0;
continue;
}
while (map.get(col + "-" + row) != null) {
col++;
}
if (td.colspan > 1 || td.rowspan > 1) {
sheet.mergeCells(col, row, col + td.colspan - 1, row + td.rowspan - 1);
for (int i = col; i <= col + td.colspan - 1; i++) {
for (int j = row; j <= row + td.rowspan - 1; j++) {
map.put(i + "-" + j, true);
}
}
}
label = new Label(col, row, td.content);
sheet.addCell(label); map.put(col + "-" + row, true);
col += td.colspan;
}
wwb.write();
wwb.close();
}
private String getCaption(String content) {
int begin = content.indexOf("<CAPTION");
int end = content.indexOf("</CAPTION>");
if (begin == -1 || end == -1) {
return null;
}
begin = content.indexOf(">", begin);
if (begin == -1) {
return null;
}
return content.substring(begin + 1, end);
}
public List<TD> getContent(String content) throws UnsupportedEncodingException {
int begin = -1;
int end = -1;
int index = -1;
String numberStr;
int number;
String[] tables = content.split("</TABLE>");
List<TD> list = new ArrayList<TD>();
for (String table : tables) {
String[] trs = table.split("</TR>");
for (String tr : trs) {
number = 1;
String[] ss = tr.split("</TD>");
for (String s : ss) {
begin = s.indexOf("<TD");
if (begin == -1) {
continue;
}
s = s.substring(begin + 3);
index = s.indexOf(">");
TD td = new TD();
begin = s.indexOf("rowSpan=");
if (begin != -1) {
end = s.indexOf(" ", begin);
if (end == -1) {
end = index;
}
numberStr = s.substring(begin + 8, end).replace('"', ' ').replace('\'', ' ').trim();
number = Integer.parseInt(numberStr);
td.rowspan = number;
}
begin = s.indexOf("colSpan=");
if (begin != -1) {
end = s.indexOf(" ", begin);
index = s.indexOf(">", begin);
if (end == -1) {
end = index;
}
if (end > index) {
end = index;
}
numberStr = s.substring(begin + 8, end).replace('"', ' ').replace('\'', ' ').trim();
number = Integer.parseInt(numberStr);
td.colspan = number;
}
td.content = s.substring(index + 1).replaceAll("\\<.*?\\>", "").replaceAll(" ", "").trim();
list.add(td);
}
list.add(null);
}
list.add(null);
list.add(null);
}
return list;
}
}
class TD {
int rowspan = 1;
int colspan = 1;
String content;
}
我用过JSP 生成 excel 很简单啊 JSP 页面头有专门转化成EXCEL的标签 为啥非的用HTML 写的这么麻烦 呵呵也在学习中
/*
//这个地方循环类别！来创建多个Sheet
for (int i = 0; i < TypeList.size(); i++) { WritableSheet sheet = wwb.createSheet(GuideType.getName(), i);
*/
//？？
//才接触jxl！
//現在那個能否說下我输入的类别=table表的类别、我就把sheet的名字设置我的类别 //这里是我输入的类别
String chan[] = (String[])request.getSession().getAttribute("choise");
int j = chan.length;
System.out.println(j);
for(int i=0;i<j;i++)
{
System.out.println(chan[i]);
}
//****************************//
//这是table表的类别
int rows = sheet.getRows();
int cols = sheet.getColumns();
Cell cells[][] = new Cell[cols][rows];
String[] content2 = new String[cols];
StringBuffer sb = new StringBuffer();
for(int i = 0;i < cols ; i ++){
sb.delete(0,sb.length());
for(int j = 0;j < rows; j ++){
cells[i][j] = sheet.getCell(i,j);
sb.append(cells[i][j].getContents()).append(",");
}
content2[i] = sb.toString();
int j = content2.length;
for(int k=0;k<1;k++){
System.out.println(content2[j-1]);
}
}
//************************//
自己也才接触！
看了1天的Api
实验了好多次就是没搞出来！
*/
import ..................省略.......................;
@Component("moreSheetsDao")
public class MoreSheetsDaoImpl implements MoreSheetsDao {
@Resource(name="jdbcTemplate") private JdbcTemplate jdbcTemplate;
@Override//得到要查询的数据
public List<Map> getAll() {
return jdbcTemplate.queryForList("select uid,uname,uaddress from user");
}
public static int maxSheetSize=6500;//windows excel每个sheet规定最多6500行
public static void main(String[] args) throws Exception{//使用main方法测试
ApplicationContext a = new ClassPathXmlApplicationContext("applicationContext.xml");
MoreSheetsDaoImpl ms = (MoreSheetsDaoImpl)a.getBean("moreSheetsDao");
String filenames = "参会人汇总信息表.xls";
WritableWorkbook book = Workbook.createWorkbook(new File("D:"+File.separator+filenames));
List<Map> listOut = ms.getAll();
WritableSheet sheet = null;
int sheets =getsheetsNumber(listOut.size());
这里定义个一个flag String lastType = "";
for(int i = 0;i<sheets;i++){//
这里判断上一个lastType 是否和当期那的相同，不同就创建
if(！ lastType .equals(map.get(----数据库标志位字段---))){
sheet = book.createSheet("sheet"+i, i);
}
WritableFont wf = new WritableFont(WritableFont.ARIAL, 15,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.CORAL); // 定义格式 字体 下划线 斜体 粗体 颜色
sheet.addCell(new Label(0, 0, "账号"));
sheet.addCell(new Label(1, 0, "姓名"));
sheet.addCell(new Label(2, 0, "地址")); int rowCoordinate =1;//设定行坐标
for (int j = i*maxSheetSize; j <(i+1)*maxSheetSize; j++) {
if (j<listOut.size()) {
Map<Object, Object> map = listOut.get(j);
sheet.addCell(new Label(0,rowCoordinate , String.valueOf(map.get("uid"))));
sheet.addCell(new Label(1,rowCoordinate , String.valueOf(map.get("uname"))));
sheet.addCell(new Label(2,rowCoordinate , String.valueOf(map.get("uaddress")))); rowCoordinate ++;
}
每次运行完以后赋值lastType=map.get("------标志位字段-----")；
}
}
book.write();
book.close();
}
private static int getsheetsNumber(int size) {
if (size %maxSheetSize==0) {
return size/maxSheetSize;
}
return size/maxSheetSize+1;
}
}
兄弟，我这里是按照6500分一个sheet那么你在外层循环的时候就判断标志位不就Ok了？