вот пример. используется библиотека jxl
| Код |
package org.umek.bts;
import java.io.IOException;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.OutputStream;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class export_xls_status extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try{
OutputStream out = null;
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=compile.xls");
HttpSession session = request.getSession(true);
String SQL = ""; // запрос тут
Connection conn = (Connection)session.getAttribute("connect");
Statement stm = conn.createStatement();
ResultSet rs = stm.executeQuery(SQL);
ResultSetMetaData meta = rs.getMetaData();
String columnName;
WritableWorkbook w = Workbook.createWorkbook(response.getOutputStream());
WritableSheet s = w.createSheet("report1", 0);
WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableFont font_c = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableFont font_bold = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableFont font_bold_left = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat format = new WritableCellFormat(font);
WritableCellFormat format_c = new WritableCellFormat(font_c);
WritableCellFormat format_header = new WritableCellFormat(font_bold);
WritableCellFormat format_footer = new WritableCellFormat(font_bold_left);
try{ // настройка ширины столбцов
s.setColumnView(0, 1);
s.setColumnView(1, 27);
s.setColumnView(2, 20);
s.setColumnView(3, 13);
s.setColumnView(4, 15);
s.setColumnView(5, 15);
s.setColumnView(6, 15);
s.setColumnView(7, 15);
s.setColumnView(8, 15);
s.setColumnView(9, 17);
s.setColumnView(10, 17);
format_header.setAlignment(Alignment.CENTRE);
format_c.setAlignment(Alignment.CENTRE);
format_footer.setAlignment(Alignment.LEFT);
String period = "";
s.addCell(new Label(4, 2, "", format_header)); // заголовок
s.addCell(new Label(4, 3, "", format_header));
s.addCell(new Label(4, 4, "", format_header));
s.addCell(new Label(4, 6, "№ _____/_______ от ____/____/_______ года", format_c));
int j=11;
s.addCell(new Label(2, j-1, "столбец 1", format)); // названия столбцов
s.addCell(new Label(3, j-1, "столбец 1", format));
s.addCell(new Label(4, j-1, "столбец 1", format));
s.addCell(new Label(5, j-1, "столбец 1", format));
s.addCell(new Label(6, j-1, "столбец 1", format));
s.addCell(new Label(7, j-1, "столбец 1", format));
s.addCell(new Label(8, j-1, "столбец 1", format));
s.addCell(new Label(9, j-1, "столбец 1", format));
s.addCell(new Label(10, j-1, "столбец 1", format));
while (rs.next()){
for (int i=1; i<=meta.getColumnCount(); i++){
columnName = meta.getColumnName(i);
columnName = columnName.toLowerCase();
s.addCell(new Label(i, j, rs.getString(columnName), format));
}
j++;
}
s.addCell(new Label(2, j+4, "", format)); // футер
s.addCell(new Label(2, j+6, "", format));
w.write();
w.close();
}catch (Exception e){
throw new ServletException("Исключение при создании таблицы", e);
}finally{
if(out != null){
out.close();
}
}
}catch(SQLException ex){
ex.printStackTrace();
}
}
}
|
|