資訊專欄INFORMATION COLUMN
摘要：一般訂單打印紙高度毫米字母或半角字符判斷是否為全角全角符號(hào)及中文連續(xù)出現(xiàn)，認(rèn)為是單詞組成部分的字符和間單頁(yè)的票據(jù)右端聯(lián)頁(yè)寬占用的寬度回退一行每減一次少一行實(shí)際高度的距離補(bǔ)足行高拉高的行內(nèi)容要調(diào)整使用靠上對(duì)齊補(bǔ)充
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;
/**
* Created by chengxiaoqi1 on 15/9/24.
*/
public class ExcelUtils {
static Logger log = LoggerFactory.getLogger(ExcelUtils.class);
public final static int PAGE_HEIGHT_DEFAULT = 140; //一般訂單打印紙高度 140毫米
// static Pattern PAT_ASC = Pattern.compile("^[A-Za-z0-9 -./+]+$"); //字母或半角字符
// static Pattern PAT_FULL_1 = Pattern.compile("^[u4e00-u9fa5]+$"); //判斷是否為全角
// static Pattern PAT_FULL_2 = Pattern.compile("[^x00-xff]$"); //全角符號(hào)及中文
static Pattern PAT_PARTNER = Pattern.compile("^[A-Za-z0-9]$"); //連續(xù)出現(xiàn)，excel認(rèn)為是單詞組成部分的字符
/**
* startRow和endRow間單頁(yè)的票據(jù)右端聯(lián)
*
* @param outputSheet
* @param startRow
* @param endRow
* @param PAGE_HEIGHT
* @param MAX_COL
*/
public static void noteOnRightSideOne(HSSFSheet outputSheet, int startRow, int endRow, int PAGE_HEIGHT, int MAX_COL, String ticketInfo, int needHeight) {
int prefixHeight = (PAGE_HEIGHT - needHeight) / 2; //頁(yè)寬-占用的寬度
startRow--; //回退一行
while (prefixHeight > 0 && startRow < endRow) {
startRow++;
prefixHeight -= outputSheet.getRow(startRow).getHeight();//每減一次少一行實(shí)際高度的距離
}
int toRow = startRow;
while (needHeight > 0 && toRow < endRow) {
toRow++;
needHeight -= outputSheet.getRow(toRow).getHeight();
}
//補(bǔ)足行高
if (needHeight > 0) {
short beHeight = (short) (outputSheet.getRow(endRow).getHeight() + needHeight);
HSSFRow row = outputSheet.getRow(endRow);
row.setHeight(beHeight);
//拉高的行內(nèi)容要調(diào)整使用靠上對(duì)齊
for (int i = 0; i < row.getLastCellNum(); i++) {
row.getCell(i).getCellStyle().setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
}
}
//補(bǔ)充不存在的單元格
for (int i = startRow; i <= toRow; i++) {
HSSFRow aRow = outputSheet.getRow(i);
if (aRow == null) {
aRow = outputSheet.createRow(i);
}
HSSFCell aCell = aRow.getCell(MAX_COL);
if (aCell == null) {
aRow.createCell(MAX_COL);
}
}
HSSFCell aCell=outputSheet.getRow(startRow).getCell(MAX_COL);
aCell.setCellValue(ticketInfo);
HSSFCellStyle style = outputSheet.getWorkbook().createCellStyle();
style.cloneStyleFrom(aCell.getCellStyle());
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setBorderLeft(HSSFCellStyle.BORDER_HAIR);
style.setWrapText(true);
HSSFFont font = outputSheet.getWorkbook().createFont(); //style.getFont(outputSheet.getWorkbook());
font.setFontHeightInPoints((short) 8);
font.setFontName("宋體");
style.setFont(font);
aCell.setCellStyle(style);
style = outputSheet.getWorkbook().createCellStyle();
style.cloneStyleFrom(aCell.getCellStyle());
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
for (int y = startRow; y <= toRow; y++) {
outputSheet.getRow(y).getCell(MAX_COL).setCellStyle(style);
}
CellRangeAddress note = new CellRangeAddress(startRow, toRow, MAX_COL, MAX_COL);
outputSheet.addMergedRegion(note);
outputSheet.setColumnWidth(MAX_COL, 600);
}
/**
* minRow和maxRow間多頁(yè)的票據(jù)右端聯(lián)
*
* @param outputSheet
* @param minRow
* @param maxRow
* @param PAGE_HEIGHT
* @param MAX_COL
*/
public static void noteOnRightSide(int minRow, int maxRow, int PAGE_HEIGHT, int MAX_COL, HSSFSheet outputSheet,String ticketInfo, int needHeight) {
int[] breaks = outputSheet.getRowBreaks();
boolean minAdded = false;
boolean maxAdded = false;
List breakLists = new ArrayList<>();
if (breaks != null && breaks.length >= 0) {
for (int i = 0; i < breaks.length; i++) {
if (breaks[i] >= minRow) {
if (!minAdded) {
breakLists.add(minRow - 1);
minAdded = true;
}
breakLists.add(breaks[i]);
} else if (breaks[i] >= maxRow) {
if (!maxAdded) {
breakLists.add(maxRow);
maxAdded = true;
break;
}
}
}
}
if (!minAdded) {
breakLists.add(minRow - 1);
}
if (!maxAdded) {
breakLists.add(maxRow);
}
int startBreakIndex = 0;
while (startBreakIndex < breakLists.size() - 1) {
noteOnRightSideOne(outputSheet, breakLists.get(startBreakIndex) + 1, breakLists.get(startBreakIndex + 1), PAGE_HEIGHT, MAX_COL,ticketInfo, needHeight);
startBreakIndex++;
}
}
/**
* 獲取模板中的行數(shù)
*
* @param sourceSheet
* @return
*/
public static int findRowCount(HSSFSheet sourceSheet) {
int count = 0;
int maxRow=sourceSheet.getLastRowNum();
while (count < maxRow) {
if ("END".equals(sourceSheet.getRow(count).getCell(0).getStringCellValue())) {
count--;
break;
}
count++;
}
if(count==maxRow){
count--;
}
return count;
}
/**
* 獲取模板中最后頁(yè)應(yīng)包含的最少的行數(shù)
* @param sourceSheet
* @param maxRows 模板的最大行數(shù)
* @return 沒找到，返回 -1
*/
public static int findLastTableMinRow(HSSFSheet sourceSheet, int maxRows) {
int count = 0;
int maxRow=sourceSheet.getLastRowNum();
while (count < maxRow) {
HSSFRow aRow=sourceSheet.getRow(count);
if (aRow != null) {
HSSFCell aCell=aRow.getCell(0);
if(aCell!=null && "LST".equals(aCell.getStringCellValue())){
aCell.setCellValue("");
count--;
break;
}
}
count++;
}
if(count==maxRow){
count=1;
}else{
count=maxRows-count;
}
return count;
}
/**
* 獲取模板中標(biāo)識(shí)聯(lián)的列位置
* @param sourceSheet
* @param row
* @return 沒找到，返回-1
*/
public static int findMarkCol(HSSFSheet sourceSheet,int row) {
HSSFRow aRow=sourceSheet.getRow(row);
if(aRow==null) return -1;
int maxCol=aRow.getLastCellNum();
int count = 0;
while (count < maxCol) {
HSSFCell aCell=aRow.getCell(count);
if(aCell!=null && "MARK_COL".equals(aCell.getStringCellValue())){
break;
}
count++;
}
if(maxCol==count){
count=-1;
}
return count;
}
/**
* 復(fù)制行內(nèi)內(nèi)容
*
* @param wb
* @param pSourceSheetName
* @param pTargetSheetName
* @param pStartRow
* @param pEndRow
* @param pPosition
*/
public static void copyRows(HSSFWorkbook wb, String pSourceSheetName,
String pTargetSheetName, int pStartRow, int pEndRow, int pPosition) {
HSSFRow sourceRow = null;
HSSFRow targetRow = null;
HSSFCell sourceCell = null;
HSSFCell targetCell = null;
HSSFSheet sourceSheet = null;
HSSFSheet targetSheet = null;
Region region = null;
int cType;
int i;
int j;
int targetRowFrom;
int targetRowTo;
if ((pStartRow == -1) || (pEndRow == -1)) {
return;
}
sourceSheet = wb.getSheet(pSourceSheetName);
targetSheet = wb.getSheet(pTargetSheetName);
// 拷貝合并的單元格
for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
region = sourceSheet.getMergedRegionAt(i);
if ((region.getRowFrom() >= pStartRow)
&& (region.getRowTo() <= pEndRow)) {
targetRowFrom = region.getRowFrom() - pStartRow + pPosition;
targetRowTo = region.getRowTo() - pStartRow + pPosition;
region.setRowFrom(targetRowFrom);
region.setRowTo(targetRowTo);
targetSheet.addMergedRegion(region);
}
}
// 設(shè)置列寬
if (pPosition == 0) {
for (j = 0; j < 100; j++) {
//log.debug("==================>>" + j + sourceSheet.getColumnWidth(j));
targetSheet.setColumnHidden(j, false);
targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
}
}
// 拷貝行并填充數(shù)據(jù)
for (i = pStartRow; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow == null) {
continue;
}
targetRow = targetSheet.createRow(i - pStartRow + pPosition);
targetRow.setHeight(sourceRow.getHeight());
for (j = sourceRow.getFirstCellNum(); j < sourceRow
.getPhysicalNumberOfCells(); j++) {
sourceCell = sourceRow.getCell(j);
if (sourceCell == null) {
continue;
}
targetCell = targetRow.createCell(j);
targetCell.setCellStyle(sourceCell.getCellStyle());
cType = sourceCell.getCellType();
targetCell.setCellType(cType);
log.debug("width:----->"+i+","+targetSheet.getColumnWidth(2)+"");
switch (cType) {
case HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
targetCell
.setCellErrorValue(sourceCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
// parseFormula這個(gè)函數(shù)的用途在后面說明
targetCell.setCellFormula(parseFormula(sourceCell
.getCellFormula()));
break;
case HSSFCell.CELL_TYPE_NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
targetCell
.setCellValue(sourceCell.getRichStringCellValue());
break;
}
}
}
}
private static String parseFormula(String pPOIFormula) {
final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
StringBuffer result = null;
int index;
result = new StringBuffer();
index = pPOIFormula.indexOf(cstReplaceString);
if (index >= 0) {
result.append(pPOIFormula.substring(0, index));
result.append(pPOIFormula.substring(index
+ cstReplaceString.length()));
} else {
result.append(pPOIFormula);
}
return result.toString();
}
/**
* 對(duì)sheet做分頁(yè)處理，右邊加上聯(lián)標(biāo)識(shí)
* @param sheet 要處理的sheet對(duì)象
* @param firstRow sheet中哪一行開始算本頁(yè)第一行
* @param PAGE_HEIGHT 頁(yè)高，一般以標(biāo)準(zhǔn)每行280單位計(jì)數(shù)
* @param lastPageMinRow 最后頁(yè)應(yīng)該有的最少行數(shù)
*/
public static void commonPageDeal(HSSFSheet sheet, int firstRow, final int PAGE_HEIGHT, int lastPageMinRow, int markCol, String ticketInfo, int needHeight){
int aPageHeight=0;
int lastPageBreak=firstRow-1;
int cRowCount=sheet.getLastRowNum();
for(int i=firstRow;i<=cRowCount;i++){
short rowHeight=sheet.getRow(i).getHeight();
aPageHeight += rowHeight;
if(aPageHeight > PAGE_HEIGHT){
lastPageBreak = i-1;
sheet.setRowBreak(lastPageBreak);
aPageHeight = rowHeight;
}
}
//每單最后頁(yè)內(nèi)容必須有指定行數(shù)的內(nèi)容
if(lastPageMinRow>1 && cRowCount-firstRow>lastPageMinRow && cRowCount-lastPageBreak0) {
ExcelUtils.noteOnRightSide(firstRow, cRowCount, PAGE_HEIGHT, markCol, sheet, ticketInfo, needHeight);
}
}
public static void styleMoneyCell(Cell moneyCell){
if(moneyCell==null) return;
CellStyle style=moneyCell.getCellStyle();
style.setDataFormat((short)731);
}
public static void styleGoodsNameCell(Cell goodsNameCell){
if(goodsNameCell==null) return;
CellStyle style=goodsNameCell.getCellStyle();
style.setShrinkToFit(true);
}
public static Integer getExcelCellAutoHeight(String str, float fontCountInline) {
float contentLength = 0.00f;
for (int i = 0; i < str.length(); i++) {
float ff = getregex(str.substring(i, i + 1));
contentLength = contentLength + ff;
}
return ((int) (contentLength / fontCountInline) + 1);//計(jì)算
}
public static float getregex(String charStr) {
return charStr.getBytes().length==1?0.5f:1.0f;
// // 判斷是否為字母或字符
// if (PAT_ASC.matcher(charStr).matches()) {
// return 0.5f;
// }
// 判斷是否為全角
// if (PAT_FULL_1.matcher(charStr).matches()) {
// return 1.00f;
// }
// //全角符號(hào) 及中文
// if (PAT_FULL_2.matcher(charStr).matches()) {
// return 1.00f;
// }
// return 0.5f;
}
public static void insertRow(HSSFSheet sheet, int startRow, int rows) {
sheet.shiftRows(startRow, sheet.getLastRowNum(), rows, true, false);
HSSFRow sourceRow = sheet.getRow(startRow + rows); //模板行
HSSFRow targetRow = null;
HSSFCell targetCell = null;
HSSFCell sourceCell = null;
for (int i = 0; i < rows; i++) {
targetRow = sheet.createRow(startRow + i);
targetRow.setHeight(sourceRow.getHeight());
for (int m = sourceRow.getFirstCellNum(); m < sourceRow.getPhysicalNumberOfCells(); m++) {
targetCell = targetRow.createCell(m);
sourceCell = sourceRow.getCell(m);
targetCell.setCellStyle(sourceCell.getCellStyle());
targetCell.setCellType(sourceCell.getCellType());
}
}
}
public static void getFile(String fileName,HSSFWorkbook wbModule,HttpServletResponse response,Logger logger){
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
String time = sdf.format(date);
String filename = time + fileName;
try {
//頁(yè)面 download
// 清空response
response.reset();
// 設(shè)置response的Header
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
ServletOutputStream sos = response.getOutputStream();
wbModule.write(sos);
sos.flush();
sos.close();
} catch (Exception e) {
logger.error("",e);
}
}
/**
*將text在該換行處插入空格，使能自動(dòng)換行
* @param text 需要處理的文本
* @param lineLen 一行顯示的最多中文字符數(shù)
* @return 返回List 第一個(gè)元素：在換行處加上空格的商品名稱 第二個(gè)元素:包含的行數(shù)
*/
public static List wrapText(String text,float lineLen){
if(text==null || "".equals(text)){
text=" ";
}
StringBuffer buffer = new StringBuffer();
int rowCount = 0;
float count = 0;
boolean lastCharPart = false;
boolean curCharPart;
int lastPartIndex =0;
for (int i=0;i=lineLen) { //當(dāng)前字符超過一行可顯示的字符
/**
* 當(dāng)前字符號(hào)上一個(gè)字符同時(shí)滿足條件
* 添加空格處理
*/
if(curCharPart && lastCharPart){
int cj = 0; //需要補(bǔ)足的空格數(shù)
for(int j=i;j= lineLen){
throw new RuntimeException("超長(zhǎng)單詞，暫不支持");
}
for(int jj=0;jj 0){
rowCount++;
}
List
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址：http://m.specialneedsforspecialkids.com/yun/64711.html
摘要：最近在做使用進(jìn)行大數(shù)據(jù)量導(dǎo)出，現(xiàn)在把其整理成工具類供大家參考。版本增加了前綴為相關(guān)的類，主要用于大數(shù)據(jù)量的寫入與讀取。 最近在做使用POI進(jìn)行大數(shù)據(jù)量導(dǎo)出，現(xiàn)在把其整理成工具類供大家參考。Apache POI 3.8版本增加了前綴為SXSSF相關(guān)的類，主要用于大數(shù)據(jù)量的寫入與讀取。關(guān)于ApachePOI導(dǎo)出Excel基本的使用我這里就不詳解了，具體參考： Apache POI官方網(wǎng)站...
摘要：我想能不能像配置文件一樣可配置的導(dǎo)入導(dǎo)出，那樣使用起來就方便許多。配置和使用下面是員工信息模型。支持多種映射，使用英文逗號(hào)進(jìn)行分割。導(dǎo)入時(shí)它會(huì)以分割前面的作為導(dǎo)入時(shí)使用的值，后面的作為導(dǎo)出時(shí)使用的值后面值進(jìn)行逆推導(dǎo)出時(shí)同理。 1.前言 在工作時(shí)，遇到過這樣的需求，需要靈活的對(duì)工單進(jìn)行導(dǎo)入或?qū)С觯郧白约阂沧鲞^，但使用不靈活繁瑣。我想能不能像配置文件一樣可配置的導(dǎo)入導(dǎo)出，那樣使用起來就方...
摘要：消費(fèi)之后，多線程處理文件導(dǎo)出，生成文件后上傳到等文件服務(wù)器。前端直接查詢并且展現(xiàn)對(duì)應(yīng)的任務(wù)執(zhí)行列表，去等文件服務(wù)器下載文件即可。這客戶體驗(yàn)不友好，而且網(wǎng)絡(luò)傳輸，系統(tǒng)占用多種問題。拓展閱讀導(dǎo)出最佳實(shí)踐框架 產(chǎn)品需求 產(chǎn)品經(jīng)理需要導(dǎo)出一個(gè)頁(yè)面的所有的信息到 EXCEL 文件。 需求分析 對(duì)于 excel 導(dǎo)出，是一個(gè)很常見的需求。 最常見的解決方案就是使用 poi 直接同步導(dǎo)出一個(gè) exc...
摘要：通過這個(gè)字段設(shè)置表名的編碼格式，從而兼容不同瀏覽器。獲取對(duì)象放入中字段名五總結(jié)設(shè)置樣式的話可根據(jù)需求進(jìn)行調(diào)整，這個(gè)過程比較費(fèi)時(shí)，導(dǎo)出條數(shù)據(jù)，無樣式在左右，有樣式需要分鐘。 Springboot+poi導(dǎo)出Excel 一、引入jar包 注意需要引入3.8版本,POI3.8提供了SXSSFWorkbook類,來處理大數(shù)據(jù)內(nèi)存溢出的問題.可設(shè)置默認(rèn)內(nèi)存大小,多出的部分可存入硬盤中,不會(huì)內(nèi)存溢...
閱讀 970·2022-06-21 15:13
閱讀 1855·2021-10-20 13:48
閱讀 1039·2021-09-22 15:47
閱讀 1373·2019-08-30 15:55
閱讀 3128·2019-08-30 15:53
閱讀 526·2019-08-29 12:33
閱讀 721·2019-08-28 18:15
閱讀 3467·2019-08-26 13:58
