/**
* Use Excel conditional formatting to highlight payments that are due in the next thirty days.
* In this example, Due dates are entered in cells A2:A4.
*/
static void Expiry(ISheet sheet)
{
ICellStyle style = sheet.Workbook.CreateCellStyle();
style.DataFormat = (short)BuiltinFormats.GetBuiltinFormat("d-mmm");
sheet.CreateRow(0).CreateCell(0).SetCellValue("Date");
sheet.CreateRow(1).CreateCell(0).SetCellFormula("TODAY()+29");
sheet.CreateRow(2).CreateCell(0).SetCellFormula("A2+1");
sheet.CreateRow(3).CreateCell(0).SetCellFormula("A3+1");
for (int rownum = 1; rownum <= 3; rownum++)
{
sheet.GetRow(rownum).GetCell(0).CellStyle = style;
}
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Formula Is =A2=A1 (White Font)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
IFontFormatting font = rule1.CreateFontFormatting();
font.SetFontStyle(false, true);
font.FontColorIndex = IndexedColors.Blue.Index;
CellRangeAddress[] regions =
{
CellRangeAddress.ValueOf("A2:A4")
};
sheetCF.AddConditionalFormatting(regions, rule1);
sheet.GetRow(0).CreateCell(1).SetCellValue("Dates within the next 30 days are highlighted");
}
public void removeNumberFormat()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
String fmt = customDataFormat;
short fmtIdx = (short)wb.GetStylesSource().PutNumberFormat(fmt);
ICell cell = wb.CreateSheet("test").CreateRow(0).CreateCell(0);
cell.SetCellValue(5.25);
ICellStyle style = wb.CreateCellStyle();
style.DataFormat = fmtIdx;
cell.CellStyle = style;
Assert.AreEqual(fmt, cell.CellStyle.GetDataFormatString());
Assert.AreEqual(fmt, wb.GetStylesSource().GetNumberFormatAt(fmtIdx));
// remove the number format from the workbook
wb.GetStylesSource().RemoveNumberFormat(fmt);
// number format in CellStyles should be restored to default number format
short defaultFmtIdx = 0;
String defaultFmt = BuiltinFormats.GetBuiltinFormat(0);
Assert.AreEqual(defaultFmtIdx, style.DataFormat);
Assert.AreEqual(defaultFmt, style.GetDataFormatString());
// The custom number format should be entirely removed from the workbook
SortedDictionary <short, String> numberFormats = wb.GetStylesSource().GetNumberFormats() as SortedDictionary <short, String>;
assertNotContainsKey(numberFormats, fmtIdx);
assertNotContainsValue(numberFormats, fmt);
// The default style shouldn't be added back to the styles source because it's built-in
Assert.AreEqual(0, wb.GetStylesSource().NumDataFormats);
cell = null; style = null; numberFormats = null;
wb = XSSFTestDataSamples.WriteOutCloseAndReadBack(wb);
cell = wb.GetSheet("test").GetRow(0).GetCell(0);
style = cell.CellStyle;
// number format in CellStyles should be restored to default number format
Assert.AreEqual(defaultFmtIdx, style.DataFormat);
Assert.AreEqual(defaultFmt, style.GetDataFormatString());
// The custom number format should be entirely removed from the workbook
numberFormats = wb.GetStylesSource().GetNumberFormats() as SortedDictionary <short, String>;
assertNotContainsKey(numberFormats, fmtIdx);
assertNotContainsValue(numberFormats, fmt);
// The default style shouldn't be added back to the styles source because it's built-in
Assert.AreEqual(0, wb.GetStylesSource().NumDataFormats);
}
finally
{
wb.Close();
}
}
/**
* get the format string that matches the given format index
* @param index of a format
* @return string represented at index of format or null if there is not a format at that index
*/
public String GetFormat(int index)
{
String fmt = stylesSource.GetNumberFormatAt(index);
if (fmt == null)
{
fmt = BuiltinFormats.GetBuiltinFormat(index);
}
return(fmt);
}
/**
* Get the format index that matches the given format
* string, creating a new format entry if required.
* Aliases text to the proper format as required.
*
* @param format string matching a built in format
* @return index of format.
*/
public short GetFormat(String format)
{
int idx = BuiltinFormats.GetBuiltinFormat(format);
if (idx == -1)
{
idx = stylesSource.PutNumberFormat(format);
}
return((short)idx);
}
public short GetFormat(string format)
{
int num = BuiltinFormats.GetBuiltinFormat(format);
if (num == -1)
{
num = this.stylesSource.PutNumberFormat(format);
}
return((short)num);
}
/**
* get the format string that matches the given format index
* @param index of a format
* @return string represented at index of format or null if there is not a format at that index
*/
public String GetFormat(int index)
{
// Indices used for built-in formats may be overridden with
// custom formats, such as locale-specific currency.
// See org.apache.poi.xssf.usermodel.TestXSSFDataFormat#test49928()
// or bug 49928 for an example.
// This is why we need to check stylesSource first and only fall back to
// BuiltinFormats if the format hasn't been overridden.
String fmt = stylesSource.GetNumberFormatAt(index);
if (fmt == null)
{
fmt = BuiltinFormats.GetBuiltinFormat(index);
}
return(fmt);
}
public byte[] DownloadCreateUserForm(string[] headers)
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet();
var header = sheet.CreateRow(0);
var cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = (short)BuiltinFormats.GetBuiltinFormat("@");
sheet.DefaultColumnWidth = DEFAULT_COLUMN_WIDTH;
var headerFont = (XSSFFont)workbook.CreateFont();
headerFont.FontHeightInPoints = DEFAULT_HEADER_FONT_SIZE;
headerFont.FontName = DEFAULT_HEADER_FONT_NAME;
headerFont.Boldweight = (short)FontBoldWeight.Bold;
var headerStyle = workbook.CreateCellStyle();
headerStyle.SetFont(headerFont);
sheet.CreateFreezePane(0, 1, 0, 1); // sabit başlık
for (int i = 0; i < headers.Length; i++)
{
sheet.SetColumnWidth(i, 30 * 300);
}
for (int i = 0; i < headers.Length; i++)
{
header.CreateCell(i).SetCellValue(headers[i]);
header.GetCell(i).CellStyle = headerStyle;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms.ToArray());
}
public static void assumeCustomDataFormatIsNotBuiltIn()
{
Assert.AreEqual(-1, BuiltinFormats.GetBuiltinFormat(customDataFormat));
}
public string ExportReport(string name, eDriverType drv, string sql, object [] args, string qname, string group, string subgroup, MemberPrincipal person)
{
this.person = person;
GenerateParams(qname, group, subgroup);
int cn = 0, rn = 4, sn = 0;
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Лист" + sn++);
IRow row = null;
sheet.SetColumnWidth(cn, 6144);
#region header cell style
IFont hf = wb.CreateFont();
hf.FontHeightInPoints = 18;
hf.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle hs = wb.CreateCellStyle();
hs.SetFont(hf);
#endregion
#region border cell style
ICellStyle bs = wb.CreateCellStyle();
bs.BorderLeft = bs.BorderRight = bs.BorderTop = bs.BorderBottom = BorderStyle.Thin;
#endregion
#region table header cell style
IFont tf = wb.CreateFont();
tf.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle ts = wb.CreateCellStyle();
ts.CloneStyleFrom(bs);
ts.Alignment = HorizontalAlignment.Center;
ts.FillPattern = FillPattern.SolidForeground;
ts.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
ts.SetFont(tf);
#endregion
#region cell style
ICellStyle cs = wb.CreateCellStyle();
cs.CloneStyleFrom(bs);
cs.WrapText = true;
#endregion
#region date cell style
ICellStyle ds1 = wb.CreateCellStyle();
ds1.DataFormat = wb.CreateDataFormat().GetFormat("d.mm.yyyy hh:mm");
ICellStyle ds2 = wb.CreateCellStyle();
ds2.CloneStyleFrom(cs);
ds2.DataFormat = (short)BuiltinFormats.GetBuiltinFormat("m/d/yy");
#endregion
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellStyle = hs;
cell.SetCellValue(qname);
row = sheet.CreateRow(2);
row.CreateCell(0).SetCellValue("Подготовил:");
row.CreateCell(1).SetCellValue(person.Fio);
row = sheet.CreateRow(3);
row.CreateCell(0).SetCellValue("Дата:");
cell = row.CreateCell(1);
cell.CellStyle = ds1;
cell.SetCellValue(DateTime.Now);
string v = null;
foreach (UParam p in userParams)
{
v = !string.IsNullOrEmpty(p.Descr) ? p.Descr : p.Field;
row = sheet.CreateRow(rn++);
row.CreateCell(0).SetCellValue(v);
row.CreateCell(1).SetCellValue(p.Def);
}
// 1 пустой ряд
rn++;
DataManager dm = new DataManager();
dm.Person = person;
dynamic q;
List <dynamic> data = dm.GetQData(name, drv, sql, args, -1).ToList();
int total = data.Count();
if (total > 0)
{
int i, j,
sheets = total / XLSMAXROWS,
rem = total % XLSMAXROWS;
for (i = 0; i < sheets; ++i)
{
// заголовок
q = data[0];
row = sheet.CreateRow(rn++);
cn = 0;
foreach (string key in q.Keys)
{
sheet.SetColumnWidth(cn, 6144);
cell = row.CreateCell(cn++);
cell.CellStyle = ts;
cell.SetCellValue(key);
}
for (j = 0; j < XLSMAXROWS; ++j)
{
q = data[i * XLSMAXROWS + j];
row = sheet.CreateRow(rn++);
cn = 0;
foreach (object val in q.Values)
{
cell = row.CreateCell(cn++);
cell.CellStyle = cs;
if (val is DateTime)
{
cell.CellStyle = ds2;
cell.SetCellValue((DateTime)val);
}
else if (val is Double)
{
cell.SetCellValue((Double)val);
}
else if (val is Int16)
{
cell.SetCellValue((Int16)val);
}
else if (val is Int32)
{
cell.SetCellValue((Int32)val);
}
else if (val is Int64)
{
cell.SetCellValue((Int64)val);
}
else if (val is byte[])
{
cell.SetCellValue(Convert.ToBase64String((byte[])val));
}
else
{
cell.SetCellValue(val.ToString());
}
}
}
sheet = wb.CreateSheet("Лист" + sn++);
rn = 0;
}
// последние ряды, если есть
// заголовки
q = data[0];
row = sheet.CreateRow(rn++);
cn = 0;
foreach (string key in q.Keys)
{
sheet.SetColumnWidth(cn, 6144);
cell = row.CreateCell(cn++);
cell.CellStyle = ts;
cell.SetCellValue(key);
}
for (j = 0; j < rem; ++j)
{
q = data[i * XLSMAXROWS + j];
row = sheet.CreateRow(rn++);
cn = 0;
foreach (object val in q.Values)
{
cell = row.CreateCell(cn++);
cell.CellStyle = cs;
if (val is DateTime)
{
cell.CellStyle = ds2;
cell.SetCellValue((DateTime)val);
}
else if (val is Double)
{
cell.SetCellValue((Double)val);
}
else if (val is Int16)
{
cell.SetCellValue((Int16)val);
}
else if (val is Int32)
{
cell.SetCellValue((Int32)val);
}
else if (val is Int64)
{
cell.SetCellValue((Int64)val);
}
else if (val is byte[])
{
cell.SetCellValue(Convert.ToBase64String((byte[])val));
}
else
{
cell.SetCellValue(val.ToString());
}
}
}
}
string path = "export_" + person.Id + ".xls";
using (FileStream fs = new FileStream(repPath + path, FileMode.Create))
{
wb.Write(fs);
}
return(path);
}
public string GetFormat(short index)
{
return(this.stylesSource.GetNumberFormatAt((int)index) ?? BuiltinFormats.GetBuiltinFormat((int)index));
}
public void AssertNotBuiltInFormat(String customFmt)
{
//check it is not in built-in formats
Assert.AreEqual(-1, BuiltinFormats.GetBuiltinFormat(customFmt));
}
