import jxl.format.Colour; //导入依赖的package包/类
/**
 * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
 */
public static void format() {
    try {
        arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
        arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
        arial14format = new WritableCellFormat(arial14font);
        arial14format.setAlignment(jxl.format.Alignment.CENTRE);
        arial14format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);

        arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
        arial10format = new WritableCellFormat(arial10font);
        arial10format.setAlignment(jxl.format.Alignment.CENTRE);
        arial10format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        arial10format.setBackground(Colour.GRAY_25);

        arial12font = new WritableFont(WritableFont.ARIAL, 10);
        arial12format = new WritableCellFormat(arial12font);
        arial10format.setAlignment(jxl.format.Alignment.CENTRE);//对齐格式
        arial12format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); //设置边框

    } catch (WriteException e) {
        e.printStackTrace();
    }
}
 

import jxl.format.Colour; //导入依赖的package包/类
public static WritableCellFormat getHeader() {
	WritableFont font = new WritableFont(WritableFont.TIMES, 10,
			WritableFont.BOLD);// 定义字体
	try {
		font.setColour(Colour.BLUE);// 蓝色字体
	} catch (WriteException e1) {
		e1.printStackTrace();
	}
	WritableCellFormat format = new WritableCellFormat(font);
	try {
		format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
		format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
		// format.setBorder(Border.ALL, BorderLineStyle.THIN,
		// Colour.BLACK);// 黑色边框
		// format.setBackground(Colour.YELLOW);// 黄色背景
	} catch (WriteException e) {
		e.printStackTrace();
	}
	return format;
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * ExcelWriter constructor. 
 * @param outputFile Excel 2003 output workbook.
 * @throws IOException Unable to work with output location.
 */
public ExcelWriter(File outputFile) throws IOException
{
    workbook = Workbook.createWorkbook(outputFile);
    WritableFont font1 = new WritableFont(WritableFont.ARIAL, 10); 
    WritableFont font2= new WritableFont(WritableFont.ARIAL, 10);
    WritableFont font3= new WritableFont(WritableFont.ARIAL, 10);
    WritableFont defaultFont= new WritableFont(WritableFont.ARIAL, 10);
    try {
        font1.setColour(Colour.DARK_BLUE);
        font1.setBoldStyle(WritableFont.BOLD);
        font2.setBoldStyle(WritableFont.BOLD);
        font2.setColour(Colour.RED);
        font3.setColour(Colour.BLACK);
        font3.setBoldStyle(WritableFont.BOLD);
        } 
    catch (WriteException ex) 
    {
     
    }
    
    BoldBlue= new WritableCellFormat(font1);
    BoldRed= new WritableCellFormat(font2);
    BoldBlack= new WritableCellFormat(font3);
    Default= new WritableCellFormat(defaultFont);
}
 

import jxl.format.Colour; //导入依赖的package包/类
private void annotateCellWithError(jxl.Cell parsedCell, jxl.write.WritableSheet annotatedErrorSheet, WorkbookParseError error) throws WriteException
  {
    Label errorLabel = new Label(parsedCell.getColumn(),
                                 parsedCell.getRow(),
                                 (parsedCell.getContents().trim().length() > 0 ? parsedCell.getContents() + ": " : "") + error.getErrorMessage());
    annotatedErrorSheet.addCell(errorLabel);
    
//  WritableCellFeatures cellFeatures = new WritableCellFeatures();
//  cellFeatures.setComment(error.getMessage());
//  errorAnnotatedCell.setCellFeatures(cellFeatures);
    
    // See [#1866]
    // TODO: this may be where we are getting the error messages:
    // "Warning:  Maximum number of format records exceeded.  Using default format."
    // which eventually results in a failure to write the error workbook.
    // see http://tech.groups.yahoo.com/group/JExcelApi/message/102
    // Unfortunately, the solution is not (has no effect):
    // errorLabel.setCellFormat(SOME_STATIC_FORMAT)
   
    WritableCellFormat newFormat = new WritableCellFormat();
    newFormat.setBackground(Colour.RED);
    errorLabel.setCellFormat(newFormat);
  }
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * Default constructor - used when there is no palette specified
 */
public PaletteRecord()
{
  super(Type.PALETTE);

  initialized = true;
  dirty = false;
  read = false;

  // Initialize the array with all the default colours
  Colour[] colours = Colour.getAllColours();

  for (int i = 0; i < colours.length; i++)
  {
    Colour c = colours[i];
    setColourRGB(c,
                 c.getDefaultRGB().getRed(),
                 c.getDefaultRGB().getGreen(),
                 c.getDefaultRGB().getBlue());
  }
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * Sets the RGB value for the specified colour for this workbook
 *
 * @param c the colour whose RGB value is to be overwritten
 * @param r the red portion to set (0-255)
 * @param g the green portion to set (0-255)
 * @param b the blue portion to set (0-255)
 */
public void setColourRGB(Colour c, int r, int g, int b)
{
  // Only colours on the standard palette with values 8-64 are acceptable
  int pos = c.getValue() - 8;
  if (pos < 0 || pos >= numColours)
  {
    return;
  }

  if (!initialized)
  {
    initialize();
  }

  // Force the colours into the range 0-255
  r = setValueRange(r, 0, 0xff);
  g = setValueRange(g, 0, 0xff);
  b = setValueRange(b, 0, 0xff);

  rgbColours[pos] = new RGB(r, g, b);

  // Indicate that the palette has been modified
  dirty = true;
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * Gets the colour RGB from the palette
 *
 * @param c the colour
 * @return an RGB structure
 */
public RGB getColourRGB(Colour c)
{
  // Only colours on the standard palette with values 8-64 are acceptable
  int pos = c.getValue() - 8;
  if (pos < 0 || pos >= numColours)
  {
    return c.getDefaultRGB();
  }

  if (!initialized)
  {
    initialize();
  }

  return rgbColours[pos];
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * 根据错误信息进行提示--excel表格背景设置为红色，表示导入信息有误
 * @param cellInfo
 * @param cellNum
 * @param columnNum
 * @param value
 * @return
 */
private Label getLabelInfo(Map<Integer,String> cellInfo,int cellNum,int columnNum,String value,Material material)
{
	Label label = null;

	//设置背景颜色
	WritableCellFormat cellFormat = new WritableCellFormat();
	try
	{
		cellFormat.setBackground(Colour.RED);
	}
	catch (WriteException e)
	{
		Log.errorFileSync(">>>>>>>>>>>>>>>>>>>>>设置单元格背景颜色错误", e);
	}

	if(null == cellInfo || cellInfo.size() == 0) {
		label = new Label(cellNum, columnNum, value);
	}
	else {
		//表示此单元格有错误
		if(cellInfo.containsKey(cellNum)) {
			if(cellNum == MaterialConstants.BusinessForExcel.EXCEL_SAFETY_STOCK) {
				label = new Label(cellNum, columnNum, material.getSafetyStockStr(), cellFormat);
			}
		}
		else{
			label =  new Label(cellNum, columnNum, value);
		}
	}
	return label;
}
 

import jxl.format.Colour; //导入依赖的package包/类
private static void setBgColor(WritableCellFormat cellFormat, Color bgColor) {
	Colour colour = convertColor(bgColor);
	if (colour != null) {
		try {
			cellFormat.setBackground(colour);
		} catch (WriteException e) {
		}
	}
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * Create the index worksheet.
 *
 * <p>This also creates links back to the index in each of the worksheets.</p>
 */
public void createIndex() {
  WritableSheet sheet = workbook.createSheet(spreadsheetifyName("Index"), 0);
  createTitle(sheet, "Index");

  try {
    // Create links for each worksheet, apart from the first sheet which is the
    // index we're currently creating
    final String[] names = workbook.getSheetNames();
    for (int currentSheet = 1; currentSheet < names.length; currentSheet++) {
      // Create the link from the index to the table's worksheet
      WritableHyperlink link = new WritableHyperlink(0, currentSheet - 1 + NUMBER_OF_ROWS_IN_TITLE, names[currentSheet], workbook.getSheet(currentSheet), 0, 0);
      sheet.addHyperlink(link);

      //Add the filename in column B (stored in cell B2 of each sheet)
      String fileName = workbook.getSheet(currentSheet).getCell(1, 1).getContents();
      Label fileNameLabel = new Label(1, currentSheet - 1 + NUMBER_OF_ROWS_IN_TITLE, fileName);
      WritableFont fileNameFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
      WritableCellFormat fileNameFormat = new WritableCellFormat(fileNameFont);
      fileNameLabel.setCellFormat(fileNameFormat);
      sheet.addCell(fileNameLabel);

      // Create the link back to the index
      link = new WritableHyperlink(0, 1, "Back to index", sheet, 0, currentSheet + NUMBER_OF_ROWS_IN_TITLE - 1);
      workbook.getSheet(currentSheet).addHyperlink(link);
      //Set column A of each sheet to be wide enough to show "Back to index"
      workbook.getSheet(currentSheet).setColumnView(0, 13);
    }

    // Make Column A fairly wide to show tab names and hide column B
    sheet.setColumnView(0, 35);
    sheet.setColumnView(1, 0);

  } catch (Exception e) {
    throw new RuntimeException(e);
  }
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * @return the format to use for bold cells
 * @throws WriteException if the format could not be created
 */
private WritableCellFormat getBoldFormat() throws WriteException {
  WritableFont boldFont = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD);
  WritableCellFormat boldHeading = new WritableCellFormat(boldFont);
  boldHeading.setBorder(Border.BOTTOM, BorderLineStyle.MEDIUM);
  boldHeading.setVerticalAlignment(VerticalAlignment.CENTRE);
  boldHeading.setBackground(Colour.GRAY_25);


  WritableCellFormat boldFormat = new WritableCellFormat(boldFont);
  boldFormat.setVerticalAlignment(VerticalAlignment.TOP);
  return boldFormat;
}
 

import jxl.format.Colour; //导入依赖的package包/类
public Colour discover(Color color) {
    for (Colour colour : Colour.getAllColours()) {
        if ( colour.getDefaultRGB().getBlue() == color.getBlue()
                && colour.getDefaultRGB().getRed() == color.getRed()
                && colour.getDefaultRGB().getGreen() == color.getGreen() ) {
            return colour;
        }
    }
    return Colour.UNKNOWN;
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
	 * @return the formatRed
	 */ 
	public WritableCellFormat getFormatRed() throws Exception{
//		if(formatRed == null){			
			formatRed=new WritableCellFormat();
			formatRed.setAlignment(jxl.format.Alignment.CENTRE);
			formatRed.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			WritableFont wf_color = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
			WritableCellFormat wff_color = new WritableCellFormat(wf_color);	
			formatRed.setBorder(Border.ALL, BorderLineStyle.THIN);
			formatRed.setFont(wf_color);				
			
//		}
		return formatRed;
	}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * Uses the JExcelAPI to create a spreadsheet
 * 
 * @exception IOException
 * @exception WriteException
 */
public void write() throws IOException, WriteException
{
  WorkbookSettings ws = new WorkbookSettings();
  ws.setLocale(new Locale("en", "EN"));
  workbook = Workbook.createWorkbook(new File(filename), ws);


  WritableSheet s2 = workbook.createSheet("Number Formats", 0);
  WritableSheet s3 = workbook.createSheet("Date Formats", 1);
  WritableSheet s1 = workbook.createSheet("Label Formats", 2);
  WritableSheet s4 = workbook.createSheet("Borders", 3);
  WritableSheet s5 = workbook.createSheet("Labels", 4);
  WritableSheet s6 = workbook.createSheet("Formulas", 5);
  WritableSheet s7 = workbook.createSheet("Images", 6);
  //    WritableSheet s8 = workbook.createSheet
  //      ("'Illegal chars in name !*%^?': which exceeds max name length",7);

  // Modify the colour palette to bright red for the lime colour
  workbook.setColourRGB(Colour.LIME, 0xff, 0, 0);

  // Add a named range to the workbook
  workbook.addNameArea("namedrange", s4, 1, 11, 5, 14);
  workbook.addNameArea("validation_range", s1, 4, 65, 9, 65);
  workbook.addNameArea("formulavalue", s6, 1, 45, 1, 45);

  // Add a print area to the "Labels" sheet
  s5.getSettings().setPrintArea(4,4,15,35);

  writeLabelFormatSheet(s1);
  writeNumberFormatSheet(s2);
  writeDateFormatSheet(s3);
  writeBordersSheet(s4);
  writeLabelsSheet(s5);
  writeFormulaSheet(s6);
  writeImageSheet(s7);

  workbook.write();
  workbook.close();
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * Sets the RGB value for the specified colour for this workbook
 *
 * @param c the colour whose RGB value is to be overwritten
 * @param r the red portion to set (0-255)
 * @param g the green portion to set (0-255)
 * @param b the blue portion to set (0-255)
 */
public void setColourRGB(Colour c, int r, int g, int b)
{
  if (palette == null)
  {
    palette = new PaletteRecord();
  }
  palette.setColourRGB(c, r, g, b);
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * Accessor for the RGB value for the specified colour
 *
 * @return the RGB for the specified colour
 */
public RGB getColourRGB(Colour c)
{
  if (palette == null)
  {
    return c.getDefaultRGB();
  }

  return palette.getColourRGB(c);
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * Gets the background colour used by this cell
 *
 * @return the foreground colour
 */
public Colour getBackgroundColour()
{
  if (!formatInfoInitialized)
  {
    initializeFormatInformation();
  }

  return backgroundColour;
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * Sets the border for this cell
 * This method should only be called from its writable subclass
 * CellXFRecord
 *
 * @param b the border
 * @param ls the border line style
 */
protected void setXFBorder(Border b, BorderLineStyle ls, Colour c)
{
  Assert.verify(!initialized);
  
  if (c == Colour.BLACK || c == Colour.UNKNOWN) 
  {
    c = Colour.PALETTE_BLACK;
  }

  if (b == Border.LEFT)
  {
    leftBorder = ls;
    leftBorderColour = c;
  }
  else if (b == Border.RIGHT)
  {
    rightBorder = ls;
    rightBorderColour = c;
  }
  else if (b == Border.TOP)
  {
    topBorder = ls;
    topBorderColour = c;
  }
  else if (b == Border.BOTTOM)
  {
    bottomBorder = ls;
    bottomBorderColour = c;
  }

  usedAttributes |= USE_BORDER;

  return;
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * Gets the line style for the given cell border
 * If a border type of ALL or NONE is specified, then a line style of
 * NONE is returned
 *
 * @param border the cell border we are interested in
 * @return the line style of the specified border
 */
public Colour getBorderColour(Border border)
{
  // Don't bother with the short cut records
  if (border == Border.NONE ||
      border == Border.ALL)
  {
    return Colour.PALETTE_BLACK;
  }

  if (!formatInfoInitialized)
  {
    initializeFormatInformation();
  }

  if (border == Border.LEFT)
  {
    return leftBorderColour;
  }
  else if (border == Border.RIGHT)
  {
    return rightBorderColour;
  }
  else if (border == Border.TOP)
  {
    return topBorderColour;
  }
  else if (border == Border.BOTTOM)
  {
    return bottomBorderColour;
  }

  return Colour.BLACK;  	
}
 

import jxl.format.Colour; //导入依赖的package包/类
/**
 * Creates a default font, vanilla font of the specified face and with
 * default point size.
 *
 * @param fn the font name
 */
public WritableFont(FontName fn)
{
  this(fn,
       DEFAULT_POINT_SIZE,
       NO_BOLD,
       false,
       UnderlineStyle.NO_UNDERLINE,
       Colour.BLACK,
       ScriptStyle.NORMAL_SCRIPT);
}