poi

package testpoi.service.impl;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import testpoi.entity.bo.OrderInfo;
import testpoi.mapper.ExportMapper;
import testpoi.service.ExportService;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Array;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Service("service")
public class ExportServiceImpl implements ExportService {
    @Autowired
    private ExportMapper exportDao;

    public String createFileName() {
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String pFileName = sdf.format(date);
        String fileName = pFileName+".xls";
        return fileName;
    }


    @Override
    public void exportExcel() throws IOException {

          String[] array = new String[]{"订单号","预定状态","预约机构","预约时间","体检套餐","预约姓名","身份证号","电话号码"};
       //创建一个工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建一个工作表
        HSSFSheet sheet = wb.createSheet("预约用户信息");

        //创建表格标题
       HSSFRow row = sheet.createRow(0);
       HSSFCell cell = row.createCell(0);
       cell.setCellValue("体检预约信息");
       /*********************标题单元格合并*******************/
        //合并单元格的位置
        CellRangeAddress cra = new CellRangeAddress(0,2,0,7);
        //执行合并单元格
        sheet.addMergedRegion(cra);

        /************************设置标题样式开始********************/
        HSSFCellStyle globalStyle = wb.createCellStyle();
        HSSFCellStyle titleStyle = wb.createCellStyle();
        HSSFCellStyle headStyle = wb.createCellStyle();
        HSSFCellStyle contentStyle = wb.createCellStyle();
        //style.setFillForegroundColor((short) 3);
        //style.setFillBackgroundColor((short) 3);

        /**************字体格式部分*******************/
        HSSFFont font  =wb.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setColor((short)2);
        font.setFontHeightInPoints((short)16);

        //设置文字居中
        titleStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        titleStyle.setFont(font);
        cell.setCellStyle(titleStyle);

        //给合并单元格添加边框
        RegionUtil.setBorderBottom(BorderStyle.THICK,cra,sheet);
        RegionUtil.setBorderLeft(BorderStyle.THICK,cra,sheet);
        RegionUtil.setBorderRight(BorderStyle.THICK,cra,sheet);
        RegionUtil.setBorderTop(BorderStyle.THICK,cra,sheet);


        //颜色1白，2红，3绿，4蓝，5黄，6粉红，7青蓝，8黑,往后依次循环

        RegionUtil.setBottomBorderColor(8,cra,sheet);
        RegionUtil.setLeftBorderColor(8,cra,sheet);
        RegionUtil.setRightBorderColor(8,cra,sheet);
        RegionUtil.setTopBorderColor(8,cra,sheet);

        /************************设置标题样式结束********************/

        //表头部分
           //创建表头行
        HSSFRow headRow = sheet.createRow(3);

          for (int i=0;i<array.length;i++) {
              HSSFCell  headcell_i= headRow.createCell(i);
              headcell_i.setCellValue(array[i]);
              System.out.println(array[i]);
        }

        String fileName = createFileName();
        FileOutputStream output = new FileOutputStream("d:\\"+fileName);


        wb.write(output);
        output.flush();
        output.close();
    }

    @Override
    public List<OrderInfo> queryAll() {
        List<OrderInfo> list =  exportDao.queryAll();


        return list;
    }
}

package testpoi;import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.BorderStyle;

import org.apache.poi.ss.usermodel.FillPatternType;

import org.apache.poi.ss.usermodel.HorizontalAlignment;

import org.apache.poi.ss.usermodel.VerticalAlignment;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.ss.util.RegionUtil;import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.test.context.junit4.SpringRunner;

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;import java.text.SimpleDateFormat;import java.util.Date;

@RunWith(SpringRunner.class)

@SpringBootTest

public class TestPoi2 { 

  public String createFileName() { 

 Date date = new Date(); 

SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss"); 

String pFileName = sdf.format(date); 

String fileName = pFileName+".xls"; return fileName; 

} 

