NPOI 1.2.3实现Excel 2007(.xls)页眉/页脚：奇偶页不同、与页边距对齐
最近在做一个项目，需要将数据输出的Excel文件中。使用C#引用Excel COM对象创建Excel文件，速度很慢，所有选用的开源的NPOI创建Excel文件。NPOI是一个开源的在没有安装微软Office的情况下读写Office 97-2003的文件的.NET 2.0类库，最新版本是1.2.4 alpha，官方网站为：http://npoi.codeplex.com/（源码和更多例子请到这里下载）。NPOI具体用法请参考：http://www.cnblogs.com/atao/archive/2009/11/15/1603528.html。此项目选用的是NPOI 1.2.3正式版。
在NPOI 1.2.3中，通过HSSFHeader类设置页眉，HSSFFooter类设置页脚，可以设置三种页眉/页脚——左、中、右，HSSFHeader/HSSFFooter类不支持Excel2007中页眉/页脚奇偶页不同、首页不同。在Excel 2000-2003中，页眉页脚距纸张边界距离为1英寸，而我做的这个项目输出的Excel文件中右上角单元格为页码，且距纸张边界小于1英寸，这样生成的文档在打印时页码显示的位置就不正确了，需要用户在打印之前，现在Office 2007中将页面设置中的页眉/页脚选项卡中选中“与页边距对齐”选项，这样以来，给用户造成了一定的负担。
通过查阅微软发布的《MICROSOFT OFFICE EXCEL 97-2007 BINARY FILE FORMAT SPECIFICATION [*.xls (97-2007) format]》文档得知，在Excel 2007的xls格式中，新增了一个新的记录块叫HeaderFooterRecord（089Ch），用来专门设置Excel 2007的页眉/页脚：
The HEADERFOOTER record stores information added in Office Excel 2007 for headers/footers.
Offset Name Size Contents 4 rt 2 Record type; this matches the BIFF rt in thefirst two bytes of the record; =089Ch 6 grbitFrt 2 FRT cell reference flag; =0 currently 8 (Reserved) 8 Currently not used, and set to 0 16 guidSview 16 If this header FRT belongs to a specific sheetview (sview), the sheet view‘s GUID will be saved here. 32 grbitFlags 2 see text 34 cchHeaderEven 2 length of even header text 36 cchFooterEven 2 length of even footer text 38 cchHeaderFirst 2 length of first page header text 40 cchFooterFirst 2 length of first page footer text 42 rgchHeaderEven var even header text if non-zero length var rgchFooterEven var even footer text if non-zero length var rgchHeaderFirst var first page header text if non-zero length var rgchFooterFirst var first page footer text if non-zero length The grbitFlags field contains the fields listed in the following table.
Bits Mask Name Contents 0 0001h fHFDiffOddEven =1 if different odd/even pages 1 0002h fHFDiffFirst =1 if different first page 2 0004h fHFScaleWithDoc =1 scales header/footer with document 3 0008h fHFAlignMargins =1 align header/footer with pagemargins 4..15 FFF0h (Reserved) Reserved; must be 0 (zero)
翻看NPOI 1.2.3源码，并没有找到HeaderFooterRecord对应的类，而在NPOI.HSSF.Record.UnknownRecord类中找到089C，这说明在NPOI 1.2.3中，并不支持HeaderFooterRecord。换用NPOI 1.2.4 alpha版本，在NPOI中找到HeaderFooterRecord类，但该类成员中并没有设页眉/页脚的成员。随决定修改NPOI 1.2.3源码，自己实现HeaderFooterRecord。
首先，在NPOI.HSSF.Record命名空间中新增HeaderFooterRecord类，实现HeaderFooterRecord二进制读取与输出。代码如下：
1: namespace NPOI.HSSF.Record
2: {
3:
4: using System;
5: using System.Text;
6: using System.Collections;
7:
8: using NPOI.Util;
9: using NPOI.HSSF.Model;
10: using NPOI.HSSF.Record;
11: using NPOI.HSSF.Util;
12: using NPOI.HSSF.UserModel;
13: using NPOI.HSSF.Record.Formula;
14: using SSFormula = NPOI.SS.Formula;
15:
16: /**
17: * Title: Header Footer Record
18: * Description: This record specifies the even page header and footer text, and the first page header and footer text of the current sheet.
19: * REFERENCE:
20: * @author Junjie Wang
21: * @version 1.0-pre
22: */
23: public class HeaderFooterRecord : StandardRecord
24: {
25: private enum Option : short
26: {
27: OPT_DIFF_ODD_EVEN = 0x0001,
28: OPT_DIFF_FIRST = 0x0002,
29: OPT_SCALE_WITH_DOC = 0x0004,
30: OPT_ALIGN_MARGINS = 0x0008,
31: }
32:
33:
34: /**
35: */
36: public const short sid = 0x89C;
37:
38:
39: private short field_1_option_flag;
40: private GUID field_2_sview_guid;
41: //private short field_3_Length_header_even_text;
42: //private short field_4_Length_footer_even_text;
43: //private short field_5_Length_header_first_text;
44: //private short field_6_Length_footer_first_text;
45: private string field_7_header_even_text;
46: private string field_8_footer_even_text;
47: private string field_9_header_first_text;
48: private string field_10_footer_first_text;
49:
50:
51: /** Creates new NameRecord */
52: public HeaderFooterRecord()
53: {
54: this.field_1_option_flag = (short)((short)Option.OPT_ALIGN_MARGINS | (short)Option.OPT_SCALE_WITH_DOC);
55: this.field_2_sview_guid = new GUID(0, 0, 0, 0);
56: this.field_7_header_even_text = "";
57: this.field_8_footer_even_text = "";
58: this.field_9_header_first_text = "";
59: this.field_10_footer_first_text = "";
60: }
61: protected override int DataSize
62: {
63: get
64: {
65: return 38
66: + (field_7_header_even_text.Length > 0 ? field_7_header_even_text.Length + 3 : 0)
67: + (field_8_footer_even_text.Length > 0 ? field_8_footer_even_text.Length + 3 : 0)
68: + (field_9_header_first_text.Length > 0 ? field_9_header_first_text.Length + 3 : 0)
69: + (field_10_footer_first_text.Length > 0 ? field_10_footer_first_text.Length + 3 : 0);
70: }
71: }
72: /**
73: * Constructs a Name record and Sets its fields appropriately.
74: *
75: * @param in the RecordInputstream to Read the record from
76: */
77: public HeaderFooterRecord(RecordInputStream in1)
78: {
79: in1.ReadShort();
80: in1.ReadShort();
81: in1.ReadLong();
82: this.field_2_sview_guid = new GUID(in1);
83: this.field_1_option_flag = in1.ReadShort();
84:
85: int field_3_Length_header_even_text = in1.ReadShort();
86: int field_4_Length_footer_even_text = in1.ReadShort();
87: int field_5_Length_header_first_text = in1.ReadShort();
88: int field_6_Length_footer_first_text = in1.ReadShort();
89:
90: this.field_7_header_even_text = "";
91: this.field_8_footer_even_text = "";
92: this.field_9_header_first_text = "";
93: this.field_10_footer_first_text = "";
94: if (field_3_Length_header_even_text > 0)
95: {
96: this.field_7_header_even_text = StringUtil.ReadUnicodeString(in1);
97: }
98: if (field_4_Length_footer_even_text > 0)
99: {
100: this.field_8_footer_even_text = StringUtil.ReadUnicodeString(in1);
101: }
102: if (field_5_Length_header_first_text > 0)
103: {
104: this.field_9_header_first_text = StringUtil.ReadUnicodeString(in1);
105: }
106: if (field_6_Length_footer_first_text > 0)
107: {
108: this.field_10_footer_first_text = StringUtil.ReadUnicodeString(in1);
109: }
110:
111: }
112:
113:
114:
115:
116: /**
117: * Gets the specific sheet view‘s GUID
118: * @return the specific sheet view‘s GUID
119: * @see GUIDRecord
120: */
121: public GUID SViewGuid
122: {
123: get
124: {
125: return this.field_2_sview_guid;
126: }
127: set
128: {
129: field_2_sview_guid = value;
130: }
131: }
132:
133: /** Gets the option flag
134: * @return option flag
135: */
136: public short OptionFlag
137: {
138: get
139: {
140: return field_1_option_flag;
141: }
142: set
143: {
144: field_1_option_flag = value;
145: }
146: }
147:
148:
149: public bool IsDiffOddEven
150: {
151: get
152: {
153: return (this.field_1_option_flag & (short)Option.OPT_DIFF_ODD_EVEN) != 0;
154: }
155: set
156: {
157: field_1_option_flag = (short)(field_1_option_flag | (short)Option.OPT_DIFF_ODD_EVEN);
158: }
159: }
160:
161: public bool IsDiffFirst
162: {
163: get
164: {
165: return (this.field_1_option_flag & (short)Option.OPT_DIFF_FIRST) != 0;
166: }
167: set
168: {
169: field_1_option_flag = (short)(field_1_option_flag | (short)Option.OPT_DIFF_FIRST);
170: }
171: }
172:
173: public bool IsScaleWithDoc
174: {
175: get
176: {
177: return (this.field_1_option_flag & (short)Option.OPT_SCALE_WITH_DOC) != 0;
178: }
179: set
180: {
181: field_1_option_flag = (short)(field_1_option_flag | (short)Option.OPT_SCALE_WITH_DOC);
182: }
183: }
184:
185: public bool IsAlignMargins
186: {
187: get
188: {
189: return (this.field_1_option_flag & (short)Option.OPT_ALIGN_MARGINS) != 0;
190: }
191: set
192: {
193: field_1_option_flag = (short)(field_1_option_flag | (short)Option.OPT_ALIGN_MARGINS);
194: }
195: }
196:
197:
198: public int HeaderEvenTextLength
199: {
200: get
201: {
202: return this.field_7_header_even_text.Length;
203: }
204: }
205:
206: public int FooterEvenTextLength
207: {
208: get
209: {
210: return this.field_8_footer_even_text.Length;
211: }
212: }
213:
214: public int HeaderFirsTtextLength
215: {
216: get
217: {
218: return this.field_9_header_first_text.Length;
219: }
220: }
221:
222: public int FooterFirsTtextLength
223: {
224: get
225: {
226: return this.field_10_footer_first_text.Length;
227: }
228: }
229:
230: public string HeaderEvenText
231: {
232: get
233: {
234: return this.field_7_header_even_text;
235: }
236: set
237: {
238: if (this.IsDiffOddEven)
239: {
240: this.field_7_header_even_text = value;
241: }
242: else
243: {
244: this.field_7_header_even_text = "";
245: }
246: }
247: }
248:
249: public string FooterEvenText
250: {
251: get
252: {
253: return this.field_8_footer_even_text;
254: }
255: set
256: {
257: if (this.IsDiffOddEven)
258: {
259: this.field_8_footer_even_text = value;
260: }
261: else
262: {
263: this.field_8_footer_even_text = "";
264: }
265: }
266: }
267:
268: public string HeaderFirsText
269: {
270: get
271: {
272: return this.field_9_header_first_text;
273: }
274: set
275: {
276: if (this.IsDiffFirst)
277: {
278: this.field_9_header_first_text = value;
279: }
280: else
281: {
282: this.field_9_header_first_text = "";
283: }
284: }
285: }
286:
287: public string FooterFirsText
288: {
289: get
290: {
291: return this.field_10_footer_first_text;
292: }
293: set
294: {
295: if (this.IsDiffFirst)
296: {
297: this.field_10_footer_first_text = value;
298: }
299: else
300: {
301: this.field_10_footer_first_text = "";
302: }
303: }
304: }
305:
306:
307:
308: /**
309: * called by the class that Is responsible for writing this sucker.
310: * Subclasses should implement this so that their data Is passed back in a
311: * @param offset to begin writing at
312: * @param data byte array containing instance data
313: * @return number of bytes written
314: */
315: public override void Serialize(NPOI.Util.IO.LittleEndianOutput out1)
316: {
317: out1.WriteShort(this.Sid);
318: out1.WriteShort(0);
319: out1.WriteLong(0);
320: this.SViewGuid.Serialize(out1);
321: out1.WriteShort(OptionFlag);
322: out1.WriteShort((short)this.field_7_header_even_text.Length);
323: out1.WriteShort((short)this.field_8_footer_even_text.Length);
324: out1.WriteShort((short)this.field_9_header_first_text.Length);
325: out1.WriteShort((short)this.field_10_footer_first_text.Length);
326: if (this.field_7_header_even_text.Length > 0)
327: {
328: StringUtil.WriteUnicodeString(out1, this.field_7_header_even_text);
329: }
330: if (this.field_8_footer_even_text.Length > 0)
331: {
332: StringUtil.WriteUnicodeString(out1, this.field_8_footer_even_text);
333: }
334: if (this.field_9_header_first_text.Length > 0)
335: {
336: StringUtil.WriteUnicodeString(out1, this.field_9_header_first_text);
337: }
338: if (this.field_10_footer_first_text.Length > 0)
339: {
340: StringUtil.WriteUnicodeString(out1, this.field_10_footer_first_text);
341: }
342: }
343:
344:
345: /**
346: * return the non static version of the id for this record.
347: */
348: public override short Sid
349: {
350: get
351: {
352: return sid;
353: }
354: }
355:
356:
357: /**
358: * @see Object#ToString()
359: */
360: public override String ToString()
361: {
362: StringBuilder buffer = new StringBuilder();
363:
364: buffer.Append("[HeaderFooter]\n");
365: buffer.Append(" .option flags = ").Append(HexDump.ToHex(field_1_option_flag))
366: .Append("\n");
367: buffer.Append(" .is different odd/even pages = ").Append(this.IsDiffOddEven)
368: .Append("\n");
369: buffer.Append(" .is different first page = ").Append(this.IsDiffFirst)
370: .Append("\n");
371: buffer.Append(" .is scales header/footer with document = ").Append(this.IsScaleWithDoc)
372: .Append("\n");
373: buffer.Append(" .is align header/footer with page margins = ").Append(this.IsAlignMargins)
374: .Append("\n");
375: buffer.Append(" .even header text = ").Append(field_7_header_even_text)
376: .Append("\n");
377: buffer.Append(" .even footer text = ").Append(field_8_footer_even_text)
378: .Append("\n");
379: buffer.Append(" .first page header text = ").Append(field_9_header_first_text)
380: .Append("\n");
381: buffer.Append(" .first page footer text = ").Append(field_10_footer_first_text)
382: .Append("\n");
383: buffer.Append("[/HeaderFooter]\n");
384:
385: return buffer.ToString();
386: }
387:
388:
389: }
390: }
然后修改NPOI.HSSF.Record.Aggregates.PageSettingsBlock类，添加HeaderFooterRecord类实例，即在创建Sheet时，创建HeaderFooterRecord块，从文件中读取时，读取HeaderFooterRecord块。
1: namespace NPOI.HSSF.Record.Aggregates
2: {
3:
4: using System;
5: using System.Text;
6: using System.Collections;
7: using NPOI.HSSF.Model;
8: using NPOI.HSSF.Record;
9: using NPOI.HSSF.Util;
10: /**
11: * Groups the page settings records for a worksheet.<p/>
12: *
13: * See OOO excelfileformat.pdf sec 4.4 'Page Settings Block'
14: *
15: * @author Josh Micich
16: */
17: public class PageSettingsBlock : RecordAggregate
18: {
19: // Every one of these component records is optional
20: // (The whole PageSettingsBlock may not be present)
21: private PageBreakRecord _rowBreaksRecord;
22: private PageBreakRecord _columnBreaksRecord;
23: private HeaderRecord header;
24: private FooterRecord footer;
25: private HeaderFooterRecord _headerfooter;
26: private HCenterRecord _hCenter;
27: private VCenterRecord _vCenter;
28: private LeftMarginRecord _leftMargin;
29: private RightMarginRecord _rightMargin;
30: private TopMarginRecord _topMargin;
31: private BottomMarginRecord _bottomMargin;
32: private Record _pls;
33: private PrintSetupRecord printSetup;
34: private Record _bitmap;
35:
36: private ArrayList _rowRecords;
37:
38: public PageSettingsBlock(RecordStream rs)
39: {
40: _rowRecords = new ArrayList();
41: while (ReadARecord(rs)) ;
42: }
43:
44: /**
45: * Creates a PageSettingsBlock with default settings
46: */
47: public PageSettingsBlock()
48: {
49: _rowBreaksRecord = new HorizontalPageBreakRecord();
50: _columnBreaksRecord = new VerticalPageBreakRecord();
51: _rowRecords = new ArrayList();
52: header = new HeaderRecord(string.Empty);
53: footer = new FooterRecord(string.Empty);
54: _headerfooter= new HeaderFooterRecord();
55: _hCenter = CreateHCenter();
56: _vCenter = CreateVCenter();
57: printSetup = CreatePrintSetup();
58: }
59: public override void Dispose()
60: {
61: _rowBreaksRecord = null;
62: _columnBreaksRecord = null;
63: header = null;
64: footer = null;
65: _headerfooter = null;
66: _hCenter = null;
67: _vCenter = null;
68: _leftMargin = null;
69: _rightMargin = null;
70: _topMargin = null;
71: _bottomMargin = null;
72: _pls = null;
73: printSetup = null;
74: _bitmap = null;
75: }
76: /**
77: * @return <c>true</c> if the specified Record sid is one belonging to the
78: * 'Page Settings Block'.
79: */
80: public static bool IsComponentRecord(int sid)
81: {
82: switch (sid)
83: {
84: case HorizontalPageBreakRecord.sid:
85: case VerticalPageBreakRecord.sid:
86: case HeaderRecord.sid:
87: case FooterRecord.sid:
88: case HeaderFooterRecord.sid:
89: case HCenterRecord.sid:
90: case VCenterRecord.sid:
91: case LeftMarginRecord.sid:
92: case RightMarginRecord.sid:
93: case TopMarginRecord.sid:
94: case BottomMarginRecord.sid:
95: case UnknownRecord.PLS_004D:
96: case PrintSetupRecord.sid:
97: case UnknownRecord.BITMAP_00E9:
98: return true;
99: }
100: return false;
101: }
102:
103: private bool ReadARecord(RecordStream rs)
104: {
105: switch (rs.PeekNextSid())
106: {
107: case HorizontalPageBreakRecord.sid:
108: _rowBreaksRecord = (PageBreakRecord)rs.GetNext();
109: _rowRecords.Add(_rowBreaksRecord);
110: break;
111: case VerticalPageBreakRecord.sid:
112: _columnBreaksRecord = (PageBreakRecord)rs.GetNext();
113: _rowRecords.Add(_columnBreaksRecord);
114: break;
115: case HeaderRecord.sid:
116: header = (HeaderRecord)rs.GetNext();
117: _rowRecords.Add(header);
118: break;
119: case FooterRecord.sid:
120: footer = (FooterRecord)rs.GetNext();
121: _rowRecords.Add(footer);
122: break;
123: case HeaderFooterRecord.sid:
124: _headerfooter = (HeaderFooterRecord)rs.GetNext();
125: _rowRecords.Add(_headerfooter);
126: break;
127: case HCenterRecord.sid:
128: _hCenter = (HCenterRecord)rs.GetNext();
129: _rowRecords.Add(_hCenter);
130: break;
131: case VCenterRecord.sid:
132: _vCenter = (VCenterRecord)rs.GetNext();
133: _rowRecords.Add(_vCenter);
134: break;
135: case LeftMarginRecord.sid:
136: _leftMargin = (LeftMarginRecord)rs.GetNext();
137: _rowRecords.Add(_leftMargin);
138: break;
139: case RightMarginRecord.sid:
140: _rightMargin = (RightMarginRecord)rs.GetNext();
141: _rowRecords.Add(_rightMargin);
142: break;
143: case TopMarginRecord.sid:
144: _topMargin = (TopMarginRecord)rs.GetNext();
145: _rowRecords.Add(_topMargin);
146: break;
147: case BottomMarginRecord.sid:
148: _bottomMargin = (BottomMarginRecord)rs.GetNext();
149: _rowRecords.Add(_bottomMargin);
150: break;
151: case 0x004D: // PLS
152: _pls = rs.GetNext();
153: _rowRecords.Add(_pls);
154: break;
155: case PrintSetupRecord.sid:
156: printSetup = (PrintSetupRecord)rs.GetNext();
157: _rowRecords.Add(printSetup);
158: break;
159: case 0x00E9: // BITMAP
160: _bitmap = rs.GetNext();
161: _rowRecords.Add(_bitmap);
162: break;
163: default:
164: // all other record types are not part of the PageSettingsBlock
165: return false;
166: }
167: return true;
168: }
169:
170: private PageBreakRecord RowBreaksRecord
171: {
172: get
173: {
174: if (_rowBreaksRecord == null)
175: {
176: _rowBreaksRecord = new HorizontalPageBreakRecord();
177: }
178: return _rowBreaksRecord;
179: }
180: }
181:
182: private PageBreakRecord ColumnBreaksRecord
183: {
184: get
185: {
186: if (_columnBreaksRecord == null)
187: {
188: _columnBreaksRecord = new VerticalPageBreakRecord();
189: }
190: return _columnBreaksRecord;
191: }
192: }
193:
194: public IEnumerator GetEnumerator()
195: {
196: return _rowRecords.GetEnumerator();
197: }
198:
199: /**
200: * Sets a page break at the indicated column
201: *
202: */
203: public void SetColumnBreak(int column, int fromRow, int toRow)
204: {
205: this.ColumnBreaksRecord.AddBreak(column, fromRow, toRow);
206: }
207:
208: /**
209: * Removes a page break at the indicated column
210: *
211: */
212: public void RemoveColumnBreak(int column)
213: {
214: this.ColumnBreaksRecord.RemoveBreak(column);
215: }
216:
217: public override void VisitContainedRecords(RecordVisitor rv)
218: {
219: VisitIfPresent(_rowBreaksRecord, rv);
220: VisitIfPresent(_columnBreaksRecord, rv);
221: VisitIfPresent(header, rv);
222: VisitIfPresent(footer, rv);
223: VisitIfPresent(_headerfooter, rv);
224: VisitIfPresent(_hCenter, rv);
225: VisitIfPresent(_vCenter, rv);
226: VisitIfPresent(_leftMargin, rv);
227: VisitIfPresent(_rightMargin, rv);
228: VisitIfPresent(_topMargin, rv);
229: VisitIfPresent(_bottomMargin, rv);
230: VisitIfPresent(_pls, rv);
231: VisitIfPresent(printSetup, rv);
232: VisitIfPresent(_bitmap, rv);
233: }
234: private static void VisitIfPresent(Record r, RecordVisitor rv)
235: {
236: if (r != null)
237: {
238: rv.VisitRecord(r);
239: }
240: }
241:
242: /**
243: * Creates the HCenter Record and sets it to false (don't horizontally center)
244: */
245: private static HCenterRecord CreateHCenter()
246: {
247: HCenterRecord retval = new HCenterRecord();
248:
249: retval.HCenter = (false);
250: return retval;
251: }
252:
253: /**
254: * Creates the VCenter Record and sets it to false (don't horizontally center)
255: */
256: private static VCenterRecord CreateVCenter()
257: {
258: VCenterRecord retval = new VCenterRecord();
259:
260: retval.VCenter = (false);
261: return retval;
262: }
263:
264: /**
265: * Creates the PrintSetup Record and sets it to defaults and marks it invalid
266: * @see org.apache.poi.hssf.record.PrintSetupRecord
267: * @see org.apache.poi.hssf.record.Record
268: * @return record containing a PrintSetupRecord
269: */
270: private static PrintSetupRecord CreatePrintSetup()
271: {
272: PrintSetupRecord retval = new PrintSetupRecord();
273:
274: retval.PaperSize = ((short)1);
275: retval.Scale = ((short)100);
276: retval.PageStart = ((short)1);
277: retval.FitWidth = ((short)1);
278: retval.FitHeight = ((short)1);
279: retval.Options = ((short)2);
280: retval.HResolution = ((short)300);
281: retval.VResolution = ((short)300);
282: retval.HeaderMargin = (0.5);
283: retval.FooterMargin = (0.5);
284: retval.Copies = ((short)0);
285: return retval;
286: }
287:
288:
289: /**
290: * Returns the HeaderRecord.
291: * @return HeaderRecord for the sheet.
292: */
293: public HeaderRecord Header
294: {
295: get
296: {
297: return header;
298: }
299: set
300: {
301: header = value;
302: }
303: }
304:
305: /**
306: * Returns the FooterRecord.
307: * @return FooterRecord for the sheet.
308: */
309: public FooterRecord Footer
310: {
311: get
312: {
313: return footer;
314: }
315: set { footer = value; }
316: }
317:
318:
319: /**
320: * Returns the HeaderFooterRecord.
321: * @return HeaderFooterRecord for the sheet.
322: */
323: public HeaderFooterRecord HeaderFooter
324: {
325: get
326: {
327: return _headerfooter;
328: }
329: set { _headerfooter = value; }
330: }
331:
332: /**
333: * Returns the PrintSetupRecord.
334: * @return PrintSetupRecord for the sheet.
335: */
336: public PrintSetupRecord PrintSetup
337: {
338: get
339: {
340: return printSetup;
341: }
342: set
343: {
344: printSetup = value;
345: }
346: }
347:
348:
349: private Margin GetMarginRec(NPOI.SS.UserModel.MarginType margin)
350: {
351: switch (margin)
352: {
353: case NPOI.SS.UserModel.MarginType.LeftMargin: return _leftMargin;
354: case NPOI.SS.UserModel.MarginType.RightMargin: return _rightMargin;
355: case NPOI.SS.UserModel.MarginType.TopMargin: return _topMargin;
356: case NPOI.SS.UserModel.MarginType.BottomMargin: return _bottomMargin;
357: default:
358: throw new InvalidOperationException("Unknown margin constant: " + (short)margin);
359: }
360: }
361:
362:
363: /**
364: * Gets the size of the margin in inches.
365: * @param margin which margin to Get
366: * @return the size of the margin
367: */
368: public double GetMargin(NPOI.SS.UserModel.MarginType margin)
369: {
370: Margin m = GetMarginRec(margin);
371: if (m != null)
372: {
373: return m.Margin;
374: }
375: else
376: {
377: switch (margin)
378: {
379: case NPOI.SS.UserModel.MarginType.LeftMargin:
380: return .75;
381: case NPOI.SS.UserModel.MarginType.RightMargin:
382: return .75;
383: case NPOI.SS.UserModel.MarginType.TopMargin:
384: return 1.0;
385: case NPOI.SS.UserModel.MarginType.BottomMargin:
386: return 1.0;
387: }
388: throw new InvalidOperationException("Unknown margin constant: " + margin);
389: }
390: }
391:
392: /**
393: * Sets the size of the margin in inches.
394: * @param margin which margin to Get
395: * @param size the size of the margin
396: */
397: public void SetMargin(NPOI.SS.UserModel.MarginType margin, double size)
398: {
399: Margin m = GetMarginRec(margin);
400: if (m == null)
401: {
402: switch (margin)
403: {
404: case NPOI.SS.UserModel.MarginType.LeftMargin:
405: _leftMargin = new LeftMarginRecord();
406: m = _leftMargin;
407: break;
408: case NPOI.SS.UserModel.MarginType.RightMargin:
409: _rightMargin = new RightMarginRecord();
410: m = _rightMargin;
411: break;
412: case NPOI.SS.UserModel.MarginType.TopMargin:
413: _topMargin = new TopMarginRecord();
414: m = _topMargin;
415: break;
416: case NPOI.SS.UserModel.MarginType.BottomMargin:
417: _bottomMargin = new BottomMarginRecord();
418: m = _bottomMargin;
419: break;
420: default:
421: throw new InvalidOperationException("Unknown margin constant: " + margin);
422: }
423: }
424: m.Margin= size;
425: }
426:
427: /**
428: * Shifts all the page breaks in the range "count" number of rows/columns
429: * @param breaks The page record to be shifted
430: * @param start Starting "main" value to shift breaks
431: * @param stop Ending "main" value to shift breaks
432: * @param count number of units (rows/columns) to shift by
433: */
434: private static void ShiftBreaks(PageBreakRecord breaks, int start, int stop, int count) {
435:
436: IEnumerator iterator = breaks.GetBreaksEnumerator();
437: IList shiftedBreak = new ArrayList();
438: while(iterator.MoveNext())
439: {
440: PageBreakRecord.Break breakItem = (PageBreakRecord.Break)iterator.Current;
441: int breakLocation = breakItem.main;
442: bool inStart = (breakLocation >= start);
443: bool inEnd = (breakLocation <= stop);
444: if(inStart && inEnd)
445: shiftedBreak.Add(breakItem);
446: }
447:
448: iterator = shiftedBreak.GetEnumerator();
449: while (iterator.MoveNext()) {
450: PageBreakRecord.Break breakItem = (PageBreakRecord.Break)iterator.Current;
451: breaks.RemoveBreak(breakItem.main);
452: breaks.AddBreak((short)(breakItem.main+count), breakItem.subFrom, breakItem.subTo);
453: }
454: }
455:
456:
457: /**
458: * Sets a page break at the indicated row
459: * @param row
460: */
461: public void SetRowBreak(int row, short fromCol, short toCol)
462: {
463: this.RowBreaksRecord.AddBreak((short)row, fromCol, toCol);
464: }
465:
466: /**
467: * Removes a page break at the indicated row
468: * @param row
469: */
470: public void RemoveRowBreak(int row)
471: {
472: if (this.RowBreaksRecord.GetBreaks().Length < 1)
473: throw new ArgumentException("Sheet does not define any row breaks");
474: this.RowBreaksRecord.RemoveBreak((short)row);
475: }
476:
477: /**
478: * Queries if the specified row has a page break
479: * @param row
480: * @return true if the specified row has a page break
481: */
482: public bool IsRowBroken(int row)
483: {
484: return this.RowBreaksRecord.GetBreak(row) != null;
485: }
486:
487:
488: /**
489: * Queries if the specified column has a page break
490: *
491: * @return <c>true</c> if the specified column has a page break
492: */
493: public bool IsColumnBroken(int column)
494: {
495: return this.ColumnBreaksRecord.GetBreak(column) != null;
496: }
497:
498: /**
499: * Shifts the horizontal page breaks for the indicated count
500: * @param startingRow
501: * @param endingRow
502: * @param count
503: */
504: public void ShiftRowBreaks(int startingRow, int endingRow, int count)
505: {
506: ShiftBreaks(this.RowBreaksRecord, startingRow, endingRow, count);
507: }
508:
509: /**
510: * Shifts the vertical page breaks for the indicated count
511: * @param startingCol
512: * @param endingCol
513: * @param count
514: */
515: public void ShiftColumnBreaks(short startingCol, short endingCol, short count)
516: {
517: ShiftBreaks(this.ColumnBreaksRecord, startingCol, endingCol, count);
518: }
519:
520: /**
521: * @return all the horizontal page breaks, never <c>null</c>
522: */
523: public int[] RowBreaks
524: {
525: get
526: {
527: return this.RowBreaksRecord.GetBreaks();
528: }
529: }
530:
531: /**
532: * @return the number of row page breaks
533: */
534: public int NumRowBreaks
535: {
536: get
537: {
538: return this.RowBreaksRecord.NumBreaks;
539: }
540: }
541:
542: /**
543: * @return all the column page breaks, never <c>null</c>
544: */
545: public int[] ColumnBreaks
546: {
547: get
548: {
549: return this.ColumnBreaksRecord.GetBreaks();
550: }
551: }
552:
553: /**
554: * @return the number of column page breaks
555: */
556: public int NumColumnBreaks
557: {
558: get
559: {
560: return this.ColumnBreaksRecord.NumBreaks;
561: }
562: }
563:
564: public VCenterRecord VCenter
565: {
566: get { return _vCenter; }
567: }
568:
569: public HCenterRecord HCenter
570: {
571: get { return _hCenter; }
572: }
573: }
574: }
在NPOI.HSSF.Record.UnknownRecord中，将089Ch相关的变量注释掉，这样在读取Excel文件中就可以读取HeaderFooterRecord信息了。
推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架
"