private ISheet CreateSheet(int year)
{
ISheet sheet = wb.CreateSheet(year.ToString());
sheet.DisplayGridlines = false;
sheet.IsPrintGridlines = false;
IPrintSetup printSetup = sheet.PrintSetup;
printSetup.Landscape = true;
sheet.FitToPage = true;
sheet.HorizontallyCenter = true;
sheet.SetColumnWidth(0, 20 * 256);
return(sheet);
}
public void TestPrintSetup_bug46548()
{
// PageSettingBlock in this file Contains PLS (sid=x004D) record
// followed by ContinueRecord (sid=x003C)
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ex46548-23133.xls");
ISheet sheet = wb.GetSheetAt(0);
IPrintSetup ps = sheet.PrintSetup;
try
{
int copies = ps.Copies;
}
catch (NullReferenceException)
{
Assert.Fail("Identified bug 46548: PageSettingBlock missing PrintSetupRecord record");
}
}
static void Main(string[] args)
{
DateTime dt = DateTime.Now;
bool xlsx = false;
for (int i = 0; i < args.Length; i++)
{
if (args[i][0] == '-')
{
xlsx = args[i].Equals("-xlsx");
}
else
{
dt = new DateTime(dt.Year, int.Parse(args[i]), dt.Day);
}
}
int year = dt.Year;
IWorkbook wb = xlsx ? new XSSFWorkbook() as IWorkbook : new HSSFWorkbook() as IWorkbook;
Dictionary <String, ICellStyle> styles = CreateStyles(wb);
DateTime dtM;
for (int month = 0; month < 12; month++)
{
dtM = new DateTime(dt.Year, month + 1, 1);
//calendar.set(Calendar.MONTH, month);
//calendar.set(Calendar.DAY_OF_MONTH, 1);
//create a sheet for each month
ISheet sheet = wb.CreateSheet(months[month]);
//turn off gridlines
sheet.DisplayGridlines = (false);
sheet.IsPrintGridlines = (false);
sheet.FitToPage = (true);
sheet.HorizontallyCenter = (true);
IPrintSetup printSetup = sheet.PrintSetup;
printSetup.Landscape = (true);
//the following three statements are required only for HSSF
sheet.Autobreaks = (true);
printSetup.FitHeight = ((short)1);
printSetup.FitWidth = ((short)1);
//the header row: centered text in 48pt font
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = (80);
ICell titleCell = headerRow.CreateCell(0);
titleCell.SetCellValue(months[month] + " " + year);
titleCell.CellStyle = (styles[("title")]);
sheet.AddMergedRegion(CellRangeAddress.ValueOf("$A$1:$N$1"));
//header with month titles
IRow monthRow = sheet.CreateRow(1);
for (int i = 0; i < days.Length; i++)
{
//set column widths, the width is measured in units of 1/256th of a character width
sheet.SetColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide
sheet.SetColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide
sheet.AddMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
ICell monthCell = monthRow.CreateCell(i * 2);
monthCell.SetCellValue(days[i]);
monthCell.CellStyle = (styles["month"]);
}
int cnt = 1, day = 1;
int rownum = 2;
for (int j = 0; j < 6; j++)
{
IRow row = sheet.CreateRow(rownum++);
row.HeightInPoints = (100);
for (int i = 0; i < days.Length; i++)
{
ICell dayCell_1 = row.CreateCell(i * 2);
ICell dayCell_2 = row.CreateCell(i * 2 + 1);
int day_of_week = (int)dtM.DayOfWeek;
if (cnt >= day_of_week + 1 && dtM.Month == (month + 1))
{
dayCell_1.SetCellValue(day++);
if (i == 0 || i == days.Length - 1)
{
dayCell_1.CellStyle = styles["weekend_left"];
dayCell_2.CellStyle = styles["weekend_right"];
}
else
{
dayCell_1.CellStyle = styles["workday_left"];
dayCell_2.CellStyle = styles["workday_right"];
}
dtM = dtM.AddDays(1);
}
else
{
dayCell_1.CellStyle = styles["grey_left"];
dayCell_2.CellStyle = styles["grey_right"];
}
cnt++;
}
if (dtM.Month > (month + 1))
{
break;
}
}
}
// Write the output to a file
String file = "calendar.xls";
if (wb is XSSFWorkbook)
{
file += "x";
}
using (FileStream fs = new FileStream(file, FileMode.Create))
{
wb.Write(fs);
}
}
static void Main(string[] args)
{
InitializeWorkbook(args);
Dictionary <String, ICellStyle> styles = CreateStyles(workbook);
ISheet sheet = workbook.CreateSheet("Timesheet");
IPrintSetup printSetup = sheet.PrintSetup;
printSetup.Landscape = true;
sheet.FitToPage = (true);
sheet.HorizontallyCenter = (true);
//title row
IRow titleRow = sheet.CreateRow(0);
titleRow.HeightInPoints = (45);
ICell titleCell = titleRow.CreateCell(0);
titleCell.SetCellValue("Weekly Timesheet");
titleCell.CellStyle = (styles["title"]);
sheet.AddMergedRegion(CellRangeAddress.ValueOf("$A$1:$L$1"));
//header row
IRow headerRow = sheet.CreateRow(1);
headerRow.HeightInPoints = (40);
ICell headerCell;
for (int i = 0; i < titles.Length; i++)
{
headerCell = headerRow.CreateCell(i);
headerCell.SetCellValue(titles[i]);
headerCell.CellStyle = (styles["header"]);
}
int rownum = 2;
for (int i = 0; i < 10; i++)
{
IRow row = sheet.CreateRow(rownum++);
for (int j = 0; j < titles.Length; j++)
{
ICell cell = row.CreateCell(j);
if (j == 9)
{
//the 10th cell contains sum over week days, e.g. SUM(C3:I3)
String reference = "C" + rownum + ":I" + rownum;
cell.CellFormula = ("SUM(" + reference + ")");
cell.CellStyle = (styles["formula"]);
}
else if (j == 11)
{
cell.CellFormula = ("J" + rownum + "-K" + rownum);
cell.CellStyle = (styles["formula"]);
}
else
{
cell.CellStyle = (styles["cell"]);
}
}
}
//row with totals below
IRow sumRow = sheet.CreateRow(rownum++);
sumRow.HeightInPoints = (35);
ICell cell1 = sumRow.CreateCell(0);
cell1.CellStyle = (styles["formula"]);
ICell cell2 = sumRow.CreateCell(1);
cell2.SetCellValue("Total Hrs:");
cell2.CellStyle = (styles["formula"]);
for (int j = 2; j < 12; j++)
{
ICell cell = sumRow.CreateCell(j);
String reference = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
cell.CellFormula = ("SUM(" + reference + ")");
if (j >= 9)
{
cell.CellStyle = (styles["formula_2"]);
}
else
{
cell.CellStyle = (styles["formula"]);
}
}
rownum++;
sumRow = sheet.CreateRow(rownum++);
sumRow.HeightInPoints = 25;
ICell cell3 = sumRow.CreateCell(0);
cell3.SetCellValue("Total Regular Hours");
cell3.CellStyle = styles["formula"];
cell3 = sumRow.CreateCell(1);
cell3.CellFormula = ("L13");
cell3.CellStyle = styles["formula_2"];
sumRow = sheet.CreateRow(rownum++);
sumRow.HeightInPoints = (25);
cell3 = sumRow.CreateCell(0);
cell3.SetCellValue("Total Overtime Hours");
cell3.CellStyle = styles["formula"];
cell3 = sumRow.CreateCell(1);
cell3.CellFormula = ("K13");
cell3.CellStyle = styles["formula_2"];
//set sample data
for (int i = 0; i < sample_data.GetLength(0); i++)
{
IRow row = sheet.GetRow(2 + i);
for (int j = 0; j < sample_data.GetLength(1); j++)
{
if (sample_data[i, j] == null)
{
continue;
}
if (sample_data[i, j] is String)
{
row.GetCell(j).SetCellValue((String)sample_data[i, j]);
}
else
{
row.GetCell(j).SetCellValue((Double)sample_data[i, j]);
}
}
}
//finally set column widths, the width is measured in units of 1/256th of a character width
sheet.SetColumnWidth(0, 30 * 256); //30 characters wide
for (int i = 2; i < 9; i++)
{
sheet.SetColumnWidth(i, 6 * 256); //6 characters wide
}
sheet.SetColumnWidth(10, 10 * 256); //10 characters wide
WriteToFile();
}
static void Main(string[] args)
{
InitializeWorkbook(args);
Dictionary <String, ICellStyle> styles = CreateStyles(workbook);
ISheet sheet = workbook.CreateSheet("Loan Calculator");
sheet.IsPrintGridlines = (false);
sheet.DisplayGridlines = (false);
IPrintSetup printSetup = sheet.PrintSetup;
printSetup.Landscape = (true);
sheet.FitToPage = (true);
sheet.HorizontallyCenter = (true);
sheet.SetColumnWidth(0, 3 * 256);
sheet.SetColumnWidth(1, 3 * 256);
sheet.SetColumnWidth(2, 11 * 256);
sheet.SetColumnWidth(3, 14 * 256);
sheet.SetColumnWidth(4, 14 * 256);
sheet.SetColumnWidth(5, 14 * 256);
sheet.SetColumnWidth(6, 14 * 256);
CreateNames(workbook);
IRow titleRow = sheet.CreateRow(0);
titleRow.HeightInPoints = (35);
for (int i = 1; i <= 7; i++)
{
titleRow.CreateCell(i).CellStyle = styles["title"];
}
ICell titleCell = titleRow.GetCell(2);
titleCell.SetCellValue("Simple Loan Calculator");
sheet.AddMergedRegion(CellRangeAddress.ValueOf("$C$1:$H$1"));
IRow row = sheet.CreateRow(2);
ICell cell = row.CreateCell(4);
cell.SetCellValue("Enter values");
cell.CellStyle = styles["item_right"];
row = sheet.CreateRow(3);
cell = row.CreateCell(2);
cell.SetCellValue("Loan amount");
cell.CellStyle = styles["item_left"];
cell = row.CreateCell(4);
cell.CellStyle = styles["input_$"];
cell.SetAsActiveCell();
row = sheet.CreateRow(4);
cell = row.CreateCell(2);
cell.SetCellValue("Annual interest rate");
cell.CellStyle = styles["item_left"];
cell = row.CreateCell(4);
cell.CellStyle = styles["input_%"];
row = sheet.CreateRow(5);
cell = row.CreateCell(2);
cell.SetCellValue("Loan period in years");
cell.CellStyle = styles["item_left"];
cell = row.CreateCell(4);
cell.CellStyle = styles["input_i"];
row = sheet.CreateRow(6);
cell = row.CreateCell(2);
cell.SetCellValue("Start date of loan");
cell.CellStyle = styles["item_left"];
cell = row.CreateCell(4);
cell.CellStyle = styles["input_d"];
row = sheet.CreateRow(8);
cell = row.CreateCell(2);
cell.SetCellValue("Monthly payment");
cell.CellStyle = styles["item_left"];
cell = row.CreateCell(4);
cell.CellFormula = ("IF(Values_Entered,Monthly_Payment,\"\")");
cell.CellStyle = styles["formula_$"];
row = sheet.CreateRow(9);
cell = row.CreateCell(2);
cell.SetCellValue("Number of payments");
cell.CellStyle = styles["item_left"];
cell = row.CreateCell(4);
cell.CellFormula = ("IF(Values_Entered,Loan_Years*12,\"\")");
cell.CellStyle = styles["formula_i"];
row = sheet.CreateRow(10);
cell = row.CreateCell(2);
cell.SetCellValue("Total interest");
cell.CellStyle = styles["item_left"];
cell = row.CreateCell(4);
cell.CellFormula = ("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
cell.CellStyle = styles["formula_$"];
row = sheet.CreateRow(11);
cell = row.CreateCell(2);
cell.SetCellValue("Total cost of loan");
cell.CellStyle = styles["item_left"];
cell = row.CreateCell(4);
cell.CellFormula = ("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
cell.CellStyle = styles["formula_$"];
WriteToFile();
}
/*{
* {"1.0", "Marketing Research Tactical Plan", "J. Dow", "70", "9-Jul", null,
* "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x"},
* null,
* {"1.1", "Scope Definition Phase", "J. Dow", "10", "9-Jul", null,
* "x", "x", null, null, null, null, null, null, null, null, null},
* {"1.1.1", "Define research objectives", "J. Dow", "3", "9-Jul", null,
* "x", null, null, null, null, null, null, null, null, null, null},
* {"1.1.2", "Define research requirements", "S. Jones", "7", "10-Jul", null,
* "x", "x", null, null, null, null, null, null, null, null, null},
* {"1.1.3", "Determine in-house resource or hire vendor", "J. Dow", "2", "15-Jul", null,
* "x", "x", null, null, null, null, null, null, null, null, null},
* null,
* {"1.2", "Vendor Selection Phase", "J. Dow", "19", "19-Jul", null,
* null, "x", "x", "x", "x", null, null, null, null, null, null},
* {"1.2.1", "Define vendor selection criteria", "J. Dow", "3", "19-Jul", null,
* null, "x", null, null, null, null, null, null, null, null, null},
* {"1.2.2", "Develop vendor selection questionnaire", "S. Jones, T. Wates", "2", "22-Jul", null,
* null, "x", "x", null, null, null, null, null, null, null, null},
* {"1.2.3", "Develop Statement of Work", "S. Jones", "4", "26-Jul", null,
* null, null, "x", "x", null, null, null, null, null, null, null},
* {"1.2.4", "Evaluate proposal", "J. Dow, S. Jones", "4", "2-Aug", null,
* null, null, null, "x", "x", null, null, null, null, null, null},
* {"1.2.5", "Select vendor", "J. Dow", "1", "6-Aug", null,
* null, null, null, null, "x", null, null, null, null, null, null},
* null,
* {"1.3", "Research Phase", "G. Lee", "47", "9-Aug", null,
* null, null, null, null, "x", "x", "x", "x", "x", "x", "x"},
* {"1.3.1", "Develop market research information needs questionnaire", "G. Lee", "2", "9-Aug", null,
* null, null, null, null, "x", null, null, null, null, null, null},
* {"1.3.2", "Interview marketing group for market research needs", "G. Lee", "2", "11-Aug", null,
* null, null, null, null, "x", "x", null, null, null, null, null},
* {"1.3.3", "Document information needs", "G. Lee, S. Jones", "1", "13-Aug", null,
* null, null, null, null, null, "x", null, null, null, null, null},
* };*/
static void Main(string[] args)
{
data[0] = new string[] { "1.0", "Marketing Research Tactical Plan", "J. Dow", "70", "9-Jul", null,
"x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x" };
data[1] = new string[] { null };
data[2] = new string[] { "1.1", "Scope Definition Phase", "J. Dow", "10", "9-Jul", null,
"x", "x", null, null, null, null, null, null, null, null, null };
data[3] = new string[] { "1.1.1", "Define research objectives", "J. Dow", "3", "9-Jul", null,
"x", null, null, null, null, null, null, null, null, null, null };
data[4] = new string[] { "1.1.2", "Define research requirements", "S. Jones", "7", "10-Jul", null,
"x", "x", null, null, null, null, null, null, null, null, null };
data[5] = new string[] { "1.1.3", "Determine in-house resource or hire vendor", "J. Dow", "2", "15-Jul", null,
"x", "x", null, null, null, null, null, null, null, null, null };
data[6] = new string[] { null };
data[7] = new string[] { "1.2", "Vendor Selection Phase", "J. Dow", "19", "19-Jul", null,
null, "x", "x", "x", "x", null, null, null, null, null, null };
data[8] = new string[] { "1.2.1", "Define vendor selection criteria", "J. Dow", "3", "19-Jul", null,
null, "x", null, null, null, null, null, null, null, null, null };
data[9] = new string[] { "1.2.2", "Develop vendor selection questionnaire", "S. Jones, T. Wates", "2", "22-Jul", null,
null, "x", "x", null, null, null, null, null, null, null, null };
data[10] = new string[] { "1.2.3", "Develop Statement of Work", "S. Jones", "4", "26-Jul", null,
null, null, "x", "x", null, null, null, null, null, null, null };
data[11] = new string[] { "1.2.4", "Evaluate proposal", "J. Dow, S. Jones", "4", "2-Aug", null,
null, null, null, "x", "x", null, null, null, null, null, null };
data[12] = new string[] { "1.2.5", "Select vendor", "J. Dow", "1", "6-Aug", null,
null, null, null, null, "x", null, null, null, null, null, null };
data[13] = new string[] { null };
data[14] = new string[] { "1.3", "Research Phase", "G. Lee", "47", "9-Aug", null,
null, null, null, null, "x", "x", "x", "x", "x", "x", "x" };
data[15] = new string[] { "1.3.1", "Develop market research information needs questionnaire", "G. Lee", "2", "9-Aug", null,
null, null, null, null, "x", null, null, null, null, null, null };
data[16] = new string[] { "1.3.2", "Interview marketing group for market research needs", "G. Lee", "2", "11-Aug", null,
null, null, null, null, "x", "x", null, null, null, null, null };
data[17] = new string[] { "1.3.3", "Document information needs", "G. Lee, S. Jones", "1", "13-Aug", null,
null, null, null, null, null, "x", null, null, null, null, null };
IWorkbook wb;
if (args.Length > 0 && args[0].Equals("-xls"))
{
wb = new HSSFWorkbook();
}
else
{
wb = new XSSFWorkbook();
}
Dictionary <String, ICellStyle> styles = createStyles(wb);
ISheet sheet = wb.CreateSheet("Business Plan");
//turn off gridlines
sheet.DisplayGridlines = (false);
sheet.IsPrintGridlines = (false);
sheet.FitToPage = (true);
sheet.HorizontallyCenter = (true);
IPrintSetup printSetup = sheet.PrintSetup;
printSetup.Landscape = (true);
//the following three statements are required only for HSSF
sheet.Autobreaks = (true);
printSetup.FitHeight = ((short)1);
printSetup.FitWidth = ((short)1);
//the header row: centered text in 48pt font
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = (12.75f);
for (int i = 0; i < titles.Length; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(titles[i]);
cell.CellStyle = (styles["header"]);
}
//columns for 11 weeks starting from 9-Jul
DateTime dt = new DateTime(DateTime.Now.Year, 6, 9);
for (int i = 0; i < 11; i++)
{
ICell cell = headerRow.CreateCell(titles.Length + i);
cell.SetCellValue(dt);
cell.CellStyle = (styles[("header_date")]);
//calendar.roll(Calendar.WEEK_OF_YEAR, true);
dt.AddDays(7);
}
//freeze the first row
sheet.CreateFreezePane(0, 1);
IRow row;
//ICell cell;
int rownum = 1;
for (int i = 0; i < data.Length; i++, rownum++)
{
row = sheet.CreateRow(rownum);
if (data[i] == null)
{
continue;
}
for (int j = 0; j < data[i].Length; j++)
{
ICell cell = row.CreateCell(j);
String styleName;
bool isHeader = i == 0 || data[i - 1] == null;
switch (j)
{
case 0:
if (isHeader)
{
styleName = "cell_b";
cell.SetCellValue(Double.Parse(data[i][j]));
}
else
{
styleName = "cell_normal";
cell.SetCellValue(data[i][j]);
}
break;
case 1:
if (isHeader)
{
styleName = i == 0 ? "cell_h" : "cell_bb";
}
else
{
styleName = "cell_indented";
}
cell.SetCellValue(data[i][j]);
break;
case 2:
styleName = isHeader ? "cell_b" : "cell_normal";
cell.SetCellValue(data[i][j]);
break;
case 3:
styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
cell.SetCellValue(int.Parse(data[i][j]));
break;
case 4:
{
//calendar.setTime(fmt.parse(data[i][j]));
//calendar.set(Calendar.YEAR, year);
DateTime dt2 = DateTime.Parse(DateTime.Now.Year.ToString() + "-" + data[i][j]);
cell.SetCellValue(dt2);
styleName = isHeader ? "cell_b_date" : "cell_normal_date";
break;
}
case 5:
{
int r = rownum + 1;
String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
cell.SetCellFormula(fmla);
styleName = isHeader ? "cell_bg" : "cell_g";
break;
}
default:
styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
break;
}
cell.CellStyle = (styles[(styleName)]);
}
}
//group rows for each phase, row numbers are 0-based
sheet.GroupRow(4, 6);
sheet.GroupRow(9, 13);
sheet.GroupRow(16, 18);
//set column widths, the width is measured in units of 1/256th of a character width
sheet.SetColumnWidth(0, 256 * 6);
sheet.SetColumnWidth(1, 256 * 33);
sheet.SetColumnWidth(2, 256 * 20);
sheet.SetZoom(75);
// Write the output to a file
String file = "businessplan.xls";
if (wb is XSSFWorkbook)
{
file += "x";
}
using (FileStream out1 = new FileStream(file, FileMode.Create))
{
wb.Write(out1);
out1.Close();
}
}
/// <summary>
/// 填充数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="records"></param>
/// <param name="root"></param>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="columns"></param>
internal void SetData <T>(IList <T> records, Root root, HSSFWorkbook workbook, ISheet sheet, List <PropertyInfo> columns)
{
IList <HSSFCellStyle> ics = new List <HSSFCellStyle>();
Int32 height = 20;
for (Int32 r = 0; r < records.Count; r++)
{
IRow row = null;
if (!sheet.IsNullOrEmpty())
{
row = sheet.CreateRow(root.head.rowspan.GetInt32() + r);
}
for (Int32 i = 0; i < columns.Count; i++)
{
if (columns[i].IsNullOrEmpty())
{
continue;
}
var value = columns[i].GetValue(records[r], null);
String drValue = String.Empty;
if (value != null)
{
drValue = value.ToString();
}
drValue = drValue.Replace("<br/>", "\n");
drValue = drValue.Replace("<br>", "\n");
if (!row.IsNullOrEmpty())
{
ICell newCell = row.CreateCell(i);
if (ics.Count < columns.Count)
{
ExportColumn ec = root.head.columns.SingleOrDefault(c => c.dataIndex == columns[i].Name);
SetHeadCellBold(ec);
// 设置行高
if (!ec.dheight.IsNullOrEmpty())
{
height = ec.dheight.GetInt32() == 0 ? 1 : ec.dheight.GetInt32();
}
// 获得样式
ICellStyle style = GetCellStyle(workbook, ExcelStyle.Default, ec);
ics.Add(style as HSSFCellStyle);
newCell.CellStyle = style;
//设置单元格的宽度
if (!root.head.defaultwidth.IsNullOrEmpty() && !ec.width.IsNullOrEmpty())
{
sheet.SetColumnWidth(i, ec.width * 40);
}
else
{
sheet.AutoSizeColumn(i); //每列宽度自适应
}
}
else if (ics.Count == columns.Count)
{
HSSFCellStyle style = ics[i];
newCell.CellStyle = style;
}
if (height > 20)
{
row.Height = (short)(height * 20);
}
newCell.SetCellValue(drValue);
}
}
}
// 设置方向
sheet.PrintSetup.Landscape = root.head.landscape;
// 上边距
sheet.SetMargin(MarginType.TopMargin, root.topMargin.IsNullOrEmpty() ? 1.05 : root.topMargin.GetDouble());
// 下边距
sheet.SetMargin(MarginType.BottomMargin, root.bottomMargin.IsNullOrEmpty() ? 0.7 : root.bottomMargin.GetDouble());
// 左边距
sheet.SetMargin(MarginType.LeftMargin, root.leftMargin.IsNullOrEmpty() ? 0.7 : root.leftMargin.GetDouble());
// 右边距
sheet.SetMargin(MarginType.RightMargin, root.rightMargin.IsNullOrEmpty() ? 0.7 : root.rightMargin.GetDouble());
//// 页眉边距
//sheet.SetMargin(MarginType.HeaderMargin, 1.3);
//// 页脚边距
//sheet.SetMargin(MarginType.FooterMargin, 1);
// 打印居中
sheet.HorizontallyCenter = true;
// 设置缩放比例
sheet.PrintSetup.Scale = 100;
sheet.PrintSetup.FitWidth = 100;
sheet.PrintSetup.FitHeight = 100;
sheet.SetZoom(1, 1);
// 设置纸张
sheet.PrintSetup.PaperSize = 9;
IPrintSetup ps = sheet.PrintSetup;
// 设置顶端标题行
Int32 endRow = root.head.rowspan.GetInt32() > 1 ? root.head.rowspan.GetInt32() - 1 : 0;
workbook.SetRepeatingRowsAndColumns(0, 0, 0, 0, endRow);
}
private static HSSFWorkbook GeneraeWorkBook(IMap map)
{
var hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
ISheet sheet = hssfworkbook.CreateSheet("Timesheet");
IPrintSetup printSetup = sheet.PrintSetup;
printSetup.Landscape = true;
sheet.FitToPage = (true);
sheet.HorizontallyCenter = (true);
//title row
IRow titleRow = sheet.CreateRow(0);
titleRow.HeightInPoints = (35);
titleRow.CreateCell(0, "#");
titleRow.CreateCell(1, "Адресс");
titleRow.CreateCell(2, "Площадь");
titleRow.CreateCell(3, "Цена");
titleRow.CreateCell(4, "Этаж");
titleRow.CreateCell(5, "Год");
titleRow.CreateCell(6, "Отображать");
titleRow.CreateCell(7, "B районе");
var flats = SearchContext.Current.Data.Flats;
for (int i = 0; i < flats.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = (30);
var f = flats[i];
row.CreateCell(0, f.Id);
row.CreateCell(1, f.Address);
row.CreateCell(2, f.Square);
row.CreateCell(3, f.Price);
row.CreateCell(4, f.Floor);
row.CreateCell(5, f.Year.ToString());
row.CreateCell(6, f.Visible.ToString());
if (!string.IsNullOrWhiteSpace(f.Lat) && !string.IsNullOrWhiteSpace(f.Lng))
{
row.CreateCell(7, map.IsInRegion(f.Lng, f.Lat).ToString());
}
}
return(hssfworkbook);
}