  @Test

public void teststyle() throws IOException { 

 HSSFWorkbook wb = new HSSFWorkbook(); 

HSSFSheet sheet = wb.createSheet("测试样式"); 

HSSFRow row = sheet.createRow(0); 

HSSFCell cell1= row.createCell(0); 

// HSSFCell cell2= row.createCell(1); 

cell1.setCellValue("体检用户预约信息");

/**********************格式部分**************************/ 

HSSFCellStyle cellStyle = wb.createCellStyle(); 

/***************设置填充样式*******************/ 

cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setFillBackgroundColor(HSSFColor.GREEN.index); 

/****************设置居中样式*********************/ 

cellStyle.setAlignment(HorizontalAlignment.CENTER);

//水平居中 

 //cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);//跨列居中 

 // cellStyle.setAlignment(HorizontalAlignment.FILL);//重复输出内容 // cellStyle.setAlignment(HorizontalAlignment.LEFT);//靠左 //cellStyle.setAlignment(HorizontalAlignment.RIGHT);//靠右 //cellStyle.setAlignment(HorizontalAlignment.DISTRIBUTED);//均匀展开，两侧无边距，两端对齐 //cellStyle.setAlignment(HorizontalAlignment.JUSTIFY);//基本无修饰 //cellStyle.setAlignment(HorizontalAlignment.GENERAL);//一般的样式 

/****************垂直居中样式******************/ 

cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 //cellStyle.setVerticalAlignment(VerticalAlignment.DISTRIBUTED);//相当于垂直居中 //cellStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY);//居顶部 //cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);//居底部 //cellStyle.setVerticalAlignment(VerticalAlignment.TOP);//居顶部

/**************字体格式部分*******************/ 

HSSFFont font =wb.createFont(); font.setBold(true); 

font.setFontName("宋体"); 

font.setColor((short)2); 

font.setFontHeightInPoints((short)16); 

cellStyle.setFont(font); 

cell1.setCellStyle(cellStyle); 

//合并单元格的位置 

CellRangeAddress cra = new CellRangeAddress(0,2,0,12); 

//执行合并单元格 sheet.addMergedRegion(cra); 

//BorderStyle.THICK 粗边线 //BorderStyle.THIN 细边线

//BorderStyle.MEDIUM 中等边线 

 //给合并单元格添加边框 

 //RegionUtil.setBorderBottom(1,cra,sheet); 

 

//RegionUtil.setBorderBottom(BorderStyle.THICK,cra,sheet); //RegionUtil.setBorderLeft(BorderStyle.DASH_DOT,cra,sheet);//断一横虚线 //RegionUtil.setBorderRight(BorderStyle.DASH_DOT_DOT,cra,sheet);//断两横虚线 //RegionUtil.setBorderTop(BorderStyle.HAIR,cra,sheet);//圆点虚线 RegionUtil.setBorderBottom(BorderStyle.MEDIUM,cra,sheet); RegionUtil.setBorderLeft(BorderStyle.MEDIUM,cra,sheet);//断一横虚线 RegionUtil.setBorderRight(BorderStyle.MEDIUM,cra,sheet);//断两横虚线 RegionUtil.setBorderTop(BorderStyle.MEDIUM,cra,sheet);

//圆点虚线 //颜色1白，2红，3绿，4蓝，5黄，6粉红，7青蓝，8黑,往后依次循环 RegionUtil.setBottomBorderColor(8,cra,sheet);

RegionUtil.setLeftBorderColor(8,cra,sheet);

RegionUtil.setRightBorderColor(8,cra,sheet); 

RegionUtil.setTopBorderColor(8,cra,sheet); 

//HSSFRow row = sheet.createRow(0); 

 //HSSFCell cell1= row.createCell(0); 

 // HSSFCell cell2= row.createCell(1); 

 //cell1.setCellValue("测试表标题"); 

 // cell2.setCellValue("测试添加数据"); 

String fileName = createFileName(); FileOutputStream output = new FileOutputStream("d:\\"+fileName); wb.write(output); output.flush(); output.close(); }}