package com.zskx.pem.standalone.commons;
import java.io.IOException;
import java.io.Writer;
import java.util.Calendar;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellReference;
public class SpreadsheetWriter {
private final Writer _out;
private int _rownum;
public SpreadsheetWriter(Writer out){
_out = out;
}
public void beginSheet() throws IOException {
_out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
_out.write("<sheetData>\n");
}
public void endSheet() throws IOException {
_out.write("</sheetData>");
_out.write("</worksheet>");
}
/**
* Insert a new row
*
* @param rownum 0-based row number
*/
public void insertRow(int rownum) throws IOException {
_out.write("<row r=\""+(rownum+1)+"\">\n");
this._rownum = rownum;
}
/**
* Insert row end marker
*/
public void endRow() throws IOException {
_out.write("</row>\n");
}
public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("<is><t>"+value+"</t></is>");
_out.write("</c>");
}
public void createCell(int columnIndex, String value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\""+ref+"\" t=\"n\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("<v>"+value+"</v>");
_out.write("</c>");
}
public void createCell(int columnIndex, double value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
}
}
package com.zskx.pem.standalone.commons;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class ExcelUtil {
private HSSFWorkbook wb = null;
private HSSFSheet sheet = null;
private HSSFRow row = null;
private int sheetNum = 0;
private int rowNum = 0;
private FileInputStream fis = null;
private File file = null;
public ExcelUtil() {
}
public ExcelUtil(File file) {
this.file = file;
}
public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}
public void setSheetNum(int sheetNum) {
this.sheetNum = sheetNum;
}
public void setFile(File file) {
this.file = file;
}
/**
* 读取excel文件获得HSSFWorkbook对象
*/
public void open() throws IOException {
fis = new FileInputStream(file);
wb = new HSSFWorkbook(new POIFSFileSystem(fis));
fis.close();
}
/**
* 返回sheet表数目
*/
public int getSheetCount() {
int sheetCount = -1;
sheetCount = wb.getNumberOfSheets();
return sheetCount;
}
/**
* sheetNum下的记录行数
*/
public int getRowCount() {
if (wb == null)
System.out.println("=============>WorkBook为空");
HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
int rowCount = -1;
rowCount = sheet.getLastRowNum();
return rowCount;
}
/**
* 读取指定sheetNum的rowCount
*/
public int getRowCount(int sheetNum) {
HSSFSheet sheet = wb.getSheetAt(sheetNum);
int rowCount = -1;
rowCount = sheet.getLastRowNum();
return rowCount;
}
/**
* 得到指定行的内容
*/
public String[] readExcelLine(int lineNum) {
return readExcelLine(this.sheetNum, lineNum);
}
/**
* 指定工作表和行数的内容
*/
public String[] readExcelLine(int sheetNum, int lineNum) {
if (sheetNum < 0 || lineNum < 0)
return null;
String[] strExcelLine = null;
try {
sheet = wb.getSheetAt(sheetNum);
row = sheet.getRow(lineNum);
int cellCount = row.getLastCellNum();
strExcelLine = new String[cellCount + 1];
for (int i = 0; i <= cellCount; i++) {
strExcelLine[i] = readStringExcelCell(lineNum, i);
}
} catch (Exception e) {
e.printStackTrace();
}
return strExcelLine;
}
/**
* 读取指定列的内容
*/
public String readStringExcelCell(int cellNum) {
return readStringExcelCell(this.rowNum, cellNum);
}
/**
* 指定行和列编号的内容
*/
public String readStringExcelCell(int rowNum, int cellNum) {
return readStringExcelCell(this.sheetNum, rowNum, cellNum);
}
/**
* 指定工作表、行、列下的内容
*/
public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
if (sheetNum < 0 || rowNum < 0)
return "";
String strExcelCell = "";
try {
sheet = wb.getSheetAt(sheetNum);
row = sheet.getRow(rowNum);
if (row.getCell((short) cellNum) != null) {
switch (row.getCell((short) cellNum).getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
strExcelCell = "FORMULA ";
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strExcelCell = String.valueOf(row.getCell((short) cellNum).getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
strExcelCell = row.getCell((short) cellNum).getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
strExcelCell = "";
break;
default:
strExcelCell = "";
break;
}
}
} catch (Exception e) {
e.printStackTrace();
}
return strExcelCell;
}
/**
* 主函数用于测试
*/
public static void main(String args[]) {
try {
File file = new File("/usr/test/users.xlsx");
ExcelUtil readExcel = new ExcelUtil(file);
try {
readExcel.open();
} catch (IOException e) {
e.printStackTrace();
}
readExcel.setSheetNum(0);
int count = readExcel.getRowCount();
for (int i = 0; i <= count; i++) {
String[] rows = readExcel.readExcelLine(i);
for (int j = 0; j < rows.length; j++) {
System.out.print(rows[j] + " ");
}
System.out.print("\n");
}
} catch (Exception e) {
System.out.println("对不起,读取出错...");
}
}
}
package statistics;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Writer;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Statistics {
public static void main(String[] args) {
try {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("每周数据");
sheet.setColumnWidth((short) 0, (short) 550);
// name of the zip entry holding sheet data, e.g.
// /xl/worksheets/sheet1.xml
String sheetRef = sheet.getPackagePart().getPartName().getName();
// save the template
FileOutputStream os = new FileOutputStream("template.xlsx");
wb.write(os);
os.close();
// Step 2. Generate XML file.
File tmp = File.createTempFile("sheet", ".xml");
Writer fw = new FileWriter(tmp);
Statistics.generate(fw);
fw.close();
// Step 3. Substitute the template entry with the generated data
FileOutputStream out = new FileOutputStream("big-grid.xlsx");
substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
private static void generate(Writer out)
throws Exception {
String[] headers = { "时间", "周数", "注册人数","购买人数","购买数量" };
Calendar calendar = Calendar.getInstance();
SpreadsheetWriter sw = new SpreadsheetWriter(out);
sw.beginSheet();
// insert header row
sw.insertRow(0);
for (int i = 0; i < headers.length; i++) {
sw.createCell(i, headers[i]);
}
sw.endRow();
//查询每周注册用户
String sql="SELECT date_format(u.ctime,'%Y年%m月%d日') time,DATE_FORMAT(u.ctime,'%u') as weekCount,count(DATE_FORMAT(u.ctime,'%u')) as addCount from cas_user u GROUP BY weekCount ORDER BY time";
List<WeekUser> weekUserList=DBhelp.executeQuery(sql, null, WeekUser.class);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy年MM月dd");
// write data rows
int addUserCount=0;
int payUserCount=0;
int oderCount=0;
for (int i = 1; i < weekUserList.size(); i++) {
sw.insertRow(i);
WeekUser wu=weekUserList.get(i-1);
sw.createCell(0,wu.getTime() );
sw.createCell(1, wu.getTime()+"第"+wu.getWeekcount()+"周");
sw.createCell(2, wu.getAddcount());
addUserCount+=wu.getAddcount();
String itemDate=sdf.format(sdf2.parse(wu.getTime()));
//查询每周购买人数
sql="SELECT count(1) as count FROM screens_order so WHERE WEEK (?) = WEEK (so.ctime) GROUP BY customer_id ";
List<WeekPayUser> weekPayUserList= DBhelp.executeQuery(sql, new Object[]{itemDate},WeekPayUser.class);
payUserCount+=weekPayUserList.size();
//查询每周订单数
sql="SELECT count(1) as count FROM screens_order so WHERE WEEK (?) = WEEK (so.ctime)";
String orderCount= DBhelp.executeQueryObject(sql, new Object[]{itemDate});
oderCount+=Integer.valueOf(orderCount);
sw.createCell(3, weekPayUserList.size());
sw.createCell(4, orderCount);
sw.endRow();
calendar.roll(Calendar.DAY_OF_YEAR, 1);
}
// 统计行
sw.insertRow(weekUserList.size());
sw.createCell(0, "统计");
sw.createCell(1, "");
sw.createCell(2, addUserCount);
sw.createCell(3, payUserCount);
sw.createCell(4, oderCount);
sw.endRow();
sw.endSheet();
}
private static void substitute(File zipfile, File tmpfile, String entry,
OutputStream out) throws IOException {
ZipFile zip = new ZipFile(zipfile);
ZipOutputStream zos = new ZipOutputStream(out);
@SuppressWarnings("unchecked")
Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
while (en.hasMoreElements()) {
ZipEntry ze = en.nextElement();
if (!ze.getName().equals(entry)) {
zos.putNextEntry(new ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
copyStream(is, zos);
is.close();
}
}
zos.putNextEntry(new ZipEntry(entry));
InputStream is = new FileInputStream(tmpfile);
copyStream(is, zos);
is.close();
zos.close();
}
private static void copyStream(InputStream in, OutputStream out)
throws IOException {
byte[] chunk = new byte[1024];
int count;
while ((count = in.read(chunk)) >= 0) {
out.write(chunk, 0, count);
}
}
}