LoginSignup
3
2

More than 3 years have passed since last update.

@dmkd3006

Java sample code 04

Last updated at Posted at 2020-03-21

SAMPLE GALLERYに掲載されたソースを疎通してimport文も付けました。

4.java poi Excel

  • Excelファイルを作成
  • シート名の設定
  • シートの色を指定
  • セルに値を入力
  • 文字の大きさを指定
  • フォントを指定
  • 文字を太字にする
  • 文字をイタリックにする
  • 文字に下線を引く
  • 文字に取消線を引く
  • セルの背景色を指定
  • 値の整列（水平方向）
  • 値の整列（垂直方向）
  • セルの文字の色を指定
  • セルの枠線を指定
  • セルの枠線の色を指定
  • セルの折り返しを指定
  • セルにハイパーリンクを設定
  • セルの結合

4.java poi Excel

4-1.Excelファイルを作成

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            workbook.createSheet();
            workbook.write(os);//ここでエクセルファイルを作成しています。
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-2.シート名の設定

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            workbook.createSheet("シート1");//ここでシート名を指定しています。
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-3.シートの色を指定

import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import static java.awt.Color.BLUE;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            sheet.setTabColor(new XSSFColor(BLUE));
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-4.セルに値を入力

import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("Cell Value");//ここでセルに値を設定しています。
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-5.文字の大きさを指定

import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 20);//ここで文字の大きさを指定しています。
            style.setFont(font);
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-6.フォントを指定

import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont font = workbook.createFont();
            font.setFontName("Times New Roman");//ここでフォントを指定しています。
            style.setFont(font);
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-7.文字を太字にする

import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont font = workbook.createFont();
            font.setBold(true);//ここで太字に設定しています。
            style.setFont(font);
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-8.文字をイタリックにする

import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont font = workbook.createFont();
            font.setItalic(true);//ここでイタリックに指定しています。
            style.setFont(font);
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-9.文字に下線を引く

import org.apache.poi.ss.usermodel.FontUnderline;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont font = workbook.createFont();
            font.setUnderline(FontUnderline.SINGLE);//ここで下線を引いています。
            style.setFont(font);
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-10.文字に取消線を引く

import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont font = workbook.createFont();
            font.setStrikeout(true);//ここで取消線を引いています。
            style.setFont(font);
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-11.セルの背景色を指定

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.*;

import java.awt.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            //style.setFillPattern(CellStyle.BRICKS);
            XSSFColor color = new XSSFColor(Color.RED);
            style.setFillForegroundColor(color);//ここで背景色を指定しています。
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-12.値の整列（水平方向）

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

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.RIGHT);//ここで値の整列をしています（水平方向）
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-13.値の整列（垂直方向）

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

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            style.setVerticalAlignment(VerticalAlignment.CENTER);//ここで値の整列をしています（垂直方向）
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-14.セルの文字の色を指定

import org.apache.poi.xssf.usermodel.*;

import java.awt.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont font = workbook.createFont();
            XSSFColor color = new XSSFColor(Color.RED);
            font.setColor(color);//ここで文字の色を設定しています。
            style.setFont(font);
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-15.セルの枠線を指定

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

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(1);
            XSSFCell cell = row.createCell(1);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            BorderStyle border = BorderStyle.THIN;//ここで枠線の種類を指定しています。
            style.setBorderBottom(border);//下の枠線を指定しています。
            style.setBorderLeft(border);//左の枠線を指定しています。
            style.setBorderRight(border);//右の枠線を指定しています。
            style.setBorderTop(border);//上の枠線を指定しています。
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-16.セルの枠線の色を指定

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;

import java.awt.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(1);
            XSSFCell cell = row.createCell(1);
            cell.setCellValue("Cell Value");
            XSSFCellStyle style = workbook.createCellStyle();
            BorderStyle border = BorderStyle.THIN;
            XSSFColor color = new XSSFColor(Color.RED);
            style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);//ここで枠線の色を指定しています。
            style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
            style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
            style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
            style.setBorderBottom(border);
            style.setBorderLeft(border);
            style.setBorderRight(border);
            style.setBorderTop(border);
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-17.セルの折り返しを指定

import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(1);
            XSSFCell cell = row.createCell(1);
            cell.setCellValue("Hello World.");
            XSSFCellStyle style = workbook.createCellStyle();
            style.setWrapText(true);//ここで折り返しを指定しています。
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-18.セルにハイパーリンクを設定

import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            XSSFRow row = sheet.createRow(1);
            XSSFCell cell = row.createCell(1);
            cell.setCellValue("Sample Code Library");
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFHyperlink hyperlink = workbook.getCreationHelper().createHyperlink(Hyperlink.LINK_URL);
            hyperlink.setAddress("http://blueplace.sakura.ne.jp/");
            cell.setHyperlink(hyperlink);//ここでハイパーリンクを指定しています。
            cell.setCellStyle(style);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}

4-19.セルの結合

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class SamplePoi {

    public static void main(String[] args) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream("TestExcelFile.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("シート1");
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 2, 2));//ここで結合するセルを設定しています。（firstRow, lastRow, firstCol, lastCol）
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                os.close();
            }
        }
    }

}
3
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
2