How to put data into Excel sheet by using Apache P...

Cat · ‎Mar 16, 2016

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

//Apache POI

package ext;

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.lang.reflect.Field;

import java.lang.reflect.InvocationTargetException;

import java.lang.reflect.Method;

import java.util.*;

import org.apache.commons.io.FileUtils;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.openxml4j.opc.OPCPackage;

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

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

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

import org.apache.poi.xssf.streaming.SXSSFSheet;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

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

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

import org.joda.time.DateTime;

import org.joda.time.format.DateTimeFormat;

import wt.epm.EPMDocument;

import wt.epm.EPMDocumentMaster;

import wt.fc.PersistenceHelper;

import wt.fc.QueryResult;

import wt.pds.StatementSpec;

import wt.query.QuerySpec;

import wt.util.WTException;

import wt.doc.*;

public class Test2 {

static int count=2;

public static void main(String[] args) throws WTException {

QuerySpec qs = new QuerySpec(wt.doc.WTDocument.class);

QueryResult qr = PersistenceHelper.manager.find((StatementSpec) qs);

System.out.println("Number of WTDocument Masters: " + qr.size() + "\n\n");

WTDocument master = null;

XSSFWorkbook workbook = new XSSFWorkbook();

XSSFSheet sheet = workbook.createSheet("WTDocument Sheet");

Map<String, Object[]> data = new TreeMap<String, Object[]>();

data.put("1", new Object[] {"DOC NAME", "DOC NUMBER"});

while (qr.hasMoreElements()) {

master = (WTDocument) qr.nextElement();

data.put(String.valueOf(count), new Object[] {master.getName(), master.getNumber()});

count++;

}

//Iterate over data and write to sheet

Set<String> keyset = data.keySet();

int rownum = 0;

for (String key : keyset)

{

Row row = sheet.createRow(rownum++);

Object [] objArr = data.get(key);

int cellnum = 0;

for (Object obj : objArr)

{

Cell cell = row.createCell(cellnum++);

if(obj instanceof String)

cell.setCellValue((String)obj);

else if(obj instanceof Integer)

cell.setCellValue((Integer)obj);

}

try

{

//Write the workbook in file system

FileOutputStream out = new FileOutputStream(new File("My_WTDocument.xlsx"));

workbook.write(out);

out.close();

System.out.println("it is written successfully on disk.");

}

catch (Exception e)

{

e.printStackTrace();

}

Colored by Color Scripter

cs

How to put data into Excel sheet by using Apache POI

How to put data into Excel sheet by using Apache POI