/// <summary>
/// 设置excel中单元格的样式
/// djin--2012-5-25
/// </summary>
/// <param name="hssfworkbook"></param>
/// <param name="options"></param>
/// <returns></returns>
public NPOI.SS.UserModel.CellStyle setCellstyle(HSSFWorkbook hssfworkbook, string[] options)
{
NPOI.SS.UserModel.CellStyle style3 = hssfworkbook.CreateCellStyle();
foreach (string s in options)
{
if (s == "Border")
{
style3.BorderBottom = CellBorderType.THIN;
style3.BorderLeft = CellBorderType.THIN;
style3.BorderRight = CellBorderType.THIN;
style3.BorderTop = CellBorderType.THIN;
}
if (s == "Center")
{
style3.VerticalAlignment = VerticalAlignment.CENTER;
style3.Alignment = HorizontalAlignment.CENTER;
}
if (s == "DateTime")
{
NPOI.SS.UserModel.DataFormat format = hssfworkbook.CreateDataFormat();
style3.DataFormat = format.GetFormat("yyyy-m-d");
}
}
return(style3);
}
public void TestDataStyle()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetStyleDate",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
Row row = s.CreateRow(0);
// with Date:
Cell cell = row.CreateCell(1);
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
cell.CellStyle = (cs);
cell.SetCellValue(DateTime.Now);
// with Calendar:
cell = row.CreateCell(2);
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
cell.CellStyle = (cs);
cell.SetCellValue(DateTime.Now);
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(0, s.LastRowNum, "LAST ROW ");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW ");
}
/// <summary>
/// Creates the excel workbook.
/// </summary>
/// <param name="subject">The subject.</param>
public void CreateWorkbook(string subject)
{
//Creating the excel workbook
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
//Creating summary information to the document
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Thoris";
//Applying summary information to the document
wb.DocumentSummaryInformation = dsi;
//Creating summary information for the data
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;
//Applying summary information to the data
wb.SummaryInformation = si;
_workbook = wb;
//Creating the current styles
Font font = _workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
NPOI.SS.UserModel.CellStyle cellStyle = _workbook.CreateCellStyle();
cellStyle.SetFont(font);
_headerStyle = CreateHeaderStyle(_workbook);
_dataStyle = CreateStyle(_workbook, false);
}
public void TestDataStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
Row row = s.CreateRow((short)0);
// with Date:
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
row.RowStyle = (cs);
row.CreateCell(0);
// with Calendar:
row = s.CreateRow((short)1);
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
row.RowStyle = (cs);
row.CreateCell(0);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(1, s.LastRowNum, "LAST ROW ");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW ");
}
public void TestWriteSheetFont()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
Font fnt = wb.CreateFont();
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
fnt.Color = (NPOI.HSSF.Util.HSSFColor.RED.index);
fnt.Boldweight = (short)FontBoldWeight.BOLD;
cs.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle = (cs);
r.CreateCell(0);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
}
private static NPOI.SS.UserModel.CellStyle CreateStyle(HSSFWorkbook wb, HorizontalAlignment h_align, short color,
bool bold)
{
Font font = wb.CreateFont();
if (bold)
{
font.Boldweight = (short)FontBoldWeight.BOLD;
}
NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
cellStyle.SetFont(font);
cellStyle.FillForegroundColor = (color);
cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
cellStyle.Alignment = (h_align);
cellStyle.BorderLeft = (CellBorderType.THIN);
cellStyle.LeftBorderColor = (HSSFColor.BLACK.index);
cellStyle.BorderTop = (CellBorderType.THIN);
cellStyle.TopBorderColor = (HSSFColor.BLACK.index);
cellStyle.BorderRight = (CellBorderType.THIN);
cellStyle.RightBorderColor = (HSSFColor.BLACK.index);
cellStyle.BorderBottom = (CellBorderType.THIN);
cellStyle.BottomBorderColor = (HSSFColor.BLACK.index);
return(cellStyle);
}
public void TestHashEquals()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
NPOI.SS.UserModel.CellStyle cs1 = wb.CreateCellStyle();
NPOI.SS.UserModel.CellStyle cs2 = wb.CreateCellStyle();
Row row = s.CreateRow(0);
Cell cell1 = row.CreateCell(1);
Cell cell2 = row.CreateCell(2);
cs1.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
cs2.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/dd/yy"));
cell1.CellStyle = (cs1);
cell1.SetCellValue(DateTime.Now);
cell2.CellStyle = (cs2);
cell2.SetCellValue(DateTime.Now);
Assert.AreEqual(cs1.GetHashCode(), cs1.GetHashCode());
Assert.AreEqual(cs2.GetHashCode(), cs2.GetHashCode());
Assert.IsTrue(cs1.Equals(cs1));
Assert.IsTrue(cs2.Equals(cs2));
// Change cs1, hash will alter
int hash1 = cs1.GetHashCode();
cs1.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/dd/yy"));
Assert.IsFalse(hash1 == cs1.GetHashCode());
}
public void TestCloneStyleSameWB()
{
HSSFWorkbook wb = new HSSFWorkbook();
Font fnt = wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(5, wb.NumberOfFonts);
NPOI.SS.UserModel.CellStyle orig = wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.RIGHT);
orig.SetFont(fnt);
orig.DataFormat = ((short)18);
Assert.AreEqual(HorizontalAlignment.RIGHT, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont(wb));
Assert.AreEqual(18, orig.DataFormat);
NPOI.SS.UserModel.CellStyle clone = wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreNotEqual(fnt, clone.GetFont(wb));
Assert.AreNotEqual(18, clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreEqual(fnt, clone.GetFont(wb));
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(5, wb.NumberOfFonts);
}
/// <summary>
/// Returns a map containing the format properties of the given cell style.
/// </summary>
/// <param name="style">cell style</param>
/// <returns>map of format properties (String -> Object)</returns>
private static Hashtable GetFormatProperties(NPOI.SS.UserModel.CellStyle style)
{
Hashtable properties = new Hashtable();
PutShort(properties, ALIGNMENT, (short)style.Alignment);
PutShort(properties, BORDER_BOTTOM, (short)style.BorderBottom);
PutShort(properties, BORDER_LEFT, (short)style.BorderLeft);
PutShort(properties, BORDER_RIGHT, (short)style.BorderRight);
PutShort(properties, BORDER_TOP, (short)style.BorderTop);
PutShort(properties, BOTTOM_BORDER_COLOR, style.BottomBorderColor);
PutShort(properties, DATA_FORMAT, style.DataFormat);
PutShort(properties, FILL_BACKGROUND_COLOR, style.FillBackgroundColor);
PutShort(properties, FILL_FOREGROUND_COLOR, style.FillForegroundColor);
PutShort(properties, FILL_PATTERN, (short)style.FillPattern);
PutShort(properties, FONT, style.FontIndex);
PutBoolean(properties, HIDDEN, style.IsHidden);
PutShort(properties, INDENTION, style.Indention);
PutShort(properties, LEFT_BORDER_COLOR, style.LeftBorderColor);
PutBoolean(properties, LOCKED, style.IsLocked);
PutShort(properties, RIGHT_BORDER_COLOR, style.RightBorderColor);
PutShort(properties, ROTATION, style.Rotation);
PutShort(properties, TOP_BORDER_COLOR, style.TopBorderColor);
PutShort(properties, VERTICAL_ALIGNMENT, (short)style.VerticalAlignment);
PutBoolean(properties, WRAP_TEXT, style.WrapText);
return(properties);
}
private void SetCoutent(List <CustomerInfo> list, Sheet sheet)
{
NPOI.SS.UserModel.CellStyle styleCoutent = hssWorkBook.CreateCellStyle();
styleCoutent.BorderBottom = CellBorderType.THIN;
styleCoutent.BorderLeft = CellBorderType.THIN;
styleCoutent.BorderRight = CellBorderType.THIN;
styleCoutent.BorderTop = CellBorderType.THIN;
for (int i = 0; i < list.Count; i++)
{
Row newrow = sheet.CreateRow(i + 3);
Cell cell = newrow.CreateCell(0);
cell.SetCellValue((i + 1).ToString());
cell.CellStyle = styleCoutent;
Cell cell1 = newrow.CreateCell(1);
cell1.SetCellValue(list[i].CustomerID);
cell1.CellStyle = styleCoutent;
Cell cell2 = newrow.CreateCell(2);
cell2.SetCellValue(list[i].Email);
cell2.CellStyle = styleCoutent;
Cell cell3 = newrow.CreateCell(3);
cell3.SetCellValue(list[i].StausString);
cell3.CellStyle = styleCoutent;
}
}
/**
* This method attempt to find an already existing HSSFCellStyle that matches
* what you want the style to be. If it does not find the style, then it
* Creates a new one. If it does Create a new one, then it applies the
* propertyName and propertyValue to the style. This is necessary because
* Excel has an upper limit on the number of Styles that it supports.
*
*@param workbook The workbook that is being worked with.
*@param propertyName The name of the property that is to be
* changed.
*@param propertyValue The value of the property that is to be
* changed.
*@param cell The cell that needs it's style changes
*@exception NestableException Thrown if an error happens.
*/
public static void SetCellStyleProperty(NPOI.SS.UserModel.Cell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue)
{
NPOI.SS.UserModel.CellStyle originalStyle = cell.CellStyle;
NPOI.SS.UserModel.CellStyle newStyle = null;
Hashtable values = GetFormatProperties(originalStyle);
values[propertyName] = propertyValue;
// index seems like what index the cellstyle is in the list of styles for a workbook.
// not good to compare on!
short numberCellStyles = workbook.NumCellStyles;
for (short i = 0; i < numberCellStyles; i++)
{
NPOI.SS.UserModel.CellStyle wbStyle = workbook.GetCellStyleAt(i);
Hashtable wbStyleMap = GetFormatProperties(wbStyle);
if (wbStyleMap.Equals(values))
{
newStyle = wbStyle;
break;
}
}
if (newStyle == null)
{
newStyle = workbook.CreateCellStyle();
SetFormatProperties(newStyle, workbook, values);
}
cell.CellStyle = (newStyle);
}
public void TestDateFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet("TestSheet1");
Row r = null;
Cell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0);
NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"));
c.SetCellValue(new DateTime());
c.CellStyle = (cellStyle);
// Assert.AreEqual("Checking hour = " + hour, date.GetTime().GetTime(),
// NPOI.SS.UserModel.DateUtil.GetJavaDate(excelDate).GetTime());
for (int k = 1; k < 100; k++)
{
r = s.CreateRow(k);
c = r.CreateCell(0);
c.CellFormula = ("A" + (k) + "+1");
c.CellStyle = cellStyle;
}
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
public void TestDoesNoHarmIfNothingToDo()
{
HSSFWorkbook wb = new HSSFWorkbook();
Font f = wb.CreateFont();
f.FontName = ("Testing");
NPOI.SS.UserModel.CellStyle s = wb.CreateCellStyle();
s.SetFont(f);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
// Optimise fonts
HSSFOptimiser.OptimiseFonts(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
// Optimise styles
HSSFOptimiser.OptimiseCellStyles(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
}
public WorkbookFormatter(HSSFWorkbook wb)
{
_wb = wb;
_style_1 = CreateStyle(wb, HorizontalAlignment.LEFT);
_style_2 = CreateStyle(wb, HorizontalAlignment.CENTER);
_style_3 = CreateStyle(wb, HorizontalAlignment.CENTER, HSSFColor.GREY_25_PERCENT.index, true);
_style_4 = CreateHeaderStyle(wb);
}
private static void WriteOtherSettings(NPOI.SS.UserModel.Sheet sheet, NPOI.SS.UserModel.CellStyle style,
String strStettings)
{
Row row = sheet.GetRow(sheet.PhysicalNumberOfRows - 1);
Cell cell = row.CreateCell(5);
cell.CellStyle = (style);
SetCellValue(cell, strStettings);
}
/// <summary>
/// Creates the header style.
/// </summary>
/// <param name="wb">The wb.</param>
/// <returns></returns>
public static NPOI.SS.UserModel.CellStyle CreateHeaderStyle(HSSFWorkbook wb)
{
Font font = wb.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
cellStyle.SetFont(font);
return(cellStyle);
}
public ValidationAdder(NPOI.SS.UserModel.Sheet fSheet, NPOI.SS.UserModel.CellStyle style_1, NPOI.SS.UserModel.CellStyle style_2,
NPOI.SS.UserModel.CellStyle cellStyle, int validationType)
{
_sheet = (HSSFSheet)fSheet;
_style_1 = style_1;
_style_2 = style_2;
_cellStyle = cellStyle;
_validationType = validationType;
_currentRowIndex = fSheet.PhysicalNumberOfRows;
}
public void TestDefaultColumnStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.CellStyle style = wb.CreateCellStyle();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
s.SetDefaultColumnStyle((short)0, style);
Row r = s.CreateRow(0);
Cell c = r.CreateCell(0);
Assert.AreEqual(style.Index, c.CellStyle.Index, "style should Match");
}
public void TestWriteSheetStyle()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetStyle",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
Font fnt = wb.CreateFont();
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
NPOI.SS.UserModel.CellStyle cs2 = wb.CreateCellStyle();
cs.BorderBottom = (CellBorderType.THIN);
cs.BorderLeft = (CellBorderType.THIN);
cs.BorderRight = (CellBorderType.THIN);
cs.BorderTop = (CellBorderType.THIN);
cs.FillForegroundColor = ( short )0xA;
cs.FillPattern = FillPatternType.SOLID_FOREGROUND;
fnt.Color = ( short )0xf;
fnt.IsItalic = (true);
cs2.FillForegroundColor = ( short )0x0;
cs2.FillPattern = FillPatternType.SOLID_FOREGROUND;
cs2.SetFont(fnt);
for (short rownum = ( short )0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
// r.SetRowNum(( short ) rownum);
for (short cellnum = ( short )0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double )rownum / 1000)
+ (( double )cellnum / 10000)));
c.CellStyle = (cs);
c = r.CreateCell(cellnum + 1);
c.SetCellValue("TEST");
c.CellStyle = (cs2);
}
}
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
// assert((s.LastRowNum == 99));
}
/// <summary>
/// Creates the style.
/// </summary>
/// <param name="wb">The wb.</param>
/// <param name="bold">if set to <c>true</c> [bold].</param>
/// <returns></returns>
public static NPOI.SS.UserModel.CellStyle CreateStyle(HSSFWorkbook wb, bool bold)
{
Font font = wb.CreateFont();
if (bold)
{
font.Boldweight = (short)FontBoldWeight.BOLD;
}
NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
cellStyle.SetFont(font);
return(cellStyle);
}
public void TestHSSFCellToStringWithDataFormat()
{
HSSFWorkbook wb = new HSSFWorkbook();
Cell cell = wb.CreateSheet("Sheet1").CreateRow(0).CreateCell(0);
cell.SetCellValue(new DateTime(2009, 8, 20));
NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
cell.CellStyle = cellStyle;
Assert.AreEqual("8/20/09", cell.ToString());
NPOI.SS.UserModel.CellStyle cellStyle2 = wb.CreateCellStyle();
DataFormat format = wb.CreateDataFormat();
cellStyle2.DataFormat = format.GetFormat("YYYY-mm/dd");
cell.CellStyle = cellStyle2;
Assert.AreEqual("2009-08/20", cell.ToString());
}
public void TestWriteDataFormat()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple", ".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
DataFormat format = wb.CreateDataFormat();
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
short df = format.GetFormat("0.0");
cs.DataFormat = (df);
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellStyle = (cs);
c.SetCellValue(1.25);
wb.Write(out1);
out1.Close();
FileStream stream = new FileStream(filepath, FileMode.OpenOrCreate);
POIFSFileSystem fs = new POIFSFileSystem(stream);
HSSFWorkbook workbook = new HSSFWorkbook(fs);
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(0);
Cell cell =
sheet.GetRow(0).GetCell(0);
format = workbook.CreateDataFormat();
Assert.AreEqual(1.25, cell.NumericCellValue, 1e-10);
Assert.AreEqual(format.GetFormat(df), "0.0");
Assert.AreEqual(format, workbook.CreateDataFormat());
stream.Close();
}
public void TestWriteSheetFont()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetFont",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
Font fnt = wb.CreateFont();
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
fnt.Color = (NPOI.HSSF.Util.HSSFColor.RED.index);
fnt.Boldweight = (short)FontBoldWeight.BOLD;
cs.SetFont(fnt);
for (short rownum = ( short )0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
// r.SetRowNum(( short ) rownum);
for (short cellnum = ( short )0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double )rownum / 1000)
+ (( double )cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue("TEST");
c.CellStyle = (cs);
}
}
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
// assert((s.LastRowNum == 99));
}
public void TestCloneStyleDiffWB()
{
HSSFWorkbook wbOrig = new HSSFWorkbook();
Font fnt = wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(5, wbOrig.NumberOfFonts);
DataFormat fmt = wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
NPOI.SS.UserModel.CellStyle orig = wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.RIGHT);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.AreEqual(HorizontalAlignment.RIGHT, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont(wbOrig));
Assert.AreEqual(fmt.GetFormat("Test##"), orig.DataFormat);
// Now a style on another workbook
HSSFWorkbook wbClone = new HSSFWorkbook();
Assert.AreEqual(4, wbClone.NumberOfFonts);
DataFormat fmtClone = wbClone.CreateDataFormat();
NPOI.SS.UserModel.CellStyle clone = wbClone.CreateCellStyle();
Assert.AreEqual(4, wbClone.NumberOfFonts);
Assert.AreNotEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreNotEqual("TestingFont", clone.GetFont(wbClone).FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont(wbClone).FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.AreNotEqual(fmtClone.GetFormat("Test##"), fmt.GetFormat("Test##"));
Assert.AreEqual(5, wbClone.NumberOfFonts);
}
public void TestFormulaStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet("TestSheet1");
Row r = null;
Cell c = null;
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
Font f = wb.CreateFont();
f.FontHeightInPoints = ((short)20);
f.Color = (HSSFColor.RED.index);
f.Boldweight = (short)FontBoldWeight.BOLD;
f.FontName = ("Arial Unicode MS");
cs.FillBackgroundColor = ((short)3);
cs.SetFont(f);
cs.BorderTop = CellBorderType.THIN;
cs.BorderRight = CellBorderType.THIN;
cs.BorderLeft = CellBorderType.THIN;
cs.BorderBottom = CellBorderType.THIN;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellStyle = (cs);
c.CellFormula = ("2*3");
wb = WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue((c.CellType == NPOI.SS.UserModel.CellType.FORMULA), "Formula Cell at 0,0");
cs = c.CellStyle;
Assert.IsNotNull(cs, "Formula Cell Style");
Assert.AreEqual(cs.FontIndex, f.Index, "Font Index Matches");
Assert.AreEqual((short)cs.BorderTop, (short)1, "Top Border");
Assert.AreEqual((short)cs.BorderLeft, (short)1, "Left Border");
Assert.AreEqual((short)cs.BorderRight, (short)1, "Right Border");
Assert.AreEqual((short)cs.BorderBottom, (short)1, "Bottom Border");
}
private static void AddDateTimeValidations(WorkbookFormatter wf, HSSFWorkbook wb)
{
wf.CreateSheet("Dates and Times");
DataFormat dataFormat = wb.CreateDataFormat();
short fmtDate = dataFormat.GetFormat("m/d/yyyy");
short fmtTime = dataFormat.GetFormat("h:mm");
NPOI.SS.UserModel.CellStyle cellStyle_date = wb.CreateCellStyle();
cellStyle_date.DataFormat = (fmtDate);
NPOI.SS.UserModel.CellStyle cellStyle_time = wb.CreateCellStyle();
cellStyle_time.DataFormat = (fmtTime);
wf.CreateDVTypeRow("Date ( cells are already formated as date - m/d/yyyy)");
wf.CreateHeaderRow();
ValidationAdder va = wf.CreateValidationAdder(cellStyle_date, DVConstraint.ValidationType.DATE);
va.AddValidation(DVConstraint.OperatorType.BETWEEN, "2004/01/02", "2004/01/06", HSSFDataValidation.ERRORSTYLE.STOP, "Between 1/2/2004 and 1/6/2004 ", "Error box type = STOP", true, true, true);
va.AddValidation(DVConstraint.OperatorType.NOT_BETWEEN, "2004/01/01", "2004/01/06", HSSFDataValidation.ERRORSTYLE.INFO, "Not between 1/2/2004 and 1/6/2004 ", "Error box type = INFO", false, true, true);
va.AddValidation(DVConstraint.OperatorType.EQUAL, "2004/03/02", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Equal to 3/2/2004", "Error box type = WARNING", false, false, true);
va.AddValidation(DVConstraint.OperatorType.NOT_EQUAL, "2004/03/02", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Not equal to 3/2/2004", "-", false, false, false);
va.AddValidation(DVConstraint.OperatorType.GREATER_THAN, "=DATEVALUE(\"4-Jul-2001\")", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Greater than DATEVALUE('4-Jul-2001')", "-", true, false, false);
va.AddValidation(DVConstraint.OperatorType.LESS_THAN, "2004/03/02", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Less than 3/2/2004", "-", true, true, false);
va.AddValidation(DVConstraint.OperatorType.GREATER_OR_EQUAL, "2004/03/02", null, HSSFDataValidation.ERRORSTYLE.STOP, "Greater than or equal to 3/2/2004", "Error box type = STOP", true, false, true);
va.AddValidation(DVConstraint.OperatorType.LESS_OR_EQUAL, "2004/03/04", null, HSSFDataValidation.ERRORSTYLE.STOP, "Less than or equal to 3/4/2004", "-", false, true, false);
// "Time" validation type
wf.CreateDVTypeRow("Time ( cells are already formated as time - h:mm)");
wf.CreateHeaderRow();
va = wf.CreateValidationAdder(cellStyle_time, DVConstraint.ValidationType.TIME);
va.AddValidation(DVConstraint.OperatorType.BETWEEN, "12:00", "16:00", HSSFDataValidation.ERRORSTYLE.STOP, "Between 12:00 and 16:00 ", "Error box type = STOP", true, true, true);
va.AddValidation(DVConstraint.OperatorType.NOT_BETWEEN, "12:00", "16:00", HSSFDataValidation.ERRORSTYLE.INFO, "Not between 12:00 and 16:00 ", "Error box type = INFO", false, true, true);
va.AddValidation(DVConstraint.OperatorType.EQUAL, "13:35", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Equal to 13:35", "Error box type = WARNING", false, false, true);
va.AddValidation(DVConstraint.OperatorType.NOT_EQUAL, "13:35", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Not equal to 13:35", "-", false, false, false);
va.AddValidation(DVConstraint.OperatorType.GREATER_THAN, "12:00", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Greater than 12:00", "-", true, false, false);
va.AddValidation(DVConstraint.OperatorType.LESS_THAN, "=1/2", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Less than (1/2) -> 12:00", "-", true, true, false);
va.AddValidation(DVConstraint.OperatorType.GREATER_OR_EQUAL, "14:00", null, HSSFDataValidation.ERRORSTYLE.STOP, "Greater than or equal to 14:00", "Error box type = STOP", true, false, true);
va.AddValidation(DVConstraint.OperatorType.LESS_OR_EQUAL, "14:00", null, HSSFDataValidation.ERRORSTYLE.STOP, "Less than or equal to 14:00", "-", false, true, false);
}
/**
* Writes plain text values into cells in a tabular format to form comments readable from within
* the spreadsheet.
*/
private static void WriteDataValidationSettings(NPOI.SS.UserModel.Sheet sheet, NPOI.SS.UserModel.CellStyle style_1,
NPOI.SS.UserModel.CellStyle style_2, String strCondition, bool allowEmpty, bool inputBox,
bool errorBox)
{
Row row = sheet.CreateRow(sheet.PhysicalNumberOfRows);
// condition's string
Cell cell = row.CreateCell(1);
cell.CellStyle = (style_1);
SetCellValue(cell, strCondition);
// allow empty cells
cell = row.CreateCell(2);
cell.CellStyle = (style_2);
SetCellValue(cell, ((allowEmpty) ? "yes" : "no"));
// show input box
cell = row.CreateCell(3);
cell.CellStyle = (style_2);
SetCellValue(cell, ((inputBox) ? "yes" : "no"));
// show error box
cell = row.CreateCell(4);
cell.CellStyle = (style_2);
SetCellValue(cell, ((errorBox) ? "yes" : "no"));
}
private static NPOI.SS.UserModel.CellStyle CreateHeaderStyle(HSSFWorkbook wb)
{
Font font = wb.CreateFont();
font.Color = (HSSFColor.WHITE.index);
font.Boldweight = (short)FontBoldWeight.BOLD;
NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
cellStyle.FillForegroundColor = (HSSFColor.BLUE_GREY.index);
cellStyle.FillPattern = (FillPatternType.SOLID_FOREGROUND);
cellStyle.Alignment = (HorizontalAlignment.CENTER);
cellStyle.VerticalAlignment = (NPOI.SS.UserModel.VerticalAlignment.CENTER);
cellStyle.BorderLeft = (CellBorderType.THIN);
cellStyle.LeftBorderColor = (HSSFColor.WHITE.index);
cellStyle.BorderTop = (CellBorderType.THIN);
cellStyle.TopBorderColor = (HSSFColor.WHITE.index);
cellStyle.BorderRight = (CellBorderType.THIN);
cellStyle.RightBorderColor = (HSSFColor.WHITE.index);
cellStyle.BorderBottom = (CellBorderType.THIN);
cellStyle.BottomBorderColor = (HSSFColor.WHITE.index);
cellStyle.SetFont(font);
return(cellStyle);
}
/// <summary>
/// Sets the format properties of the given style based on the given map.
/// </summary>
/// <param name="style">The cell style</param>
/// <param name="workbook">The parent workbook.</param>
/// <param name="properties">The map of format properties (String -> Object).</param>
private static void SetFormatProperties(
NPOI.SS.UserModel.CellStyle style, HSSFWorkbook workbook, Hashtable properties)
{
style.Alignment = (NPOI.SS.UserModel.HorizontalAlignment)GetShort(properties, ALIGNMENT);
style.BorderBottom = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_BOTTOM);
style.BorderLeft = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_LEFT);
style.BorderRight = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_RIGHT);
style.BorderTop = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_TOP);
style.BottomBorderColor = (GetShort(properties, BOTTOM_BORDER_COLOR));
style.DataFormat = (GetShort(properties, DATA_FORMAT));
style.FillBackgroundColor = (GetShort(properties, FILL_BACKGROUND_COLOR));
style.FillForegroundColor = (GetShort(properties, FILL_FOREGROUND_COLOR));
style.FillPattern = (NPOI.SS.UserModel.FillPatternType)GetShort(properties, FILL_PATTERN);
style.SetFont(workbook.GetFontAt(GetShort(properties, FONT)));
style.IsHidden = (GetBoolean(properties, HIDDEN));
style.Indention = (GetShort(properties, INDENTION));
style.LeftBorderColor = (GetShort(properties, LEFT_BORDER_COLOR));
style.IsLocked = (GetBoolean(properties, LOCKED));
style.RightBorderColor = (GetShort(properties, RIGHT_BORDER_COLOR));
style.Rotation = (GetShort(properties, ROTATION));
style.TopBorderColor = (GetShort(properties, TOP_BORDER_COLOR));
style.VerticalAlignment = (NPOI.SS.UserModel.VerticalAlignment)GetShort(properties, VERTICAL_ALIGNMENT);
style.WrapText = (GetBoolean(properties, WRAP_TEXT));
}
/// <summary>
/// 金董春：添加打印功能，添加了TransportationBill.xls模板打印功能 2012-5-28
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public string WriteToFile(IList <object> list)
{
string path = Server.MapPath(".") + @"\Reports\Templates\YFKExcelTemplates\TransportationBill.xls";
if (File.Exists(path))
{
TransportationBill tb = (TransportationBill)list[0];
string filename = @"/Reports/Templates/TempFiles/temp_" + DateTime.Now.ToString("yyyyMMddhhmmss") + tb.BillNo + ".xls";
string _wpath = Server.MapPath(".") + filename;
File.Copy(path, _wpath);
FileStream file = new FileStream(_wpath, FileMode.Open, FileAccess.ReadWrite, FileShare.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
Sheet sheet = hssfworkbook.GetSheet("sheet1");
NPOI.SS.UserModel.CellStyle normalStyle = setCellstyle(hssfworkbook, new string[] { "Border", "Center" });
NPOI.SS.UserModel.CellStyle dateStyle = setCellstyle(hssfworkbook, new string[] { "Border", "Center", "DateTime" });
Cell cell = sheet.GetRow(2).GetCell(1);
cell.SetCellValue(tb.BillNo);
cell = sheet.GetRow(2).GetCell(6);
cell.SetCellValue(tb.ReferenceBillNo);
cell = sheet.GetRow(3).GetCell(1);
cell.SetCellValue(tb.Status);
int i = 10;
decimal cnt = 0;
foreach (TransportationBillDetail tbd in tb.TransportationBillDetails)
{
Row row = sheet.CreateRow(i);
TransportationOrder tord = TheTransportationOrderMgr.LoadTransportationOrder(tbd.ActBill.OrderNo);
row.CreateCell(0).SetCellValue(tord.CreateDate); //运输日期
row.CreateCell(1).SetCellValue(tord.TransportationRoute != null ? tord.TransportationRoute.Description : ""); //运输路线
row.CreateCell(2).SetCellValue(tbd.ActBill.PricingMethod != null ? tbd.ActBill.PricingMethod : ""); //运输形式
row.CreateCell(3).SetCellValue(tord.OrderNo); //运单号码
row.CreateCell(4).SetCellValue(tbd.ActBill.EffectiveDate); //生效日期
row.CreateCell(5).SetCellValue(tbd.ActBill.UnitPrice.ToString("F2")); //单价
row.CreateCell(6).SetCellValue(tbd.ActBill.Currency.Name); //币种
row.CreateCell(7).SetCellValue(tbd.ActBill.BillQty.ToString("F0")); //开票数
row.CreateCell(8).SetCellValue(tbd.ActBill.BillAmount.ToString("F2")); //金额
cnt = Convert.ToInt32(tbd.ActBill.BillAmount) + cnt;
for (int y = 0; y < 9; y++)
{
row.GetCell(y).CellStyle = normalStyle;
}
row.GetCell(0).CellStyle = dateStyle;
row.GetCell(4).CellStyle = dateStyle;
i++;
}
if (i <= 20)
{
for (int j = i; j < 21; j++)
{
Row row = sheet.CreateRow(j);
for (int y = 0; y < 9; y++)
{
row.CreateCell(y).CellStyle = normalStyle;
}
}
i = 20;
}
Row _row = sheet.CreateRow(i + 1);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i + 1, i + 1, 6, 7));
_row.CreateCell(6).SetCellValue("合计发票金额：");
_row.GetCell(6).CellStyle.Alignment = HorizontalAlignment.RIGHT;
_row.CreateCell(8).SetCellValue(cnt.ToString("F2"));
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
// Response.AddHeader("Content-Disposition", string.Format("attachment;filename=TempWorkBook.xls"));
// Response.BinaryWrite(ms.ToArray());Reports/Templates/TempFiles
FileStream f = new FileStream(_wpath, FileMode.Open, FileAccess.Write);
byte[] data = ms.ToArray();
f.Write(data, 0, data.Length);
f.Close();
f.Dispose();
hssfworkbook = null;
ms.Close();
ms.Dispose();
return("http://" + Request.Url.Authority + filename);
}
return("");
}
