4

I am creating a script for export document as excel.

How to have cell value like "Name: Mark DOB: 11-11-2014" by merging few cells?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Krunal Shah
  • 2,083
  • 12
  • 27
  • Can you add more info and some code so that its easier to solve your problem. I have suggested another library below which i found very easy to use. – vatsal Nov 12 '14 at 11:42

3 Answers3

8

What you need to do is create a RichTextString for your cell. That's the way of applying different formatting / styles to different parts of the same cell for display in Excel

You'll want to review the POI "Working With Rich Text" example for more on how to use it, but broadly it'll be something like

    Cell cell = row.createCell(1);
    RichTextString rt = new XSSFRichTextString("The quick brown fox");

    Font font1 = wb.createFont();
    font1.setBoldWeight(Font.BOLDWEIGHT_BOLD);
    rt.applyFont(0, 10, font1);

    Font font2 = wb.createFont();
    font2.setItalic(true);
    font2.setUnderline(XSSFFont.U_DOUBLE);
    rt.applyFont(10, 19, font2);

    Font font3 = wb.createFont();
    font3.setBoldWeight(Font.BOLDWEIGHT_NORMAL);
    rt.append(" Jumped over the lazy dog", font3);

    cell.setCellValue(rt);

That should give you a cell with a mixture of bold, italic+underline and normal

Gagravarr
  • 47,320
  • 10
  • 111
  • 156
  • what is that 0,10 and 10,19 ? – Krunal Shah Nov 12 '14 at 14:13
  • Look at the linked javadocs, those are the offsets within the cell's text you want the styling to apply to – Gagravarr Nov 12 '14 at 15:59
  • Are you sure Font.setBold will work? Should it not be `XSSFFont font1 = (XSSFFont)wb.createFont(); font1.setBold(true);` or `font1.setBoldweight(Font.BOLDWEIGHT_BOLD);` ? – Axel Richter Nov 12 '14 at 16:10
  • Depends if you're using HSSF or XSSF. I believe the above ought to work for both, but I haven't actually tried it... – Gagravarr Nov 12 '14 at 18:42
  • Hm, no, the interface Font has no method setBold. https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Font.html – Axel Richter Nov 12 '14 at 19:04
  • Hmm, how strange! Corrected – Gagravarr Nov 12 '14 at 19:06
  • 1
    Though if you look at the javadocs, you will see [Font.setBold(boolean)](https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Font.html#setBold%28boolean%29) showing up now... – Gagravarr Nov 12 '14 at 19:14
  • Hm, not for me. There is only https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFFont.html#setBold%28boolean%29 in class XSSFFont, not in interface Font. And it is setBoldweight and not setBoldWeight. – Axel Richter Nov 12 '14 at 19:19
  • Refresh your page, you might be surprised ;-) – Gagravarr Nov 13 '14 at 05:01
  • Its not working, we have to think alternative option for "Font". – Krunal Shah Nov 13 '14 at 05:32
  • @Gagravarr: *g* Yes the documentation has changed now. But nevertheless I got a compiler error for Font.setBold with my POI version. Will try this evening whether the apache guys have updated my local jar files too ;-). – Axel Richter Nov 13 '14 at 05:50
7

I have created a short complete example for this. 

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

import java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;


class RichTextTest {

 public static void main(String[] args) {
  Workbook wb = new XSSFWorkbook();
  Sheet sheet = wb.createSheet("Sheet1");

  Row row = sheet.createRow(0);

  Cell cell = row.createCell(0);

  RichTextString richString = new XSSFRichTextString( "Name: Mark DOB: 11-11-2014" );
                                                     //^0  ^4     ^11^14
  Font fontBold = wb.createFont();
  //fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD);
  fontBold.setBold(true);

  richString.applyFont( 0, 4, fontBold );
  richString.applyFont( 11, 14, fontBold );
  cell.setCellValue(richString);


  try {
   FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
   wb.write(fileOut);
   fileOut.flush();
   fileOut.close();
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }

 }
}

For further reading see documentation.

How to create workbook, sheet and cells: http://poi.apache.org/spreadsheet/quick-guide.html#CreateCells

How to use Richtext: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFRichTextString.html

The Font interface: https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Font.html

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
0

Have you tried using JXLS ?

Using xls templates you can read and write data from Excel. Its very simple to use.

vatsal
  • 3,803
  • 2
  • 19
  • 19