//[Obsolete]
//public static void SetBorderBottom(NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet,
// HSSFWorkbook workbook)
//{
// SetBorderBottom(border, toCRA(region), sheet, workbook);
//}
/// <summary>
/// Sets the borderBottom attribute of the HSSFRegionUtil object
/// </summary>
/// <param name="border">The new border</param>
/// <param name="region">The region that should have the border</param>
/// <param name="sheet">The sheet that the region is on.</param>
/// <param name="workbook">The workbook that the region is on.</param>
public static void SetBorderBottom(NPOI.SS.UserModel.CellBorderType border, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int colStart = region.FirstColumn;
int colEnd = region.LastColumn;
int rowIndex = region.LastRow;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_BOTTOM, (int)border);
NPOI.SS.UserModel.Row row = HSSFCellUtil.GetRow(rowIndex, sheet);
for (int i = colStart; i <= colEnd; i++)
{
cps.SetProperty(row, i);
}
}
public CFHeaderRecord(CellRangeAddress[] regions, int nRules)
{
CellRangeAddress[] unmergedRanges = regions;
CellRangeAddress[] mergeCellRanges = CellRangeUtil.MergeCellRanges(unmergedRanges);
CellRanges= mergeCellRanges;
field_1_numcf = nRules;
}
public FeatRecord(RecordInputStream in1)
{
futureHeader = new FtrHeader(in1);
isf_sharedFeatureType = in1.ReadShort();
reserved1 = (byte)in1.ReadByte();
reserved2 = in1.ReadInt();
int cref = in1.ReadUShort();
cbFeatData = in1.ReadInt();
reserved3 = in1.ReadShort();
cellRefs = new CellRangeAddress[cref];
for (int i = 0; i < cellRefs.Length; i++)
{
cellRefs[i] = new CellRangeAddress(in1);
}
switch (isf_sharedFeatureType)
{
case FeatHdrRecord.SHAREDFEATURES_ISFPROTECTION:
sharedFeature = new FeatProtection(in1);
break;
case FeatHdrRecord.SHAREDFEATURES_ISFFEC2:
sharedFeature = new FeatFormulaErr2(in1);
break;
case FeatHdrRecord.SHAREDFEATURES_ISFFACTOID:
sharedFeature = new FeatSmartTag(in1);
break;
default:
System.Console.WriteLine("Unknown Shared Feature " + isf_sharedFeatureType + " found!");
break;
}
}
public CFHeaderRecord(RecordInputStream in1)
{
field_1_numcf = in1.ReadShort();
field_2_need_recalculation = in1.ReadShort();
field_3_enclosing_cell_range = new CellRangeAddress(in1);
field_4_cell_ranges = new CellRangeAddressList(in1);
}
/**
* Constructs a MergedCellsRecord and Sets its fields appropriately
* @param in the RecordInputstream to Read the record from
*/
public MergeCellsRecord(RecordInputStream in1)
{
int nRegions = in1.ReadUShort();
CellRangeAddress[] cras = new CellRangeAddress[nRegions];
for (int i = 0; i < nRegions; i++)
{
cras[i] = new CellRangeAddress(in1);
}
_numberOfRegions = nRegions;
_startIndex = 0;
_regions = cras;
}
/**
* Sets the left border for a region of cells by manipulating the cell style of the individual
* cells on the left
*
* @param border The new border
* @param region The region that should have the border
* @param workbook The workbook that the region is on.
* @param sheet The sheet that the region is on.
*/
public static void SetBorderLeft(int border, CellRangeAddress region, ISheet sheet,
IWorkbook workbook)
{
int rowStart = region.FirstRow;
int rowEnd = region.LastRow;
int column = region.FirstColumn;
CellPropertySetter cps = new CellPropertySetter(workbook, CellUtil.BORDER_LEFT, border);
for (int i = rowStart; i <= rowEnd; i++)
{
cps.SetProperty(CellUtil.GetRow(i, sheet), column);
}
}
/**
* Creates a map (i.e. two-dimensional array) filled with ranges. Allow fast
* retrieving {@link CellRangeAddress} of any cell, if cell is contained in
* range.
*
* @see #getMergedRange(CellRangeAddress[][], int, int)
*/
public static CellRangeAddress[][] BuildMergedRangesMap(HSSFSheet sheet)
{
CellRangeAddress[][] mergedRanges = new CellRangeAddress[1][];
for ( int m = 0; m < sheet.NumMergedRegions; m++ )
{
CellRangeAddress cellRangeAddress = sheet.GetMergedRegion( m );
int requiredHeight = cellRangeAddress.LastRow + 1;
if ( mergedRanges.Length < requiredHeight )
{
CellRangeAddress[][] newArray = new CellRangeAddress[requiredHeight][];
Array.Copy( mergedRanges, 0, newArray, 0, mergedRanges.Length );
mergedRanges = newArray;
}
for ( int r = cellRangeAddress.FirstRow; r <= cellRangeAddress.LastRow; r++ )
{
int requiredWidth = cellRangeAddress.LastColumn + 1;
CellRangeAddress[] rowMerged = mergedRanges[r];
if ( rowMerged == null )
{
rowMerged = new CellRangeAddress[requiredWidth];
mergedRanges[r] = rowMerged;
}
else
{
int rowMergedLength = rowMerged.Length;
if ( rowMergedLength < requiredWidth )
{
CellRangeAddress[] newRow = new CellRangeAddress[requiredWidth];
Array.Copy(rowMerged, 0, newRow, 0,rowMergedLength );
mergedRanges[r] = newRow;
rowMerged = newRow;
}
}
//Arrays.Fill( rowMerged, cellRangeAddress.FirstColumn, cellRangeAddress.LastColumn + 1, cellRangeAddress );
for (int i = cellRangeAddress.FirstColumn; i < cellRangeAddress.LastColumn + 1; i++)
{
rowMerged[i] = cellRangeAddress;
}
}
}
return mergedRanges;
}
void CreateSlowestTests(int rowNum, IEnumerable<SlowestTest> slowestTests)
{
rowNum = CreateSummaryHeading("20 Slowest Tests", rowNum);
IRow row = sheet.CreateRow(rowNum++);
row.CreateHeadings(ColAssembly, "Assembly", "Class", "Time (secs)", "Time (hh:mm:ss)", "Test Name");
var range = new CellRangeAddress(row.RowNum, row.RowNum, ColPercent, maxColumnUsed);
sheet.AddMergedRegion(range);
foreach (var t in slowestTests)
{
row = sheet.CreateRow(rowNum++);
row.SetCell(ColAssembly, t.AssemblyFileName);
row.SetCell(ColClass, t.ClassName);
row.SetCell(ColTime, t.DurationInSeconds).Format("0.00").ApplyStyle();
row.SetCell(ColTimeHuman, t.DurationHuman).Alignment(HorizontalAlignment.Right).ApplyStyle();
row.SetCell(ColPercent, t.TestName);
range = new CellRangeAddress(row.RowNum, row.RowNum, ColPercent, maxColumnUsed);
sheet.AddMergedRegion(range);
}
}
/// <summary>
/// Sets the top border of region.
/// </summary>
/// <param name="region">The region.</param>
/// <param name="borderType">Type of the border.</param>
/// <param name="color">The color.</param>
public void SetBorderTopOfRegion(CellRangeAddress region, NPOI.SS.UserModel.CellBorderType borderType, short color)
{
HSSFRegionUtil.SetTopBorderColor(color, region, this, _workbook);
HSSFRegionUtil.SetBorderTop(borderType, region, this, _workbook);
}
private void ExportDynamicReportHeader(
ref ExportExcelInfo exportExcelInfo,
GetDynamicReportCriteria criteria,
DynamicReportInfo dynamicReport)
{
NpoiExcelExtensions.SetHeaderColorIndex(exportExcelInfo);
ICellStyle headerStyle = NpoiExcelExtensions.GetHeaderStyle(
exportExcelInfo.ExcelFile,
new ExcelStyleParam());
var currentColumn = exportExcelInfo.StartColumn;
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 80 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 100 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 120 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 300 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 160 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 100 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 80 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 140 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 140 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 140 * NpoiExcelExtensions.FontWidth);
string reportTitle = string.Format(
Labels.DynamicReport_TitleWithNumberOfLog,
SettingService.GetPeriodTypeName(criteria.ReportType),
DateTimeUtilize.ChangeTimeZoneToClientDateTime(criteria.FromDate).ToShortFormatedDate(),
DateTimeUtilize.ChangeTimeZoneToClientDateTime(criteria.ToDate).ToShortFormatedDate(),
dynamicReport != null ? "[" + dynamicReport.TotalChildReports + "]" : string.Empty);
CellRangeAddress cellRangeAddress;
cellRangeAddress = new CellRangeAddress(
exportExcelInfo.CurrentRow,
exportExcelInfo.CurrentRow + 1,
exportExcelInfo.StartColumn,
exportExcelInfo.MaxColumnIndex);
NpoiExcelExtensions.RenderMergedCells(exportExcelInfo, cellRangeAddress, reportTitle, headerStyle);
exportExcelInfo.CurrentRow += 2;
NpoiExcelExtensions.RenderEmptyRow(exportExcelInfo, exportExcelInfo.CurrentRow);
exportExcelInfo.CurrentRow++;
IRow row = exportExcelInfo.Worksheet.CreateRow(exportExcelInfo.CurrentRow);
currentColumn = exportExcelInfo.StartColumn;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Symbol_No, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Running_No, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Feedback_Date, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(
row,
currentColumn,
string.Format("{0}/{1}", Labels.Main_Category, Labels.Sub_Category),
headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Subject, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Status, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Duration, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Username, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Handler, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Reviewer, headerStyle);
currentColumn++;
exportExcelInfo.CurrentRow++;
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="workbook">工作区域</param>
/// <param name="sheet">工作簿</param>
private void SetMergeCell(HSSFWorkbook workbook, ISheet sheet)
{
foreach (MergeCell itemCell in ListMergeCell)
{
Cellocation StartCellocation = itemCell.StartCellocation;
Cellocation EndCellocation = itemCell.EndCellocation;
if (HeaderText != null)
{
StartCellocation.X += 1;
EndCellocation.X += 1;
}
if (EndCellocation.X >= StartCellocation.X && EndCellocation.Y >= StartCellocation.Y)
{
string GroupName = "";
if (itemCell.IMergeType == MergeType.Custom)
{
GroupName = itemCell.Custom;
}
else if (itemCell.IMergeType == MergeType.GroupByText)
{
GroupName = ",";
for (int y1 = StartCellocation.Y; y1 <= EndCellocation.Y; y1++)
{
for (int x1 = StartCellocation.X; x1 <= EndCellocation.X; x1++)
{
string StringCellValue = sheet.GetRow(y1).GetCell(x1).StringCellValue;
if (!GroupName.Contains("," + StringCellValue + ","))
{
GroupName += StringCellValue + ",";
}
}
}
if (GroupName.Length != 1)
{
GroupName = GroupName.Substring(1, GroupName.Length - 1);
GroupName = GroupName.Substring(0, GroupName.Length - 1);
}
}
else if (itemCell.IMergeType == MergeType.MergeText)
{
for (int y1 = StartCellocation.Y; y1 <= EndCellocation.Y; y1++)
{
for (int x1 = StartCellocation.X; x1 <= EndCellocation.X; x1++)
{
string StringCellValue = sheet.GetRow(y1).GetCell(x1).StringCellValue;
if (!GroupName.Contains("," + StringCellValue + ","))
{
GroupName += StringCellValue + ",";
}
}
}
if (GroupName.Length != 0)
{
GroupName = GroupName.Substring(0, GroupName.Length - 1);
}
}
else if (itemCell.IMergeType == MergeType.TakeFirstText)
{
GroupName = sheet.GetRow(StartCellocation.X).GetCell(StartCellocation.Y).StringCellValue;
}
CellRangeAddress cellRangeAddress = new CellRangeAddress(StartCellocation.X, EndCellocation.X, StartCellocation.Y, EndCellocation.Y);
sheet.AddMergedRegion(cellRangeAddress);
ICell newCell = sheet.GetRow(StartCellocation.X).GetCell(StartCellocation.Y);
SetCellValue(newCell, "", GroupName, newCell.CellStyle, null);
}
}
}
public void TestBasic()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh = wb.CreateSheet();
ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;
Assert.AreEqual(0, sheetCF.NumConditionalFormattings);
try
{
Assert.IsNull(sheetCF.GetConditionalFormattingAt(0));
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("Specified CF index 0 is outside the allowable range"));
}
try
{
sheetCF.RemoveConditionalFormatting(0);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("Specified CF index 0 is outside the allowable range"));
}
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("1");
IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule("2");
IConditionalFormattingRule rule3 = sheetCF.CreateConditionalFormattingRule("3");
IConditionalFormattingRule rule4 = sheetCF.CreateConditionalFormattingRule("4");
try
{
sheetCF.AddConditionalFormatting(null, rule1);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("regions must not be null"));
}
try
{
sheetCF.AddConditionalFormatting(
new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") },
(IConditionalFormattingRule)null);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("cfRules must not be null"));
}
try
{
sheetCF.AddConditionalFormatting(
new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") },
new IConditionalFormattingRule[0]);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("cfRules must not be empty"));
}
try
{
sheetCF.AddConditionalFormatting(
new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") },
new IConditionalFormattingRule[] { rule1, rule2, rule3, rule4 });
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("Number of rules must not exceed 3"));
}
}
private static void CreadHeader(IWorkbook book, ref ISheet sheet, DataTable dt, List <NpoiHeadCfg> heads)
{
//创建 表格头部
if (heads != null && heads.Count > 0)
{
//使用自定义表头（可以支持多行表头）
IRow headRow = sheet.CreateRow(0); //创建空行
headRow.Height = (short)(heads[0].Height * 20); //设置行高
//遍历自定义列头
int maxHeadRowNum = 0; //多行最大行号
//
int newColIndex = 0;
//记录当前列最多变成几列
Dictionary <int, int[]> mgs = new Dictionary <int, int[]>();
//
for (int i = 0; i < heads.Count; i++)
{
if (heads[i].Childs.Count == 0)
{
#region 无子节点
ICell cell = headRow.CreateCell(newColIndex); //创建单元格
cell.SetCellValue(heads[i].FieldLable); //设置单元格内容
var style = GetCellStyle(book, heads[i]);
cell.CellStyle = style;
// 设置列宽
if (heads[i].Width > 0)
{
sheet.SetColumnWidth(cell.ColumnIndex, heads[i].Width * TotalExcelColumns);
}
else
{
sheet.SetColumnWidth(cell.ColumnIndex, 100 * TotalExcelColumns);
}
//
mgs.Add(i, new int[] { newColIndex, 1 });
newColIndex += 1;
#endregion
}
else
{
#region 多个子节点
int rowIndex = 0;
int outRowIndex = 0;
int old_colIndex = newColIndex;
int new_colIndex = CreateHeadCell(headRow, newColIndex, rowIndex, out outRowIndex, heads[i]); // 返回最大列数
//
for (int j = old_colIndex; j < new_colIndex; j++)
{
if (headRow.GetCell(j) == null)
{
ICell _cell = headRow.CreateCell(j); //创建单元格
_cell.SetCellValue(heads[i].FieldLable); //设置单元格内容
var style = GetCellStyle(book, heads[i]);
_cell.CellStyle = style;
}
}
mgs.Add(i, new int[] { old_colIndex, new_colIndex - old_colIndex });
//
//合并单元格
//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
CellRangeAddress region1 = new CellRangeAddress(headRow.RowNum, headRow.RowNum, (short)old_colIndex, (short)new_colIndex - 1);
headRow.Sheet.AddMergedRegion(region1);
//
newColIndex = new_colIndex;
//
if (outRowIndex > maxHeadRowNum)
{
maxHeadRowNum = outRowIndex;//更新多行最大行号
}
#endregion
}
}
var fullstyle = GetCellStyle(book, heads[0]);
//合并 列
#region 合并列
if (maxHeadRowNum > 0)
{
foreach (var mg in mgs)
{
var values = mg.Value;
int cIndex = values[0];
int cCount = values[1];
if (cCount == 1)
{
for (int j = headRow.RowNum; j <= maxHeadRowNum; j++)
{
ICell cell = sheet.GetRow(j).GetCell(cIndex);
if (cellIsNull(cell))
{
cell = sheet.GetRow(j).CreateCell(cIndex);
cell.CellStyle = fullstyle;
}
}
CellRangeAddress region1 = new CellRangeAddress(headRow.RowNum, maxHeadRowNum, (short)cIndex, (short)cIndex);
headRow.Sheet.AddMergedRegion(region1);
}
else
{
for (int j = maxHeadRowNum; j >= headRow.RowNum; j--)
{
IRow row = sheet.GetRow(j);
ICell cell = row.GetCell(cIndex);
if (cellIsNull(cell))
{
for (int y = 0; y < cCount; y++)
{
cell = row.GetCell(cIndex + y);
if (cellIsNull(cell))
{
cell = row.CreateCell(cIndex + y);
cell.CellStyle = fullstyle;
//向上行合并
CellRangeAddress region1 = new CellRangeAddress(j - 1, maxHeadRowNum, (short)(cIndex + y), (short)(cIndex + y));
headRow.Sheet.AddMergedRegion(region1);
}
}
}
else
{
for (int y = 0; y < cCount; y++)
{
cell = row.GetCell(cIndex + y);
if (cellIsNull(cell))
{
cell = row.CreateCell(cIndex + y);
cell.CellStyle = fullstyle;
//判断上一行是否空
for (int x = j - 1; x >= headRow.RowNum; x--)
{
IRow preRow = sheet.GetRow(x);
var precell = preRow.GetCell(cIndex + y);
if (cellIsNull(precell))
{
var newcell = preRow.CreateCell(cIndex + y);
newcell.CellStyle = fullstyle;
}
else
{
//向下行合并
CellRangeAddress region1 = new CellRangeAddress(x, maxHeadRowNum, (short)(cIndex + y), (short)(cIndex + y));
headRow.Sheet.AddMergedRegion(region1);
break;
}
}
}
}
break;
}
}
}
}
}
#endregion
}
else
{
//使用数据源列名作表头（只支持单行表头）
IRow headRow = sheet.CreateRow(0);//创建空行
var style = GetCellStyle(book, null);
//遍历列
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headRow.CreateCell(i);
cell.CellStyle = style;
sheet.SetColumnWidth(cell.ColumnIndex, 100 * TotalExcelColumns);
if (!string.IsNullOrEmpty(dt.Columns[i].Caption))
{
cell.SetCellValue(dt.Columns[i].Caption);
}
else
{
cell.SetCellValue(dt.Columns[i].ColumnName);
}
}
}
}
//[Obsolete]
//public static void SetRightBorderColor(short color, Region region, HSSFSheet sheet,
// HSSFWorkbook workbook)
//{
// SetRightBorderColor(color, toCRA(region), sheet, workbook);
//}
/// <summary>
/// Sets the rightBorderColor attribute of the HSSFRegionUtil object
/// </summary>
/// <param name="color">The color of the border</param>
/// <param name="region">The region that should have the border</param>
/// <param name="sheet">The workbook that the region is on.</param>
/// <param name="workbook">The sheet that the region is on.</param>
public static void SetRightBorderColor(int color, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int rowStart = region.FirstRow;
int rowEnd = region.LastRow;
int column = region.LastColumn;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.RIGHT_BORDER_COLOR, color);
for (int i = rowStart; i <= rowEnd; i++)
{
cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column);
}
}
public static void SetMergedCellStyle(XSSFWorkbook xssfworkbook, ISheet sheet, CellRangeAddress region, int fontSize)
{
ICellStyle style = xssfworkbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BottomBorderColor = HSSFColor.Black.Index;
style.LeftBorderColor = HSSFColor.Black.Index;
style.RightBorderColor = HSSFColor.Black.Index;
style.TopBorderColor = HSSFColor.Black.Index;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//设置表头字体
IFont font1 = xssfworkbook.CreateFont();
font1.Boldweight = 700;
font1.FontHeight = fontSize;
font1.FontName = "微软雅黑";
style.SetFont(font1);//将设置好的字体样式设置给单元格样式对象。
//cell.CellStyle = style;
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row = sheet.GetRow(i);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
}
}
/**
* Read hyperlink from input stream
*
* @param in the stream to Read from
*/
public HyperlinkRecord(RecordInputStream in1)
{
_range = new CellRangeAddress(in1);
// 16-byte GUID
_guid = new GUID(in1);
/*
* streamVersion (4 bytes): An unsigned integer that specifies the version number
* of the serialization implementation used to save this structure. This value MUST equal 2.
*/
int streamVersion = in1.ReadInt();
if (streamVersion != 0x00000002)
{
throw new RecordFormatException("Stream Version must be 0x2 but found " + streamVersion);
}
_linkOpts = in1.ReadInt();
if ((_linkOpts & HLINK_LABEL) != 0)
{
int label_len = in1.ReadInt();
_label = in1.ReadUnicodeLEString(label_len);
}
if ((_linkOpts & HLINK_TARGET_FRAME) != 0)
{
int len = in1.ReadInt();
_targetFrame = in1.ReadUnicodeLEString(len);
}
if ((_linkOpts & HLINK_URL) != 0 && (_linkOpts & HLINK_UNC_PATH) != 0)
{
_moniker = null;
int nChars = in1.ReadInt();
_address = in1.ReadUnicodeLEString(nChars);
}
if ((_linkOpts & HLINK_URL) != 0 && (_linkOpts & HLINK_UNC_PATH) == 0)
{
_moniker = new GUID(in1);
if (URL_MONIKER.Equals(_moniker))
{
int length = in1.ReadInt();
/*
* The value of <code>length<code> be either the byte size of the url field
* (including the terminating NULL character) or the byte size of the url field plus 24.
* If the value of this field is set to the byte size of the url field,
* then the tail bytes fields are not present.
*/
int remaining = in1.Remaining;
if (length == remaining)
{
int nChars = length / 2;
_address = in1.ReadUnicodeLEString(nChars);
}
else
{
int nChars = (length - TAIL_SIZE) / 2;
_address = in1.ReadUnicodeLEString(nChars);
/*
* TODO: make sense of the remaining bytes
* According to the spec they consist of:
* 1. 16-byte GUID: This field MUST equal
* {0xF4815879, 0x1D3B, 0x487F, 0xAF, 0x2C, 0x82, 0x5D, 0xC4, 0x85, 0x27, 0x63}
* 2. Serial version, this field MUST equal 0 if present.
* 3. URI Flags
*/
_uninterpretedTail = ReadTail(URL_uninterpretedTail, in1);
}
}
else if (FILE_MONIKER.Equals(_moniker))
{
_fileOpts = in1.ReadShort();
int len = in1.ReadInt();
_shortFilename = StringUtil.ReadCompressedUnicode(in1, len);
_uninterpretedTail = ReadTail(FILE_uninterpretedTail, in1);
int size = in1.ReadInt();
if (size > 0)
{
int charDataSize = in1.ReadInt();
//From the spec: An optional unsigned integer that MUST be 3 if present
// but some files has 4
int usKeyValue = in1.ReadUShort();
_address = StringUtil.ReadUnicodeLE(in1, charDataSize / 2);
}
else
{
_address = null;
}
}
else if (STD_MONIKER.Equals(_moniker))
{
_fileOpts = in1.ReadShort();
int len = in1.ReadInt();
byte[] path_bytes = new byte[len];
in1.ReadFully(path_bytes);
_address = Encoding.UTF8.GetString(path_bytes);
}
}
if ((_linkOpts & HLINK_PLACE) != 0)
{
int len = in1.ReadInt();
_textMark = in1.ReadUnicodeLEString(len);
}
if (in1.Remaining > 0)
{
Console.WriteLine(HexDump.ToHex(in1.ReadRemainder()));
}
}
public ActionResult ExportData(string queryJson)
{
try
{
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 1000000000;
pagination.p_kid = "u.USERID";
pagination.p_fields = @"
u.DEPARTMENTID,u.DEPARTMENTCODE, u.REALNAME,u.DUTYID,u.DUTYNAME,u.depttype,
case when u.nature = '班组' then u.parentname else u.DEPTNAME end FULLNAME,
COUNT(u.USERID) OVER(partition by case when u.nature = '班组' then u.parentname else u.DEPTNAME end) AS personcount,
case when(length(u.deptcode) > 20) then(select d.SORTCODE from base_department d where d.deptcode = substr(u.deptcode, 1, 20)) else u.DEPTSORT end as DEPTSORTss ";
pagination.p_tablename = @" V_USERINFO u
left JOIN BIS_HIKINOUTLOG bh on bh.USERID = u.USERID ";
pagination.conditionJson = @" u.DEPARTMENTID not in ('0') AND TO_CHAR(bh.CREATEDATE, 'yyyy-MM-dd') is NULL
AND u.USERID not in (SELECT USERID FROM HJB_PERSONSET WHERE MODULETYPE=3) " ;
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
DataTable exportTable = hikinoutlogbll.GetAbsenteeismPageList(pagination, queryJson);
//导出excel
string title = "连续缺勤统计";
HSSFWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
sheet.DefaultRowHeight = 30 * 20;
int column = exportTable.Columns.Count;
int indexRow = 0;
//标题
if (!string.IsNullOrEmpty(title))
{
IRow headerRow = sheet.CreateRow(indexRow);
headerRow.CreateCell(0).SetCellValue(title);
//合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
sheet.AddMergedRegion(region);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 22;
font.FontName = "宋体";
font.Boldweight = (short)FontBoldWeight.Bold;
cellstyle.SetFont(font);
var cell = sheet.GetRow(0).GetCell(0);
cell.CellStyle = cellstyle;
HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, sheet, workbook); //下边框
HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, sheet, workbook); //左边框
HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, sheet, workbook); //右边框
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, sheet, workbook); //上边框
indexRow++;
}
//列头样式
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont headerFont = workbook.CreateFont();
headerFont.FontHeightInPoints = 16;
headerFont.FontName = "宋体";
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(headerFont);
IRow row1 = sheet.CreateRow(indexRow);
row1.CreateCell(0).SetCellValue("单位名称");
row1.GetCell(0).CellStyle = headerStyle;
row1.CreateCell(1).SetCellValue("姓名");
row1.GetCell(1).CellStyle = headerStyle;
row1.CreateCell(2).SetCellValue("岗位名称");
row1.GetCell(2).CellStyle = headerStyle;
//普通单元格样式
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.Alignment = HorizontalAlignment.Center;
bodyStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.Black.Index;
font1.Boldweight = 25;
font1.FontHeightInPoints = 12;
bodyStyle.FillForegroundColor = HSSFColor.White.Index;
bodyStyle.SetFont(font1);
//设置格式
IDataFormat format = workbook.CreateDataFormat();
//填充数据
for (int i = 0; i < exportTable.Rows.Count; i++)
{
indexRow++;
IRow rowTemp = sheet.CreateRow(indexRow);
rowTemp.Height = 62 * 20;
rowTemp.CreateCell(0).SetCellValue(exportTable.Rows[i]["fullname"].ToString() + "(" + exportTable.Rows[i]["personcount"].ToString() + ")");
rowTemp.CreateCell(1).SetCellValue(exportTable.Rows[i]["realname"].ToString());
rowTemp.CreateCell(2).SetCellValue(exportTable.Rows[i]["dutyname"].ToString());
rowTemp.GetCell(0).CellStyle = bodyStyle;
rowTemp.GetCell(1).CellStyle = bodyStyle;
rowTemp.GetCell(2).CellStyle = bodyStyle;
}
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
//合并单元格
MergeCells(sheet, exportTable, 0, 0, 0);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", title + ".xls"));
}
catch (Exception ex)
{
}
return(Success("导出成功。"));
}
public CellWalk(ISheet sheet, CellRangeAddress range)
{
this.sheet = sheet;
this.range = range;
this.traverseEmptyCells = false;
}
private static bool IsNewMergedRegion(CellRangeAddress newMergedRegion,
List <CellRangeAddress> mergedRegions)
{
return(!mergedRegions.Contains(newMergedRegion));
}
public void SetBorder(CellRangeAddress region)
{
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
}
private static int CreateHeadCell(IRow preHeadRow, int startColIndex, int rowIndex, out int outRowIndex, NpoiHeadCfg headCfg)
{
// int colCount = headCfg.Childs.Count;
int preRowIndex = rowIndex;
rowIndex += 1;
outRowIndex = rowIndex;
var sheet = preHeadRow.Sheet;
IWorkbook book = sheet.Workbook;
var style = GetCellStyle(book, headCfg);
//
IRow curHeadRow = null;
if (sheet.LastRowNum >= rowIndex)
{
curHeadRow = sheet.GetRow(rowIndex);
}
else
{
curHeadRow = sheet.CreateRow(rowIndex);//创建空行
for (int i = 0; i < startColIndex; i++)
{
ICell cell = curHeadRow.CreateCell(i); //创建单元格
cell.CellStyle = style;
////
ICell precell = preHeadRow.GetCell(i); //获取单元格
if (precell != null)
{
cell.SetCellValue("");//设置单元格内容
//precell.SetCellValue("");
}
}
}
int newColIndex = startColIndex;
for (int i = 0; i < headCfg.Childs.Count; i++)
{
style = GetCellStyle(book, headCfg.Childs[i]);
short Height = (short)(headCfg.Childs[i].Height * 20);
if (curHeadRow.Height < Height)
{
curHeadRow.Height = Height;
}
if (headCfg.Childs[i].Childs.Count > 0)
{
//Console.Write("递归调用\r\n");
//
int _outRowIndex = 0;
int old_ColIndex = newColIndex;
//
int new_ColIndex = CreateHeadCell(curHeadRow, newColIndex, rowIndex, out _outRowIndex, headCfg.Childs[i]);//递归调用
//
for (int j = old_ColIndex; j < new_ColIndex; j++)
{
ICell _cell = curHeadRow.GetCell(j);
if (_cell == null)
{
_cell = curHeadRow.CreateCell(j); //创建单元格
}
// 设置列宽
_cell.SetCellValue(headCfg.Childs[i].FieldLable); //设置单元格内容
_cell.CellStyle = style;
if (headCfg.Childs[i].Width > 100)
{
sheet.SetColumnWidth(_cell.ColumnIndex, headCfg.Childs[i].Width * TotalExcelColumns);
}
else
{
sheet.SetColumnWidth(_cell.ColumnIndex, 100 * TotalExcelColumns);
}
}
//合并单元格
//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
CellRangeAddress region1 = new CellRangeAddress(curHeadRow.RowNum, curHeadRow.RowNum, (short)old_ColIndex, (short)(new_ColIndex - 1));
sheet.AddMergedRegion(region1);
//
if (_outRowIndex > outRowIndex)
{
outRowIndex = _outRowIndex;
}
newColIndex = new_ColIndex;
}
else
{
ICell _cell = curHeadRow.GetCell(newColIndex);
if (_cell == null)
{
_cell = curHeadRow.CreateCell(newColIndex); //创建单元格
}
_cell.SetCellValue(headCfg.Childs[i].FieldLable); //设置单元格内容
_cell.CellStyle = style;
// 设置列宽
if (headCfg.Width > 0)
{
sheet.SetColumnWidth(_cell.ColumnIndex, headCfg.Width * TotalExcelColumns);
}
else
{
sheet.SetColumnWidth(_cell.ColumnIndex, 100 * TotalExcelColumns);
}
//
newColIndex += 1;
}
}
//
return(newColIndex);
}
private object[] RowValues( int rowNum, int startCol, int numberOfCols )
{
if( mSheet == null )
return null;
if( numberOfCols == 1 ) {
IRow row = HSSFCellUtil.GetRow( rowNum, (HSSFSheet)mSheet );
ICell cell = HSSFCellUtil.GetCell( row, startCol );
return new object[] { NPOIUtils.GetCellValue( cell ) };
} else {
CellRangeAddress range = new CellRangeAddress( rowNum, rowNum, startCol, startCol + numberOfCols - 1 );
CellWalk cw = new CellWalk( mSheet, range );
cw.SetTraverseEmptyCells( true );
CellExtractor ce = new CellExtractor();
cw.Traverse( ce );
return ce.CellValues;
}
}
public ActionResult Export_ToExcel()
{
var getPersonCities = ItemManager.Instance.GetPersonCities();
var workbook = new HSSFWorkbook();
//Create new Excel Sheet
var sheet = workbook.CreateSheet();
HSSFFont myFont = (HSSFFont)workbook.CreateFont();
myFont.FontHeightInPoints = (short)12;
myFont.FontName = "Times New Roman";
HSSFCellStyle borderedCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
borderedCellStyle.SetFont(myFont);
borderedCellStyle.WrapText = true;
borderedCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
borderedCellStyle.FillPattern = FillPattern.SolidForeground;
borderedCellStyle.VerticalAlignment = VerticalAlignment.Center;
HSSFCellStyle borderedCellStyleContent = (HSSFCellStyle)workbook.CreateCellStyle();
borderedCellStyleContent.SetFont(myFont);
borderedCellStyleContent.WrapText = true;
//borderedCellStyleContent.Alignment = HorizontalAlignment.Center; //chieu ngang
borderedCellStyleContent.VerticalAlignment = VerticalAlignment.Center;
//(Optional) set the width of the columns
sheet.SetColumnWidth(0, 20 * 200);
sheet.SetColumnWidth(1, 20 * 200);
//sheet.SetColumnWidth(2, 20 * 200);
//sheet.SetDefaultColumnStyle(0, borderedCellStyle);
//sheet.SetDefaultColumnStyle(1, borderedCellStyle);
//sheet.SetDefaultColumnStyle(2, borderedCellStyle);
CreateCell(sheet.CreateRow(0), 0, "Column Value", borderedCellStyle);
IRow HeaderRow = sheet.CreateRow(0);
CreateCell(HeaderRow, 0, "CityName", borderedCellStyle);
CreateCell(HeaderRow, 1, "Gender", borderedCellStyle);
CreateCell(HeaderRow, 2, "Amount", borderedCellStyle);
//headerRow.CreateCell(0).SetCellValue("CityName");
//headerRow.CreateCell(1).SetCellValue("Gender");
//headerRow.CreateCell(2).SetCellValue("Amount");
//for (int c = 0; c < headerRow.Cells.Count; c++)
//{
// headerRow.Cells[c].CellStyle = borderedCellStyle;
//}
sheet.CreateFreezePane(0, 1, 0, 1);
var result = from cities in getPersonCities
group cities by cities.IdCity into c
select c.ToList();
int RowIndex = 1;
foreach (var objOrg in result)
{
int Number = objOrg.Count;
if (Number > 1)
{
int MergeIndex = (Number - 1) + RowIndex;
CellRangeAddress Merged = new CellRangeAddress(RowIndex, MergeIndex, 0, 0);
sheet.AddMergedRegion(Merged);
}
foreach (var objOrg1 in objOrg)
{
IRow row = sheet.CreateRow(RowIndex);
CreateCell(row, 0, objOrg1.CityName, borderedCellStyleContent);
CreateCell(row, 1, objOrg1.Gender, borderedCellStyleContent);
CreateCell(row, 2, objOrg1.Amount.ToString(), borderedCellStyleContent);
//row.CreateCell(0).SetCellValue(objOrg1.CityName);
//row.CreateCell(1).SetCellValue(objOrg1.Gender);
//row.CreateCell(2).SetCellValue(objOrg1.Amount);
RowIndex++;
//for (int c = 0; c < row.Cells.Count; c++)
//{
// row.Cells[c].CellStyle = borderedCellStyleContent;
//}
}
}
//Write the Workbook to a memory stream
MemoryStream output = new MemoryStream();
workbook.Write(output);
//Return the result to the end user
return(File(output.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"OrganizationList.xls"));
}
public static string ExportOrganization(string rootPath, ExportByOrganizationArgs args)
{
List <WeeklyReport> arg_28_0 = ExcelHelper._weeklyReportManager.GetWeeklyReportListByOrganization(args.Domain, args.Organization, args.Year, args.WeekOfYear);
List <WeeklyReport> list = new List <WeeklyReport>();
foreach (WeeklyReport current in arg_28_0)
{
if (args.UserList.Contains(current.User))
{
list.Add(current);
}
}
if (list.Count == 0)
{
return(null);
}
IWorkbook workbook = null;
FileStream fileStream = null;
string text = Path.Combine(rootPath, ExcelHelper._templateFile);
FileStream fileStream2 = new FileStream(text, FileMode.Open, FileAccess.Read, FileShare.Read);
if (text.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fileStream2);
}
else if (text.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fileStream2);
}
string text3;
try
{
var sheet = workbook.GetSheetAt(0);
string text2 = list[0].Monday.ToString("yyyyMMdd") + "-";
text2 += list[list.Count - 1].Sunday.ToString("yyyyMMdd");
text2 += "-";
Organization organization = ExcelHelper._domainManager.GetOrganization(args.Organization);
text2 += organization.Name;
text2 += "-周报";
sheet.GetRow(0).GetCell(0).SetCellValue(text2);
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.WrapText = true;
int num = 2;
foreach (WeeklyReport current2 in list)
{
int firstRow = num;
var countNeedMerged = num;
foreach (WeeklyReportItem current3 in current2.ItemList)
{
IRow expr_1EF = sheet.CreateRow(num);
expr_1EF.Height = 350;
expr_1EF.CreateCell(0).SetCellValue(current2.Monday.ToString("yyyyMMdd") + "-" + current2.Sunday.ToString("yyyyMMdd"));
expr_1EF.CreateCell(1).SetCellValue(current3.OrganizationName);
expr_1EF.CreateCell(2).SetCellValue(current2.UserName);
expr_1EF.CreateCell(3).SetCellValue(current3.WorkTypeName);
expr_1EF.CreateCell(4).SetCellValue(current3.WorkTaskName);
expr_1EF.CreateCell(5).SetCellValue(current3.Content.Replace("<br/>", "\r\n"));
expr_1EF.CreateCell(6).SetCellValue(current3.StatusName);
ICell cell = expr_1EF.CreateCell(7);
if (current3.Date.HasValue)
{
cell.SetCellValue(current3.Date.Value.ToString("yyyy-MM-dd"));
}
using (List <ICell> .Enumerator enumerator3 = expr_1EF.Cells.GetEnumerator())
{
while (enumerator3.MoveNext())
{
enumerator3.Current.CellStyle = cellStyle;
}
}
num++;
}
var needMerged = num - countNeedMerged > 1;
if (needMerged)
{
CellRangeAddress region = new CellRangeAddress(firstRow, num - 1, 0, 0);
sheet.AddMergedRegion(region);
region = new CellRangeAddress(firstRow, num - 1, 1, 1);
sheet.AddMergedRegion(region);
region = new CellRangeAddress(firstRow, num - 1, 2, 2);
sheet.AddMergedRegion(region);
}
}
text3 = Path.Combine(rootPath, "ExcelExport", text2 + "(" + DateTime.Now.ToString("ddHHmmss") + ").xls");
fileStream = new FileStream(text3, FileMode.OpenOrCreate, FileAccess.Write);
workbook.Write(fileStream);
}
catch (Exception arg_3FB_0)
{
throw arg_3FB_0;
}
finally
{
fileStream2.Close();
fileStream2.Dispose();
if (fileStream != null)
{
fileStream.Flush();
fileStream.Close();
fileStream.Dispose();
}
}
return(text3);
}
private void ExportExcelBm()
{
string filePath = Server.MapPath("/AppFile/Excel/ExcelBm.xls"); //duong dan file excel
HSSFWorkbook wb = ExcelNpoi.ReadExcelToHSSFWorkBook(filePath); //tao excel ten ban excel duoc dua vao
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0); //tao sheet moi tu excel duoc chon sheet
HSSFFont font1 = (HSSFFont)wb.CreateFont(); //Khoi tao doi tuong font chu
font1.FontName = "Arial";
font1.FontHeight = 240;
//Chu can giua o
//Thieu chu phai in dam
HSSFFont font2 = (HSSFFont)wb.CreateFont();
font2.FontName = "Arial";
font2.FontHeight = 90;
//create cell style for title
HSSFCellStyle xsStyle = (HSSFCellStyle)wb.CreateCellStyle();
xsStyle.SetFont(font1);
HSSFCellStyle xsStyletime = (HSSFCellStyle)wb.CreateCellStyle();
xsStyletime.SetFont(font2);
//Tao doi tuong hang (Chi duong phep lay hang truoc sau do trong hang co thuoc tinh cot)
var row0 = sheet.CreateRow(0);
row0.CreateCell(0);
var row1 = sheet.CreateRow(1);
row1.CreateCell(1);
CellRangeAddress cellMerge = new CellRangeAddress(0, 0, 0, 20);//Gop cell theo y muon
CellRangeAddress cellMergetime = new CellRangeAddress(1, 2, 1, 1);
sheet.AddMergedRegion(cellMerge);
sheet.AddMergedRegion(cellMergetime);
row0.GetCell(0).CellStyle = xsStyle;
row0.GetCell(0).SetCellValue("Quản lý thông tin sinh viên");
row1.GetCell(1).CellStyle = xsStyletime;
row1.GetCell(1).SetCellValue("Thoi gian ");
var row2 = sheet.CreateRow(2);
row2.CreateCell(0).SetCellValue("STT");
row2.CreateCell(1).SetCellValue("Ho Va Ten");
row2.CreateCell(2).SetCellValue("Ngay Sinh");
row2.CreateCell(3).SetCellValue("Gioi Tinh");
row2.CreateCell(4).SetCellValue("Dia Chi");
row2.CreateCell(5).SetCellValue("SDT");
row2.CreateCell(6).SetCellValue("Email");
row2.CreateCell(7).SetCellValue("Lop");
List <SINHVIENEntities> tbl_Sinhviens = dapSINHVIEN.GetAll();
using (FileStream excel = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Read))
{
#region Đổ dữ liệu ra sheet
int rowIndex = 2;
int stt = 0;
foreach (var item in tbl_Sinhviens)
{
//Neu di muon di to mau cam
//Neu onssite thi to may xanh
var newRow = sheet.CreateRow(rowIndex);
newRow.CreateCell(0).SetCellValue(stt += 1);
newRow.CreateCell(1).SetCellValue(item.HOVATEN);
newRow.CreateCell(2).SetCellValue(item.NGAYSINH.ToString("dd/MM/yyyy"));
newRow.CreateCell(3).SetCellValue(item.GIOITINH == 1 ? "Nữ" : "Nam");
newRow.CreateCell(4).SetCellValue(item.DIACHI);
newRow.CreateCell(5).SetCellValue(item.SDT);
newRow.CreateCell(6).SetCellValue(item.EMAIL);
newRow.CreateCell(7).SetCellValue(item.TENLOP);
rowIndex++;
}
#endregion
#region Xuất file Excel
MemoryStream exportData = new MemoryStream();
wb.Write(exportData);
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-disposition", "attachment; filename=DanhSachSinhVien2020.xls");
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
#endregion
}
}
public void MergeCell(int x1, int y1, int x2, int y2)
{
CellRangeAddress range = new CellRangeAddress(y1, y2, x1, x2);
sheet.AddMergedRegion(range);
}
public void TestCreateCF()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
String formula = "7";
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(formula);
IFontFormatting fontFmt = rule1.CreateFontFormatting();
fontFmt.SetFontStyle(true, false);
IBorderFormatting bordFmt = rule1.CreateBorderFormatting();
bordFmt.BorderBottom = (/*setter*/ BorderStyle.Thin);
bordFmt.BorderTop = (/*setter*/ BorderStyle.Thick);
bordFmt.BorderLeft = (/*setter*/ BorderStyle.Dashed);
bordFmt.BorderRight = (/*setter*/ BorderStyle.Dotted);
IPatternFormatting patternFmt = rule1.CreatePatternFormatting();
patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Yellow.Index);
IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2");
IConditionalFormattingRule[] cfRules =
{
rule1, rule2
};
short col = 1;
CellRangeAddress[] regions =
{
new CellRangeAddress(0, 65535, col, col)
};
sheetCF.AddConditionalFormatting(regions, cfRules);
sheetCF.AddConditionalFormatting(regions, cfRules);
// Verification
Assert.AreEqual(2, sheetCF.NumConditionalFormattings);
sheetCF.RemoveConditionalFormatting(1);
Assert.AreEqual(1, sheetCF.NumConditionalFormattings);
IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0);
Assert.IsNotNull(cf);
regions = cf.GetFormattingRanges();
Assert.IsNotNull(regions);
Assert.AreEqual(1, regions.Length);
CellRangeAddress r = regions[0];
Assert.AreEqual(1, r.FirstColumn);
Assert.AreEqual(1, r.LastColumn);
Assert.AreEqual(0, r.FirstRow);
Assert.AreEqual(65535, r.LastRow);
Assert.AreEqual(2, cf.NumberOfRules);
rule1 = cf.GetRule(0);
Assert.AreEqual("7", rule1.Formula1);
Assert.IsNull(rule1.Formula2);
IFontFormatting r1fp = rule1.GetFontFormatting();
Assert.IsNotNull(r1fp);
Assert.IsTrue(r1fp.IsItalic);
Assert.IsFalse(r1fp.IsBold);
IBorderFormatting r1bf = rule1.GetBorderFormatting();
Assert.IsNotNull(r1bf);
Assert.AreEqual(BorderStyle.Thin, r1bf.BorderBottom);
Assert.AreEqual(BorderStyle.Thick, r1bf.BorderTop);
Assert.AreEqual(BorderStyle.Dashed, r1bf.BorderLeft);
Assert.AreEqual(BorderStyle.Dotted, r1bf.BorderRight);
IPatternFormatting r1pf = rule1.GetPatternFormatting();
Assert.IsNotNull(r1pf);
// Assert.AreEqual(HSSFColor.Yellow.index,r1pf.FillBackgroundColor);
rule2 = cf.GetRule(1);
Assert.AreEqual("2", rule2.Formula2);
Assert.AreEqual("1", rule2.Formula1);
}
private void buttonX1_Click(object sender, System.EventArgs e)
{
string fileName = "";//保存的excel文件名
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "导出Excel(*.xls)|*.xls";
sfd.FileName = "选瓷汇总表";
DataSet ds = (DataSet)rptGrid.PrimaryGrid.DataSource;
if (ds != null)
{
if (sfd.ShowDialog() == DialogResult.OK)
{
fileName = sfd.FileName;
int columnNums = rptGrid.PrimaryGrid.Columns.Count; //列数;
int rowNums = rptGrid.PrimaryGrid.Rows.Count; //行数
HSSFWorkbook book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet("sheet1");
IRow row = sheet.CreateRow(0);
NpoiExcelHelper.setMergedRegion(sheet, row, 0, "产品编号", 0, 1, 0, 0);
NpoiExcelHelper.setMergedRegion(sheet, row, 1, "器型名称", 0, 1, 1, 1);
NpoiExcelHelper.setMergedRegion(sheet, row, 2, "花色名称", 0, 1, 2, 2);
NpoiExcelHelper.setMergedRegion(sheet, row, 3, "窑炉编号", 0, 1, 3, 3);
NpoiExcelHelper.setMergedRegion(sheet, row, 4, "瓷质", 0, 1, 4, 4);
NpoiExcelHelper.setMergedRegion(sheet, row, 5, "班次", 0, 1, 5, 5);
NpoiExcelHelper.setMergedRegion(sheet, row, 6, "A+", 0, 0, 6, 7);
NpoiExcelHelper.setMergedRegion(sheet, row, 8, "A等", 0, 0, 8, 9);
NpoiExcelHelper.setMergedRegion(sheet, row, 10, "CA等", 0, 0, 10, 11);
NpoiExcelHelper.setMergedRegion(sheet, row, 12, "B甲", 0, 0, 12, 13);
NpoiExcelHelper.setMergedRegion(sheet, row, 14, "B等", 0, 0, 14, 15);
NpoiExcelHelper.setMergedRegion(sheet, row, 16, "C等", 0, 0, 16, 17);
NpoiExcelHelper.setMergedRegion(sheet, row, 18, "补釉", 0, 0, 18, 19);
NpoiExcelHelper.setMergedRegion(sheet, row, 20, "废品", 0, 0, 20, 21);
NpoiExcelHelper.setMergedRegion(sheet, row, 22, "总数", 0, 1, 22, 23);
IRow row2 = sheet.CreateRow(1);
NpoiExcelHelper.setQtyAndRate(row2, 4, 18, isNeedRate);
for (int i = 2; i < rowNums + 2; i++)//
{
IRow r = sheet.CreateRow(i);
if (isNeedRate)
{
for (int j = 0; j < columnNums; j++)
{
ICell c = r.CreateCell(j);
GridRow gr = (GridRow)rptGrid.PrimaryGrid.Rows[i - 2];
//瓷质
if (gr[j].FormattedValue != "")// gc.DataPropertyName == "FMaterial")
{
c.SetCellValue(gr[j].FormattedValue.ToString());
}
else
{
c.SetCellValue(gr[j].Value == null ? "" : gr[j].Value.ToString());
}
}
}
else
{
for (int j = 0; j < 6; j++)
{
ICell c = r.CreateCell(j);
GridRow gr = (GridRow)rptGrid.PrimaryGrid.Rows[i - 2];
//瓷质
if (gr[j].FormattedValue != "")// gc.DataPropertyName == "FMaterial")
{
c.SetCellValue(gr[j].FormattedValue.ToString());
}
else
{
c.SetCellValue(gr[j].Value == null ? "" : gr[j].Value.ToString());
}
}
for (int j = 6; j < columnNums; j = j + 2)
{
ICell c = r.CreateCell(j);
CellRangeAddress region = new CellRangeAddress(i, i, j, j + 1);
sheet.AddMergedRegion(region);
GridRow gr = (GridRow)rptGrid.PrimaryGrid.Rows[i - 2];
//瓷质
if (gr[j].FormattedValue != "")// gc.DataPropertyName == "FMaterial")
{
c.SetCellValue(gr[j].FormattedValue.ToString());
}
else
{
c.SetCellValue(gr[j].Value == null ? "" : gr[j].Value.ToString());
}
}
}
}
//for (int i = 0; i <= rowNums + 2; i++)
//{
// sheet.AutoSizeColumn(i);
//}
FileStream file = new FileStream(fileName, FileMode.OpenOrCreate);
book.Write(file);
file.Flush();
file.Close();
MessageUtil.ShowTips("导出成功!");
}
}
}
public void TestCreateFontFormatting()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, "7");
IFontFormatting fontFmt = rule1.CreateFontFormatting();
Assert.IsFalse(fontFmt.IsItalic);
Assert.IsFalse(fontFmt.IsBold);
fontFmt.SetFontStyle(true, true);
Assert.IsTrue(fontFmt.IsItalic);
Assert.IsTrue(fontFmt.IsBold);
Assert.AreEqual(-1, fontFmt.FontHeight); // not modified
fontFmt.FontHeight = (/*setter*/ 200);
Assert.AreEqual(200, fontFmt.FontHeight);
fontFmt.FontHeight = (/*setter*/ 100);
Assert.AreEqual(100, fontFmt.FontHeight);
Assert.AreEqual(FontSuperScript.None, fontFmt.EscapementType);
fontFmt.EscapementType = (/*setter*/ FontSuperScript.Sub);
Assert.AreEqual(FontSuperScript.Sub, fontFmt.EscapementType);
fontFmt.EscapementType = (/*setter*/ FontSuperScript.None);
Assert.AreEqual(FontSuperScript.None, fontFmt.EscapementType);
fontFmt.EscapementType = (/*setter*/ FontSuperScript.Super);
Assert.AreEqual(FontSuperScript.Super, fontFmt.EscapementType);
Assert.AreEqual(FontUnderlineType.None, fontFmt.UnderlineType);
fontFmt.UnderlineType = (/*setter*/ FontUnderlineType.Single);
Assert.AreEqual(FontUnderlineType.Single, fontFmt.UnderlineType);
fontFmt.UnderlineType = (/*setter*/ FontUnderlineType.None);
Assert.AreEqual(FontUnderlineType.None, fontFmt.UnderlineType);
fontFmt.UnderlineType = (/*setter*/ FontUnderlineType.Double);
Assert.AreEqual(FontUnderlineType.Double, fontFmt.UnderlineType);
Assert.AreEqual(-1, fontFmt.FontColorIndex);
fontFmt.FontColorIndex = (/*setter*/ HSSFColor.Red.Index);
Assert.AreEqual(HSSFColor.Red.Index, fontFmt.FontColorIndex);
fontFmt.FontColorIndex = (/*setter*/ HSSFColor.Automatic.Index);
Assert.AreEqual(HSSFColor.Automatic.Index, fontFmt.FontColorIndex);
fontFmt.FontColorIndex = (/*setter*/ HSSFColor.Blue.Index);
Assert.AreEqual(HSSFColor.Blue.Index, fontFmt.FontColorIndex);
IConditionalFormattingRule[] cfRules = { rule1 };
CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:A5") };
sheetCF.AddConditionalFormatting(regions, cfRules);
// Verification
IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0);
Assert.IsNotNull(cf);
Assert.AreEqual(1, cf.NumberOfRules);
IFontFormatting r1fp = cf.GetRule(0).GetFontFormatting();
Assert.IsNotNull(r1fp);
Assert.IsTrue(r1fp.IsItalic);
Assert.IsTrue(r1fp.IsBold);
Assert.AreEqual(FontSuperScript.Super, r1fp.EscapementType);
Assert.AreEqual(FontUnderlineType.Double, r1fp.UnderlineType);
Assert.AreEqual(HSSFColor.Blue.Index, r1fp.FontColorIndex);
}
private static void ConfirmRange(CellRangeAddress cr, bool isFullRow, bool isFullColumn)
{
Assert.AreEqual(isFullRow, cr.IsFullRowRange, "isFullRowRange");
Assert.AreEqual(isFullColumn, cr.IsFullColumnRange, "isFullColumnRange");
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
}
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();
}
/// <summary>
/// Excel数据导出简单示例
/// </summary>
/// <param name="resultMsg">导出结果</param>
/// <param name="excelFilePath">保存excel文件路径</param>
/// <returns></returns>
public bool ExcelDataExport(out string resultMsg, out string excelFilePath)
{
var result = true;
excelFilePath = "";
resultMsg = "successfully";
//Excel导出名称
string excelName = "人才培训课程表";
try
{
//首先创建Excel文件对象
var workbook = new HSSFWorkbook();
//创建工作表，也就是Excel中的sheet，给工作表赋一个名称(Excel底部名称)
var sheet = workbook.CreateSheet("人才培训课程表");
//sheet.DefaultColumnWidth = 20;//默认列宽
sheet.ForceFormulaRecalculation = true;//TODO:是否开始Excel导出后公式仍然有效（非必须）
#region table 表格内容设置
#region 标题样式
//设置顶部大标题样式
var cellStyleFont = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 20, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Coral.Index, HSSFColor.White.Index,
FontUnderlineType.None, FontSuperScript.None, false);
//第一行表单
var row = NpoiExcelExportHelper._.CreateRow(sheet, 0, 28);
var cell = row.CreateCell(0);
//合并单元格 例： 第1行到第2行 第3列到第4列围成的矩形区域
//TODO:关于Excel行列单元格合并问题
/**
* 第一个参数：从第几行开始合并
* 第二个参数：到第几行结束合并
* 第三个参数：从第几列开始合并
* 第四个参数：到第几列结束合并
**/
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
sheet.AddMergedRegion(region);
cell.SetCellValue("人才培训课程表");//合并单元格后，只需对第一个位置赋值即可（TODO:顶部标题）
cell.CellStyle = cellStyleFont;
//二级标题列样式设置
var headTopStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 15, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Grey25Percent.Index, HSSFColor.Black.Index,
FontUnderlineType.None, FontSuperScript.None, false);
//表头名称
var headerName = new[] { "课程类型", "序号", "日期", "课程名称", "内容概要", "讲师简介" };
row = NpoiExcelExportHelper._.CreateRow(sheet, 1, 24);//第二行
for (var i = 0; i < headerName.Length; i++)
{
cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, i, headerName[i]);
//设置单元格宽度
if (headerName[i] == "讲师简介" || headerName[i] == "内容概要")
{
sheet.SetColumnWidth(i, 10000);
}
else
{
sheet.SetColumnWidth(i, 5000);
}
}
#endregion
#region 单元格内容信息
//单元格边框样式
var cellStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 10, true, 400);
//左侧列单元格合并 begin
//TODO:关于Excel行列单元格合并问题（合并单元格后，只需对第一个位置赋值即可）
/**
* 第一个参数：从第几行开始合并
* 第二个参数：到第几行结束合并
* 第三个参数：从第几列开始合并
* 第四个参数：到第几列结束合并
**/
CellRangeAddress leftOne = new CellRangeAddress(2, 7, 0, 0);
sheet.AddMergedRegion(leftOne);
CellRangeAddress leftTwo = new CellRangeAddress(8, 11, 0, 0);
sheet.AddMergedRegion(leftTwo);
//左侧列单元格合并 end
var currentDate = DateTime.Now;
string[] curriculumList = new[] { "艺术学", "设计学", "材料学", "美学", "心理学", "中国近代史", "管理人员的情绪修炼", "高效时间管理", "有效的目标管理", "沟通与协调" };
int number = 1;
for (var i = 0; i < 10; i++)
{
row = NpoiExcelExportHelper._.CreateRow(sheet, i + 2, 20); //sheet.CreateRow(i+2);//在上面表头的基础上创建行
switch (number)
{
case 1:
cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, "公共类课程");
break;
case 7:
cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, "管理类课程");
break;
}
//创建单元格列公众类课程
cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 1, number.ToString());
cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 2, currentDate.AddDays(number).ToString("yyyy-MM-dd"));
cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 3, curriculumList[i]);
cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 4, "提升，充实，拓展自己综合实力");
cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 5, "追逐时光_" + number + "号金牌讲师！");
number++;
}
#endregion
#endregion
string folder = DateTime.Now.ToString("yyyyMMdd");
//保存文件到静态资源文件夹中（wwwroot）,使用绝对路径
var uploadPath = _environment.WebRootPath + "/UploadFile/" + folder + "/";
//excel保存文件名
string excelFileName = excelName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//创建目录文件夹
if (!Directory.Exists(uploadPath))
{
Directory.CreateDirectory(uploadPath);
}
//Excel的路径及名称
string excelPath = uploadPath + excelFileName;
//使用FileStream文件流来写入数据（传入参数为：文件所在路径，对文件的操作方式，对文件内数据的操作）
var fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
//向Excel文件对象写入文件流，生成Excel文件
workbook.Write(fileStream);
//关闭文件流
fileStream.Close();
//释放流所占用的资源
fileStream.Dispose();
//excel文件保存的相对路径，提供前端下载
var relativePositioning = "/UploadFile/" + folder + "/" + excelFileName;
excelFilePath = relativePositioning;
}
catch (Exception e)
{
result = false;
resultMsg = e.Message;
}
return(result);
}
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, string[] mergedColumNames)
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook();
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook();
}
#region 样式
ICellStyle style1 = workbook.CreateCellStyle(); //样式
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
//设置边框
style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style1.WrapText = true;//自动换行
IDataFormat dataFormat1 = workbook.CreateDataFormat();
style1.DataFormat = dataFormat1.GetFormat("@");
#endregion
ICell cell;
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
return(-1);
}
int[] columnWidth = { 15, 15, 15, 10 };
for (int Length = 0; Length < columnWidth.Length; Length++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet.SetColumnWidth(Length, 256 * columnWidth[Length]);
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
row.Height = 25 * 20;
for (j = 0; j < data.Columns.Count; ++j)
{
cell = row.CreateCell(j);
cell.SetCellValue(data.Columns[j].ColumnName);
cell.CellStyle = style1;
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
row.Height = 25 * 20;
for (j = 0; j < data.Columns.Count; ++j)
{
string cellText = data.Rows[i][j].ToString();
cell = row.CreateCell(j);
cell.SetCellValue(cellText);
cell.CellStyle = style1;
if (i > 0)
{
bool needMerged = false;
if (data.Columns[j] != null)
{
foreach (var mergedColumName in mergedColumNames)
{
if (string.Equals(mergedColumName, data.Columns[j].ColumnName))
{
if (i < data.Rows.Count - 1)
{
string nextCellText = data.Rows[i + 1][j].ToString();
if (nextCellText != cellText)
{
needMerged = true;
}
}
else
{
needMerged = true;
}
}
}
}
if (needMerged)
{
int firstrow = MergedRegionGetFirstRowNo(row.RowNum, j, sheet, cellText);
CellRangeAddress region = new CellRangeAddress(firstrow, row.RowNum, j, j);
sheet.AddMergedRegion(region);
}
}
//cell = row.CreateCell(j);
//cell.SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
workbook.Write(fs); //写入到excel
fs.Close();
fs.Dispose();
return(count);
}
catch (Exception ex)
{
//Console.WriteLine("Exception: " + ex.Message);
return(-1);
}
}
private void ExportDynamicReportBody(
ref ExportExcelInfo exportExcelInfo,
GetDynamicReportCriteria criteria,
DynamicReportInfo dynamicReport)
{
if (dynamicReport == null || dynamicReport.ChildReports.Count == 0)
{
exportExcelInfo.Worksheet = exportExcelInfo.ExcelFile.CreateSheet(Labels.DynamicReport_SheetName);
ExportDynamicReportHeader(ref exportExcelInfo, criteria, dynamicReport);
NpoiExcelExtensions.RenderNoInfoMessage(exportExcelInfo, exportExcelInfo.CurrentRow);
return;
}
var periodGroupBackgroundColor = Color.FromArgb(215, 215, 215);
var periodGroupBackgroundColorIndex = (short)HSSFColor.DarkYellow.Index;
NpoiExcelExtensions.SetColorAtIndex(exportExcelInfo, periodGroupBackgroundColor, periodGroupBackgroundColorIndex);
ICellStyle periodGroupTitleStyle = NpoiExcelExtensions.GetTextStyle(
exportExcelInfo.ExcelFile,
periodGroupBackgroundColorIndex,
true,
CRMHorizontalAlignment.Left);
var font = exportExcelInfo.ExcelFile.CreateFont();
font.FontHeightInPoints = NpoiExcelExtensions.FontSize;
font.FontName = NpoiExcelExtensions.FontName;
font.Color = (short)HSSFColor.Blue.Index;
font.Boldweight = (short)FontBoldWeight.Bold;
periodGroupTitleStyle.SetFont(font);
NpoiExcelExtensions.SetBorderToCell(periodGroupTitleStyle);
CellRangeAddress cellRangeAddress;
var totalItems = dynamicReport.TotalChildReports;
var normalRowWriter = new CommunicationLogRowExcelWriter(
exportExcelInfo,
NpoiExcelExtensions.BackgroundColorIndex);
var closedRowWriter = new CommunicationLogRowExcelWriter(
exportExcelInfo,
_closedBackgroundColorIndex);
var checkedRowWriter = new CommunicationLogRowExcelWriter(
exportExcelInfo,
_checkedBackgroundColorIndex);
var pendingRowWriter = new CommunicationLogRowExcelWriter(
exportExcelInfo,
_pendingBackgroundColorIndex);
var maxRowPerSheet = NpoiExcelExtensions.MaxRowPerSheet;
var numberOfSheet = (totalItems / maxRowPerSheet) +
(totalItems % maxRowPerSheet != 0 ? 1 : 0);
var mainCategoryCount = dynamicReport.ChildReports.Count;
var currentIndexOfMainCategory = 0;
var currentIndexOfDetailItem = 0;
var startContentIndex = 4;
var actualMaxRowPerSheet = (maxRowPerSheet * 2) + startContentIndex;
for (var sheetIndex = 0; sheetIndex < numberOfSheet; sheetIndex++)
{
var sheetName = numberOfSheet > 1 ?
string.Format("{0} - {1}", Labels.DynamicReport_SheetName, sheetIndex + 1) :
Labels.DynamicReport_SheetName;
exportExcelInfo.Worksheet = exportExcelInfo.ExcelFile.CreateSheet(sheetName);
exportExcelInfo.CurrentRow = 0;
ExportDynamicReportHeader(ref exportExcelInfo, criteria, dynamicReport);
for (var i = currentIndexOfMainCategory; i < mainCategoryCount && exportExcelInfo.CurrentRow < actualMaxRowPerSheet; i++)
{
var periodReport = dynamicReport.ChildReports[i];
var childsLength = periodReport.ChildReports.Count;
if (exportExcelInfo.CurrentRow > startContentIndex)
{
currentIndexOfDetailItem = 0;
}
exportExcelInfo.Worksheet.CreateRow(exportExcelInfo.CurrentRow);
exportExcelInfo.CurrentColumn = exportExcelInfo.StartColumn;
cellRangeAddress = new CellRangeAddress(
exportExcelInfo.CurrentRow,
exportExcelInfo.CurrentRow + 1,
exportExcelInfo.StartColumn,
exportExcelInfo.MaxColumnIndex);
NpoiExcelExtensions.RenderMergedCells(
exportExcelInfo,
cellRangeAddress,
string.Format("{0} [{1}]", periodReport.Name, childsLength),
periodGroupTitleStyle);
exportExcelInfo.CurrentRow += 2;
for (var j = currentIndexOfDetailItem; j < childsLength && exportExcelInfo.CurrentRow < actualMaxRowPerSheet; j++)
{
exportExcelInfo.Worksheet.CreateRow(exportExcelInfo.CurrentRow);
exportExcelInfo.CurrentColumn = exportExcelInfo.StartColumn;
var info = periodReport.ChildReports[j];
if (string.Equals(info.StatusName, Labels.Closed, StringComparison.OrdinalIgnoreCase))
{
closedRowWriter.WriteRow(exportExcelInfo, info, j);
}
else if (string.Equals(info.StatusName, Labels.Checked, StringComparison.OrdinalIgnoreCase) ||
string.Equals(info.StatusName, Labels.Solved, StringComparison.OrdinalIgnoreCase))
{
checkedRowWriter.WriteRow(exportExcelInfo, info, j);
}
else if (string.Equals(info.StatusName, Labels.Pending, StringComparison.OrdinalIgnoreCase))
{
pendingRowWriter.WriteRow(exportExcelInfo, info, j);
}
else
{
normalRowWriter.WriteRow(exportExcelInfo, info, j);
}
currentIndexOfDetailItem++;
}
if (currentIndexOfDetailItem == childsLength)
{
currentIndexOfMainCategory++;
}
}
}
}
/// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="dt">源数据</param>
/// <param name="fields">字段名（与 names 列名顺序一致，含个数）</param>
/// <param name="names">列名（与 fields 字段名顺序一致，含个数）</param>
/// <param name="merger">合并列相同的值，用于上下行单元格重复合并（多个列间用半角逗号间隔）</param>
/// <param name="totalColumn">合计列的值，仅对数字、货币类型有效（在最后一行自动合计显示）</param>
/// <param name="columnWidth">自定义列宽，格式：<key, value> 参数：key表示列索引号，value表示列宽</param>
/// <returns>返回生成后的URL下载地址</returns>
public string ToExcel(string fileName, DataTable dt, string[] fields, string[] names, int[] merger, int[] totalColumn = null, Dictionary <int, int> columnWidth = null, bool hasHeaderText = true, bool formatFileName = true)
{
if (dt != null && dt.Rows.Count > 0 && fields.Length == names.Length)
{
//创建Excel文件
string sheetName = fileName;
string headerText = HeaderText ?? fileName;
if (formatFileName)
{
fileName = string.Format("{0}_{1}.xls", fileName, DateTime.Now.ToString("yyyy-MM-dd"));
}
else
{
fileName = string.Format("{0}.xls", fileName);
}
HSSFWorkbook book = new HSSFWorkbook();
book.DocumentSummaryInformation = DSI;
book.SummaryInformation = SummaryInfo(fileName);
//设置表
ISheet iSheet;
//创建数据格式
IDataFormat iDataFormat = book.CreateDataFormat();
//设置日期格式
ICellStyle dataStyle = book.CreateCellStyle();
int namesCount = names.Length;
int fieldsCount = fields.Length;
#region //自适应列宽
int colCount = names.Length;
int[] colWidth = new int[colCount];
for (int i = 0; i < namesCount; i++)
{
if (columnWidth != null && columnWidth.ContainsKey(i))
{
colWidth[i] = (columnWidth[i] < 5) ? 5 : columnWidth[i] + 2;
}
else
{
colWidth[i] = Encoding.GetEncoding(936).GetBytes(names[i]).Length;
colWidth[i] = (colWidth[i] < 5) ? 5 : colWidth[i] + 2;
}
}
DataRowCollection drc = dt.Rows;
int rowCount = drc.Count;
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
int tmp = Encoding.GetEncoding(936).GetBytes(drc[i][fields[j]].ToString()).Length;
if (tmp > colWidth[j])
{
colWidth[j] = tmp;
}
}
}
if (rowCount > this.MaxRows)
{
iSheet = book.CreateSheet(string.Format("{0}(1)", sheetName));
}
else
{
iSheet = book.CreateSheet(sheetName);
}
#endregion
IFont titleFont = book.CreateFont();
IFont headFont = book.CreateFont();
IFont textFont = book.CreateFont();
ICellStyle css = book.CreateCellStyle();
//
int cur = 0;
int tab = 2;
decimal dec = 0;
Dictionary <int, decimal> heji = new Dictionary <int, decimal>();
for (int index = 0; index < rowCount; index++)
{
#region 设置表头、列名、样式
if ((index == 0 || index % this.MaxRows == 0))
{
if (cur != 0)
{
iSheet = book.CreateSheet(string.Format("{0}({1})", sheetName, tab));
tab++;
}
cur = 1;
//表头
IRow header = iSheet.CreateRow(0);
header.HeightInPoints = 25;
//样式
ICellStyle cellStyle = book.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
titleFont.FontHeightInPoints = 14;
cellStyle.SetFont(titleFont);
if (hasHeaderText)
{
if (!string.IsNullOrWhiteSpace(headerText))
{
header.CreateCell(0).SetCellValue(headerText);
header.GetCell(0).CellStyle = cellStyle;
CellRangeAddress ra = new CellRangeAddress(0, 0, 0, colCount - 1);
iSheet.AddMergedRegion(ra);
((HSSFSheet)iSheet).SetEnclosedBorderOfRegion(ra, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
header = iSheet.CreateRow(1);//列头
cur = 2;
}
}
header.HeightInPoints = 18;
cellStyle = book.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
headFont.FontHeightInPoints = 12;
cellStyle.SetFont(headFont);
for (int i = 0; i < namesCount; i++)
{
header.CreateCell(i).SetCellValue(names[i]);
header.GetCell(i).CellStyle = cellStyle;
iSheet.SetColumnWidth(i, (int)Math.Ceiling((double)((colWidth[i] + 1) * 256)));
}
}
#endregion
#region 填充内容
IRow rows = iSheet.CreateRow(cur);
rows.HeightInPoints = 18;
for (int i = 0; i < fieldsCount; i++)
{
ICell cell = rows.CreateCell(i);
dataStyle.Alignment = css.Alignment = HorizontalAlignment.Left;
dataStyle.VerticalAlignment = css.VerticalAlignment = VerticalAlignment.Center;
dataStyle.WrapText = css.WrapText = true;
dataStyle.BorderTop = css.BorderTop = BorderStyle.Thin;
dataStyle.BorderBottom = css.BorderBottom = BorderStyle.Thin;
dataStyle.BorderLeft = css.BorderLeft = BorderStyle.Thin;
dataStyle.BorderRight = css.BorderRight = BorderStyle.Thin;
textFont.FontHeightInPoints = 10;
css.SetFont(textFont);
dataStyle.SetFont(textFont);
cell.CellStyle = css;
if (index > 0 && IsExistMerger(merger, i) && (index % this.MaxRows != 0))
{
if (string.Compare(Convert.ToString(drc[index][fields[i]]), Convert.ToString(drc[index - 1][fields[i]])) == 0 &&
string.Compare(Convert.ToString(drc[index][fields[merger[0]]]), Convert.ToString(drc[index - 1][fields[merger[0]]])) == 0)
{
iSheet.AddMergedRegion(new CellRangeAddress(cur - 1, cur, i, i));
}
}
string val = Convert.ToString(drc[index][fields[i]]);
val = this.IsFilterHTML ? FilterHtml.Remove(val) : val;
dec = 0;
this.SetCellValue(cell, iDataFormat, dataStyle, drc[index][fields[i]].GetType().ToString(), val, out dec);
iSheet.SetColumnWidth(i, (int)Math.Ceiling((double)((colWidth[i] + 1) * 256)));
if (IsExistMerger(totalColumn, i))
{
if (!heji.ContainsKey(i))
{
heji.Add(i, dec);
}
else
{
if (!cell.IsMergedCell)
{
dec += heji[i];
heji.Remove(i);
heji.Add(i, dec);
}
}
}
}
#endregion
cur++;
}
#region 对指定列进行合计
int beginColumn = 0;
if (totalColumn != null && totalColumn.Length > 0)
{
IRow rows = iSheet.CreateRow(cur);
rows.HeightInPoints = 18;
for (int i = 0; i < fieldsCount; i++)
{
ICell cell = rows.CreateCell(i);
css.Alignment = HorizontalAlignment.Left;
css.VerticalAlignment = VerticalAlignment.Center;
css.WrapText = true;
css.BorderTop = BorderStyle.Thin;
css.BorderBottom = BorderStyle.Thin;
css.BorderLeft = BorderStyle.Thin;
css.BorderRight = BorderStyle.Thin;
textFont.FontHeightInPoints = 10;
css.SetFont(textFont);
cell.CellStyle = css;
dec = 0;
if (IsExistMerger(totalColumn, i))
{
this.SetCellValue(cell, iDataFormat, dataStyle, heji[i].GetType().ToString(), heji[i].ToString(), out dec);
if (beginColumn == 0)
{
beginColumn = i;
}
}
else
{
this.SetCellValue(cell, iDataFormat, dataStyle, "System.String", "", out dec);
}
iSheet.SetColumnWidth(i, (int)Math.Ceiling((double)((colWidth[i] + 2) * 256)));
}
if (beginColumn > 0)//合计单独计算
{
var cell = rows.GetCell(0);
css.Alignment = HorizontalAlignment.Right;
css.SetFont(textFont);
cell.CellStyle = css;
iSheet.AddMergedRegion(new CellRangeAddress(cur, cur, 0, beginColumn - 1));
this.SetCellValue(cell, iDataFormat, dataStyle, "System.String", "合计：", out dec);
beginColumn = -1;
}
}
#endregion
//输出文件
return(this.OutputFile(book, fileName));
}
return(string.Empty);
}
public void MergeCell(int x1,int y1,int x2,int y2)
{
CellRangeAddress range = new CellRangeAddress(y1, y2, x1, x2);
sheet.AddMergedRegion(range);
}
private static void ConfirmRegion(CellRangeAddress ra, CellRangeAddress rb)
{
Assert.AreEqual(ra.FirstRow, rb.FirstRow);
Assert.AreEqual(ra.LastRow, rb.LastRow);
Assert.AreEqual(ra.FirstColumn, rb.FirstColumn);
Assert.AreEqual(ra.LastColumn, rb.LastColumn);
}
private void WriteRowValues( object[] values, int rowNum, int startCol )
{
if( mSheet == null )
return;
CellRangeAddress range = new CellRangeAddress( rowNum, rowNum, startCol, startCol + values.Length - 1 );
CellWalk cw = new CellWalk( mSheet, range );
cw.SetTraverseEmptyCells( true );
CellInserter ci = new CellInserter( new List<object>( values ) );
cw.Traverse( ci );
}
public void SetArrayFormula(CellRangeAddress r, Ptg[] ptgs)
{
ArrayRecord arr = new ArrayRecord(Zephyr.Utils.NPOI.SS.Formula.Formula.Create(ptgs), new CellRangeAddress8Bit(r.FirstRow, r.LastRow, r.FirstColumn, r.LastColumn));
_sharedValueManager.AddArrayRecord(arr);
}
static void Main(string[] args)
{
InitializeWorkbook();
Dictionary <String, ICellStyle> styles = CreateStyles(hssfworkbook);
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 = (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();
}
/// <summary>
/// 往excel中插入图片
/// </summary>
/// <param name="imgInfos">要插入的图片信息，匿名类集合，格式:{sheetName:存放到哪个sheet里(不存在则会新增),path:图片物理路径,imgName:图片类型名称,col1:图片左上角从第几列开始,row1:图片左上角从第几行开始,col2:图片右下角在第几列结束,row2:图片右下角在第几行结束}</param>
public void InsertImg(List <object> imgInfos)
{
if (imgInfos == null || imgInfos.Count < 1)
{
return;
}
int insertRow = imgInfos.Max(_obj => Convert.ToInt32(_obj.GetType().GetProperty("row2").GetValue(_obj, null))) + 3; //所有图片最大占用多少行
int insertCol = imgInfos.Max(_obj => Convert.ToInt32(_obj.GetType().GetProperty("col2").GetValue(_obj, null))) + 3; //所有图片最大占用多少列
HSSFSheet sheet = null;
foreach (object imgObj in imgInfos)
{
string sheetName = Convert.ToString(imgObj.GetType().GetProperty("sheetName").GetValue(imgObj, null)); //要生成到哪个sheet,
string imgPath = Convert.ToString(imgObj.GetType().GetProperty("path").GetValue(imgObj, null)); //图片物理路径
string imgName = Convert.ToString(imgObj.GetType().GetProperty("imgName").GetValue(imgObj, null)); //图片名称
int col1 = Convert.ToInt32(imgObj.GetType().GetProperty("col1").GetValue(imgObj, null)); //图片左上角从第几列开始
int row1 = Convert.ToInt32(imgObj.GetType().GetProperty("row1").GetValue(imgObj, null)); //图片左上角从第几行开始
int col2 = Convert.ToInt32(imgObj.GetType().GetProperty("col2").GetValue(imgObj, null)); //图片右下角在第几列结束
int row2 = Convert.ToInt32(imgObj.GetType().GetProperty("row2").GetValue(imgObj, null)); //图片右下角在第几行结束
if (!File.Exists(imgPath))
{
continue;
}
string extension = Path.GetExtension(imgPath);//获取扩展名
PictureType pictureType;
//判断扩展名
switch (extension.ToLower())
{
#region
case "png":
pictureType = PictureType.PNG;
break;
case "dib":
pictureType = PictureType.DIB;
break;
case "emf":
pictureType = PictureType.EMF;
break;
case "pict":
pictureType = PictureType.PICT;
break;
case "wmf":
pictureType = PictureType.WMF;
break;
default:
pictureType = PictureType.JPEG;
break;
#endregion
}
//获取sheet，没有就创建sheet
sheet = (HSSFSheet)hssfWorkBook.GetSheet(sheetName);
if (sheet == null)
{
sheet = (HSSFSheet)hssfWorkBook.CreateSheet(sheetName);
sheet.InsertRow(insertRow, insertCol);
}
//将图片插到sheet里
byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
int pictureIdx = hssfWorkBook.AddPicture(bytes, pictureType);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//插如图片名称
var tempRow = sheet.GetRow(row2);
var tempCell = tempRow.GetCell(col1);
tempCell.SetCellValue(imgName);
CellRangeAddress cellRangeAddress = new CellRangeAddress(row2, row2, col1, col2 - 1);//合并单元格
sheet.AddMergedRegion(cellRangeAddress);
}
}
/// <summary>
/// Sets the topBorderColor attribute of the HSSFRegionUtil object
/// </summary>
/// <param name="color">The color of the border</param>
/// <param name="region">The region that should have the border</param>
/// <param name="sheet">The sheet that the region is on.</param>
/// <param name="workbook">The workbook that the region is on.</param>
public static void SetTopBorderColor(int color, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int colStart = region.FirstColumn;
int colEnd = region.LastColumn;
int rowIndex = region.FirstRow;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.TOP_BORDER_COLOR, color);
NPOI.SS.UserModel.Row row = HSSFCellUtil.GetRow(rowIndex, sheet);
for (int i = colStart; i <= colEnd; i++)
{
cps.SetProperty(row, i);
}
}
private static void createHeader <T>(T item, XSSFWorkbook xlBook, ISheet sheet, int columnIndex, IEnumerable <ExcelOnWritingColumnConfiguration> columnMaps, CellRangeAddress mergedCellRange = null) where T : class
{
var headerRow = sheet.CreateRow(sheet.LastRowNum + 1);
foreach (var column in columnMaps)
{
var headerConfig = column.Header;
var headerStyle = createCellStyle(xlBook, true);
var cell = headerRow.CreateCell(columnIndex + column.ColumnIndex, CellType.String);
cell.CellStyle = headerStyle;
cell.SetCellValue(column.ColumnName);
if (!headerConfig.BackgroundColor.IsEmpty)
{
var backgroundColor = new XSSFColor(headerConfig.BackgroundColor);
headerStyle.SetFillForegroundColor(backgroundColor);
headerStyle.FillPattern = FillPattern.SolidForeground;
}
if (!headerConfig.TextColor.IsEmpty)
{
var textColor = new XSSFColor(headerConfig.TextColor);
headerStyle.GetFont().SetColor(textColor);
}
// add merged cell
CellRangeAddress mergedArea = null;
if (column.Children != null)
{
// add merged cell horizontally
mergedArea = new CellRangeAddress(headerRow.RowNum, headerRow.RowNum, column.ColumnIndex, (column.ColumnIndex + column.Children.Length - 1));
createHeader(item, xlBook, sheet, column.ColumnIndex, ((ExcelOnWritingColumnConfiguration[])column.Children).ToList(), mergedArea);
}
else
{
// add merged cell vertically
if (columnMaps.Any(x => x.Children != null && x.Children.Count() > 0))
{
mergedArea = new CellRangeAddress(headerRow.RowNum, headerRow.RowNum + 1, column.ColumnIndex, column.ColumnIndex);
}
}
if (mergedArea != null)
{
sheet.AddMergedRegion(mergedArea);
}
}
}
/// <summary>
/// 根据传入数据新建sheet表单到指定workbook
/// </summary>
/// <param name="objectDatas"></param>
/// <param name="excelHeader"></param>
/// <param name="sheetName"></param>
/// <param name="regulars"></param>
/// <param name="workbook"></param>
private static void CreateExcelSheetByDatas <T>(List <T> objectDatas, string excelHeader, string sheetName, ref IWorkbook workbook, string xmlPath)
{
var regulars = GetExportRegulars(xmlPath);
// excel sheet表单
ISheet sheet = workbook.CreateSheet(sheetName);
// excel行数
int rows = 0;
#region 单元格 -表头格式
#region 表头字体
IFont fontTitle = workbook.CreateFont();
fontTitle.FontHeightInPoints = 12;
fontTitle.Boldweight = (short)FontBoldWeight.Bold;
#endregion
ICellStyle styleTitle = workbook.CreateCellStyle();
styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
styleTitle.SetFont(fontTitle);
styleTitle.VerticalAlignment = VerticalAlignment.Center;
styleTitle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; //XSSFColor
styleTitle.FillPattern = FillPattern.SolidForeground;
#endregion
#region 单元格 -表体格式
#region 表体字体
IFont fontMessage = workbook.CreateFont();
fontMessage.FontHeightInPoints = 10;
#endregion
ICellStyle styleMessage = workbook.CreateCellStyle();
styleMessage.Alignment = HorizontalAlignment.Center;
styleMessage.SetFont(fontMessage);
styleMessage.VerticalAlignment = VerticalAlignment.Center;
#endregion
if (!string.IsNullOrEmpty(excelHeader))//表头存在
{
// 创建表头并赋值
int firstRowCellCount = GetAttributeCount(objectDatas.First());
IRow headerRow = sheet.CreateRow(rows);
headerRow.HeightInPoints = 40;
var headerCell = headerRow.CreateCell(0);
headerCell.SetCellValue(excelHeader);
// 合并表头
var cellRangeAddress = new CellRangeAddress(rows, rows, 0, firstRowCellCount - 1);
sheet.AddMergedRegion(cellRangeAddress);
// 设置表头格式
headerCell.CellStyle = styleTitle;
}
//生成表头(属性表头)
if (objectDatas.Any())
{
rows++;
// excel列数
int cells = -1;
// 创建数据行
var firstRow = sheet.CreateRow(rows);
firstRow.HeightInPoints = 16;
var objectData = objectDatas.FirstOrDefault();
foreach (System.Reflection.PropertyInfo p in objectData.GetType().GetProperties())
{
cells++;
var regular = regulars.Find(t => t.PropertyName == p.Name);
if (regular == null)
{
throw new Exception("导出excel时，出现未配置字段。表：" + objectData.GetType().Name + ",字段：" + p.Name);
}
var firstRowCell = firstRow.CreateCell(cells);
firstRowCell.SetCellValue(regular.ExportFieldName);
sheet.SetColumnWidth(cells, regular.ExportFieldName.Length * 256 * 4);
firstRowCell.CellStyle = styleMessage;
}
}
// 反射object对象，遍历字段
foreach (var objectData in objectDatas)
{
rows++;
// excel列数
int cells = -1;
// 创建数据行
var messageRow = sheet.CreateRow(rows);
messageRow.HeightInPoints = 16;
foreach (System.Reflection.PropertyInfo p in objectData.GetType().GetProperties())
{
cells++;
var regular = regulars.Find(t => t.PropertyName == p.Name);
var messageCell = messageRow.CreateCell(cells);
var value = p.GetValue(objectData);
if (value == null)
{
messageCell.SetCellValue("");
}
else
{
switch (regular.DataType)
{
case "datetime":
if (Convert.ToDateTime(value) == DateTime.MinValue)
{
messageCell.SetCellValue("");
}
else
{
messageCell.SetCellValue(
Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));
}
break;
case "int":
messageCell.SetCellValue(Convert.ToInt32(value));
break;
case "double":
messageCell.SetCellValue(Convert.ToDouble(value));
break;
case "bool":
var setValue = "是";
if (!(bool)value)
{
setValue = "否";
}
messageCell.SetCellValue(setValue);
break;
default:
messageCell.SetCellValue(value.ToString());
break;
}
}
messageCell.CellStyle = styleMessage;
}
}
}
public void TestBug55752()
{
IWorkbook wb = new XSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet("test");
for (int i = 0; i < 4; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < 2; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = (wb.CreateCellStyle());
}
}
// set content
IRow row1 = sheet.GetRow(0);
row1.GetCell(0).SetCellValue("AAA");
IRow row2 = sheet.GetRow(1);
row2.GetCell(0).SetCellValue("BBB");
IRow row3 = sheet.GetRow(2);
row3.GetCell(0).SetCellValue("CCC");
IRow row4 = sheet.GetRow(3);
row4.GetCell(0).SetCellValue("DDD");
// merge cells
CellRangeAddress range1 = new CellRangeAddress(0, 0, 0, 1);
sheet.AddMergedRegion(range1);
CellRangeAddress range2 = new CellRangeAddress(1, 1, 0, 1);
sheet.AddMergedRegion(range2);
CellRangeAddress range3 = new CellRangeAddress(2, 2, 0, 1);
sheet.AddMergedRegion(range3);
Assert.AreEqual(0, range3.FirstColumn);
Assert.AreEqual(1, range3.LastColumn);
Assert.AreEqual(2, range3.LastRow);
CellRangeAddress range4 = new CellRangeAddress(3, 3, 0, 1);
sheet.AddMergedRegion(range4);
// set border
RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range1, sheet, wb);
row2.GetCell(0).CellStyle.BorderBottom = BorderStyle.Thin;
row2.GetCell(1).CellStyle.BorderBottom = BorderStyle.Thin;
ICell cell0 = CellUtil.GetCell(row3, 0);
CellUtil.SetCellStyleProperty(cell0, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
ICell cell1 = CellUtil.GetCell(row3, 1);
CellUtil.SetCellStyleProperty(cell1, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range4, sheet, wb);
// write to file
Stream stream = new FileStream("55752.xlsx", FileMode.Create, FileAccess.ReadWrite);
try
{
wb.Write(stream);
}
finally
{
stream.Close();
}
}
finally
{
wb.Close();
}
}
void GenerateData(GridView grid)
{
//2015-07-07 修改发现两者的对应关系不一致
ISheet sheet1 = hssfworkbook.CreateSheet("拣货明细");
//增加总箱数
DataTable dt = ((DataSet)grid.DataSource).Tables[0];
dt.Rows.Add();
dt.Rows[dt.Rows.Count - 1]["boxNum"] = dt.Compute("Sum(boxNum)", string.Empty);
dt.Rows[dt.Rows.Count - 1]["num"] = dt.Compute("Sum(num)", string.Empty);
List<string[]> ColumnsValues = new List<string[]>();
for (int i = 0; i < grid.Columns.Count; i++)
{
if (grid.Columns[i].HeaderText != "")
{
string[] values = new string[2];
values[0] = grid.Columns[i].HeaderText;
values[1] = (grid.Columns[i].HeaderText.Length + 10).ToString();
ColumnsValues.Add(values);
}
}
for (int i = 0; i < ColumnsValues.Count; i++)
{
sheet1.SetColumnWidth(i, int.Parse(ColumnsValues[i][1]) * 256); //设置列宽，20个字符宽度。宽度参数为1/256，故乘以256
}
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
//第二步 设定中文字体
IFont font2 = hssfworkbook.CreateFont();
font2.IsStrikeout = false;
font2.FontHeightInPoints = 14;
font2.Boldweight = (short)FontBoldWeight.Bold;
font2.FontName = "宋体";
//第三部设定边框信息
ICellStyle style4 = hssfworkbook.CreateCellStyle();
style4.SetFont(font2);
style4.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style4.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style4.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style4.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//第四步 标题列设定
IFont font3 = hssfworkbook.CreateFont();
font3.Color = HSSFColor.Blue.Index;
font3.IsStrikeout = false;
font3.FontHeightInPoints = 14;
font3.Boldweight = (short)FontBoldWeight.Bold;
font3.FontName = "宋体";
ICellStyle style3 = hssfworkbook.CreateCellStyle();
style3.SetFont(font3);
//------增加备注和备货单号的显示 备货单号的显示 将第一行进行合并显示---------------------------------------
IRow row = null;
row = sheet1.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue(mark.Text+" "+stockUpBillNo.Text);
for (int i = 0; i < row.Cells.Count; i++)
{
row.Cells[i].CellStyle = style3;
}
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, ColumnsValues.Count - 1);
sheet1.AddMergedRegion(cellRangeAddress);
//列名称
row = sheet1.CreateRow(1);
for (int i = 0; i < ColumnsValues.Count; i++)
{
row.CreateCell(i, CellType.String).SetCellValue(ColumnsValues[i][0]);
}
for (int i = 0; i < row.Cells.Count; i++)
{
row.Cells[i].CellStyle = style3;
}
//-------------------修改为从第二列开始显示数据------------------
//-------------------因为未知原因，需要将相应的列的关联关系手动进行修改
//-------------------使用字典将两者的关系建立起来----------------------
//Dictionary<string, string> columnBidings = new Dictionary<string, string>();
//columnBidings.Add("托盘序号",dt.Columns[0].ColumnName);
int rowStart = 2;
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet1.CreateRow(i + rowStart);
row.CreateCell(0, CellType.String).SetCellValue(dt.Rows[i]["palletIndex"].ToString());//托盘序号
row.CreateCell(1, CellType.String).SetCellValue(dt.Rows[i]["saleBillNo"].ToString());//托盘序号
row.CreateCell(2, CellType.String).SetCellValue(dt.Rows[i]["lineNum"].ToString());//托盘序号
row.CreateCell(3, CellType.String).SetCellValue(dt.Rows[i]["absEntry"].ToString());//托盘序号
row.CreateCell(4, CellType.String).SetCellValue(dt.Rows[i]["financeBillNo"].ToString());//托盘序号
row.CreateCell(5, CellType.String).SetCellValue(dt.Rows[i]["materialNo"].ToString());//托盘序号
row.CreateCell(6, CellType.String).SetCellValue(dt.Rows[i]["materialDesc"].ToString());//托盘序号
row.CreateCell(7, CellType.String).SetCellValue(dt.Rows[i]["U_Material"].ToString());//托盘序号
row.CreateCell(8, CellType.String).SetCellValue(dt.Rows[i]["U_PackWay"].ToString());//托盘序号
row.CreateCell(9, CellType.String).SetCellValue(dt.Rows[i]["U_UintQty"].ToString());//托盘序号
row.CreateCell(10, CellType.String).SetCellValue(dt.Rows[i]["U_BoxQty"].ToString());//托盘序号
row.CreateCell(11, CellType.String).SetCellValue(dt.Rows[i]["num"].ToString());//托盘序号
row.CreateCell(12, CellType.String).SetCellValue(dt.Rows[i]["boxNum"].ToString());//托盘序号
for (int j = 0; j < row.Cells.Count; j++)
{
row.Cells[j].CellStyle = style4;
}
}
}
private void ValidateArrayFormulas(CellRangeAddress region)
{
int firstRow = region.FirstRow;
int firstColumn = region.FirstColumn;
int lastRow = region.LastRow;
int lastColumn = region.LastColumn;
for (int rowIn = firstRow; rowIn <= lastRow; rowIn++)
{
for (int colIn = firstColumn; colIn <= lastColumn; colIn++)
{
HSSFRow row = (HSSFRow)GetRow(rowIn);
if (row == null) continue;
HSSFCell cell = (HSSFCell)row.GetCell(colIn);
if (cell == null) continue;
if (cell.IsPartOfArrayFormulaGroup)
{
CellRangeAddress arrayRange = cell.ArrayFormulaRange;
if (arrayRange.NumberOfCells > 1 &&
(arrayRange.IsInRange(region.FirstRow, region.FirstColumn) ||
arrayRange.IsInRange(region.FirstRow, region.FirstColumn)))
{
String msg = "The range " + region.FormatAsString() + " intersects with a multi-cell array formula. " +
"You cannot merge cells of an array.";
throw new InvalidOperationException(msg);
}
}
}
}
}
public void TestCreateBorderFormatting()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, "7");
IBorderFormatting borderFmt = rule1.CreateBorderFormatting();
Assert.AreEqual(BorderStyle.None, borderFmt.BorderBottom);
borderFmt.BorderBottom = (/*setter*/ BorderStyle.Dotted);
Assert.AreEqual(BorderStyle.Dotted, borderFmt.BorderBottom);
borderFmt.BorderBottom = (/*setter*/ BorderStyle.None);
Assert.AreEqual(BorderStyle.None, borderFmt.BorderBottom);
borderFmt.BorderBottom = (/*setter*/ BorderStyle.Thick);
Assert.AreEqual(BorderStyle.Thick, borderFmt.BorderBottom);
Assert.AreEqual(BorderStyle.None, borderFmt.BorderTop);
borderFmt.BorderTop = (/*setter*/ BorderStyle.Dotted);
Assert.AreEqual(BorderStyle.Dotted, borderFmt.BorderTop);
borderFmt.BorderTop = (/*setter*/ BorderStyle.None);
Assert.AreEqual(BorderStyle.None, borderFmt.BorderTop);
borderFmt.BorderTop = (/*setter*/ BorderStyle.Thick);
Assert.AreEqual(BorderStyle.Thick, borderFmt.BorderTop);
Assert.AreEqual(BorderStyle.None, borderFmt.BorderLeft);
borderFmt.BorderLeft = (/*setter*/ BorderStyle.Dotted);
Assert.AreEqual(BorderStyle.Dotted, borderFmt.BorderLeft);
borderFmt.BorderLeft = (/*setter*/ BorderStyle.None);
Assert.AreEqual(BorderStyle.None, borderFmt.BorderLeft);
borderFmt.BorderLeft = (/*setter*/ BorderStyle.Thin);
Assert.AreEqual(BorderStyle.Thin, borderFmt.BorderLeft);
Assert.AreEqual(BorderStyle.None, borderFmt.BorderRight);
borderFmt.BorderRight = (/*setter*/ BorderStyle.Dotted);
Assert.AreEqual(BorderStyle.Dotted, borderFmt.BorderRight);
borderFmt.BorderRight = (/*setter*/ BorderStyle.None);
Assert.AreEqual(BorderStyle.None, borderFmt.BorderRight);
borderFmt.BorderRight = (/*setter*/ BorderStyle.Hair);
Assert.AreEqual(BorderStyle.Hair, borderFmt.BorderRight);
IConditionalFormattingRule[] cfRules = { rule1 };
CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:A5") };
sheetCF.AddConditionalFormatting(regions, cfRules);
// Verification
IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0);
Assert.IsNotNull(cf);
Assert.AreEqual(1, cf.NumberOfRules);
IBorderFormatting r1fp = cf.GetRule(0).GetBorderFormatting();
Assert.IsNotNull(r1fp);
Assert.AreEqual(BorderStyle.Thick, r1fp.BorderBottom);
Assert.AreEqual(BorderStyle.Thick, r1fp.BorderTop);
Assert.AreEqual(BorderStyle.Thin, r1fp.BorderLeft);
Assert.AreEqual(BorderStyle.Hair, r1fp.BorderRight);
}
private static void CopyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, IDictionary<Int32, HSSFCellStyle> styleMap, Dictionary<short, short> paletteMap, bool keepFormulas)
{
List<SS.Util.CellRangeAddress> mergedRegions = destSheet.Sheet.MergedRecords.MergedRegions;
destRow.Height = srcRow.Height;
destRow.IsHidden = srcRow.IsHidden;
destRow.RowRecord.OptionFlags = srcRow.RowRecord.OptionFlags;
for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++)
{
HSSFCell oldCell = (HSSFCell)srcRow.GetCell(j);
HSSFCell newCell = (HSSFCell)destRow.GetCell(j);
if (srcSheet.Workbook == destSheet.Workbook)
{
newCell = (HSSFCell)destRow.GetCell(j);
}
if (oldCell != null)
{
if (newCell == null)
{
newCell = (HSSFCell)destRow.CreateCell(j);
}
HSSFCellUtil.CopyCell(oldCell, newCell, styleMap, paletteMap, keepFormulas);
CellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum, (short)oldCell.ColumnIndex);
if (mergedRegion != null)
{
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
if (IsNewMergedRegion(newMergedRegion, mergedRegions))
{
mergedRegions.Add(newMergedRegion);
}
}
}
}
}
private static bool IsNewMergedRegion(CellRangeAddress newMergedRegion, List<CellRangeAddress> mergedRegions)
{
bool isNew = true;
// we want to check if newMergedRegion is contained inside our collection
foreach (CellRangeAddress add in mergedRegions)
{
bool r1 = (add.FirstRow == newMergedRegion.FirstRow);
bool r2 = (add.LastRow == newMergedRegion.LastRow);
bool c1 = (add.FirstColumn == newMergedRegion.FirstColumn);
bool c2 = (add.LastColumn == newMergedRegion.LastColumn);
if (AreAllTrue(r1, r2, c1, c2))
{
isNew = false;
}
}
return isNew;
}
private void SetRepeatingRowsAndColumns(
CellRangeAddress rowDef, CellRangeAddress colDef)
{
int sheetIndex = _workbook.GetSheetIndex(this);
int maxRowIndex = SpreadsheetVersion.EXCEL97.LastRowIndex;
int maxColIndex = SpreadsheetVersion.EXCEL97.LastColumnIndex;
int col1 = -1;
int col2 = -1;
int row1 = -1;
int row2 = -1;
if (rowDef != null)
{
row1 = rowDef.FirstRow;
row2 = rowDef.LastRow;
if ((row1 == -1 && row2 != -1) || (row1 > row2)
|| (row1 < 0 || row1 > maxRowIndex)
|| (row2 < 0 || row2 > maxRowIndex))
{
throw new ArgumentException("Invalid row range specification");
}
}
if (colDef != null)
{
col1 = colDef.FirstColumn;
col2 = colDef.LastColumn;
if ((col1 == -1 && col2 != -1) || (col1 > col2)
|| (col1 < 0 || col1 > maxColIndex)
|| (col2 < 0 || col2 > maxColIndex))
{
throw new ArgumentException("Invalid column range specification");
}
}
short externSheetIndex =
(short)_workbook.Workbook.CheckExternSheet(sheetIndex);
bool setBoth = rowDef != null && colDef != null;
bool removeAll = rowDef == null && colDef == null;
HSSFName name = _workbook.GetBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex);
if (removeAll)
{
if (name != null)
{
_workbook.RemoveName(name);
}
return;
}
if (name == null)
{
name = _workbook.CreateBuiltInName(
NameRecord.BUILTIN_PRINT_TITLE, sheetIndex);
}
List<Ptg> ptgList = new List<Ptg>();
if (setBoth)
{
int exprsSize = 2 * 11 + 1; // 2 * Area3DPtg.SIZE + UnionPtg.SIZE
ptgList.Add(new MemFuncPtg(exprsSize));
}
if (colDef != null)
{
Area3DPtg colArea = new Area3DPtg(0, maxRowIndex, col1, col2,
false, false, false, false, externSheetIndex);
ptgList.Add(colArea);
}
if (rowDef != null)
{
Area3DPtg rowArea = new Area3DPtg(row1, row2, 0, maxColIndex,
false, false, false, false, externSheetIndex);
ptgList.Add(rowArea);
}
if (setBoth)
{
ptgList.Add(UnionPtg.instance);
}
Ptg[] ptgs = ptgList.ToArray();
//ptgList.toArray(ptgs);
name.SetNameDefinition(ptgs);
HSSFPrintSetup printSetup = (HSSFPrintSetup)PrintSetup;
printSetup.ValidSettings = (false);
SetActive(true);
}
private CellRangeAddress GetRepeatingRowsOrColums(bool rows)
{
NameRecord rec = GetBuiltinNameRecord(NameRecord.BUILTIN_PRINT_TITLE);
if (rec == null)
{
return null;
}
Ptg[] nameDefinition = rec.NameDefinition;
if (rec.NameDefinition == null)
{
return null;
}
int maxRowIndex = SpreadsheetVersion.EXCEL97.LastRowIndex;
int maxColIndex = SpreadsheetVersion.EXCEL97.LastColumnIndex;
foreach (Ptg ptg in nameDefinition)
{
if (ptg is Area3DPtg)
{
Area3DPtg areaPtg = (Area3DPtg)ptg;
if (areaPtg.FirstColumn == 0
&& areaPtg.LastColumn == maxColIndex)
{
if (rows)
{
CellRangeAddress rowRange = new CellRangeAddress(
areaPtg.FirstRow, areaPtg.LastRow, -1, -1);
return rowRange;
}
}
else if (areaPtg.FirstRow == 0
&& areaPtg.LastRow == maxRowIndex)
{
if (!rows)
{
CellRangeAddress columnRange = new CellRangeAddress(-1, -1,
areaPtg.FirstColumn, areaPtg.LastColumn);
return columnRange;
}
}
}
}
return null;
}
/// <summary>
/// Checks if the provided region is part of the merged regions.
/// </summary>
/// <param name="mergedRegion">Region searched in the merged regions</param>
/// <returns><c>true</c>, when the region is contained in at least one of the merged regions</returns>
public bool IsMergedRegion(CellRangeAddress mergedRegion)
{
foreach (CellRangeAddress range in _sheet.MergedRecords.MergedRegions)
{
if (range.FirstColumn <= mergedRegion.FirstColumn
&& range.LastColumn >= mergedRegion.LastColumn
&& range.FirstRow <= mergedRegion.FirstRow
&& range.LastRow >= mergedRegion.LastRow)
{
return true;
}
}
return false;
}
/// <summary>
/// Enable filtering for a range of cells
/// </summary>
/// <param name="range">the range of cells to filter</param>
public IAutoFilter SetAutoFilter(CellRangeAddress range)
{
InternalWorkbook workbook = _workbook.Workbook;
int sheetIndex = _workbook.GetSheetIndex(this);
NameRecord name = workbook.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1);
if (name == null)
{
name = workbook.CreateBuiltInName(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1);
}
int firstRow = range.FirstRow;
// if row was not given when constructing the range...
if (firstRow == -1)
{
firstRow = 0;
}
// The built-in name must consist of a single Area3d Ptg.
Area3DPtg ptg = new Area3DPtg(firstRow, range.LastRow,
range.FirstColumn, range.LastColumn,
false, false, false, false, sheetIndex);
name.NameDefinition = (new Ptg[] { ptg });
AutoFilterInfoRecord r = new AutoFilterInfoRecord();
// the number of columns that have AutoFilter enabled.
int numcols = 1 + range.LastColumn - range.FirstColumn;
r.NumEntries = (short)numcols;
int idx = _sheet.FindFirstRecordLocBySid(DimensionsRecord.sid);
_sheet.Records.Insert(idx, r);
//create a combobox control for each column
HSSFPatriarch p = (HSSFPatriarch)CreateDrawingPatriarch();
for (int col = range.FirstColumn; col <= range.LastColumn; col++)
{
p.CreateComboBox(new HSSFClientAnchor(0, 0, 0, 0,
(short)col, firstRow, (short)(col + 1), firstRow + 1));
}
return new HSSFAutoFilter(this);
}
/// <summary>
/// Sets array formula to specified region for result.
/// </summary>
/// <param name="formula">text representation of the formula</param>
/// <param name="range">Region of array formula for result</param>
/// <returns>the <see cref="ICellRange{ICell}"/> of cells affected by this change</returns>
public ICellRange<ICell> SetArrayFormula(String formula, CellRangeAddress range)
{
// make sure the formula parses OK first
int sheetIndex = _workbook.GetSheetIndex(this);
Ptg[] ptgs = HSSFFormulaParser.Parse(formula, _workbook, FormulaType.Array, sheetIndex);
ICellRange<ICell> cells = GetCellRange(range);
foreach (HSSFCell c in cells)
{
c.SetCellArrayFormula(range);
}
HSSFCell mainArrayFormulaCell = (HSSFCell)cells.TopLeftCell;
FormulaRecordAggregate agg = (FormulaRecordAggregate)mainArrayFormulaCell.CellValueRecord;
agg.SetArrayFormula(range, ptgs);
return cells;
}
/// <summary>
/// Also creates cells if they don't exist.
/// </summary>
private ICellRange<ICell> GetCellRange(CellRangeAddress range)
{
int firstRow = range.FirstRow;
int firstColumn = range.FirstColumn;
int lastRow = range.LastRow;
int lastColumn = range.LastColumn;
int height = lastRow - firstRow + 1;
int width = lastColumn - firstColumn + 1;
List<ICell> temp = new List<ICell>(height * width);
for (int rowIn = firstRow; rowIn <= lastRow; rowIn++)
{
for (int colIn = firstColumn; colIn <= lastColumn; colIn++)
{
IRow row = GetRow(rowIn);
if (row == null)
{
row = CreateRow(rowIn);
}
ICell cell = row.GetCell(colIn);
if (cell == null)
{
cell = row.CreateCell(colIn);
}
temp.Add(cell);
}
}
return SSCellRange<ICell>.Create(firstRow, firstColumn, height, width, temp, typeof(HSSFCell));
}
/// <summary>
/// Sets the bottom border of region.
/// </summary>
/// <param name="region">The region.</param>
/// <param name="borderType">Type of the border.</param>
/// <param name="color">The color.</param>
public void SetBorderBottomOfRegion(CellRangeAddress region, NPOI.SS.UserModel.BorderStyle borderType, short color)
{
HSSFRegionUtil.SetBottomBorderColor(color, region, this, _workbook);
HSSFRegionUtil.SetBorderBottom(borderType, region, this, _workbook);
}
/// <summary>
/// Sets the enclosed border of region.
/// </summary>
/// <param name="region">The region.</param>
/// <param name="borderType">Type of the border.</param>
/// <param name="color">The color.</param>
public void SetEnclosedBorderOfRegion(CellRangeAddress region, NPOI.SS.UserModel.BorderStyle borderType, short color)
{
HSSFRegionUtil.SetRightBorderColor(color, region, this, _workbook);
HSSFRegionUtil.SetBorderRight(borderType, region, this, _workbook);
HSSFRegionUtil.SetLeftBorderColor(color, region, this, _workbook);
HSSFRegionUtil.SetBorderLeft(borderType, region, this, _workbook);
HSSFRegionUtil.SetTopBorderColor(color, region, this, _workbook);
HSSFRegionUtil.SetBorderTop(borderType, region, this, _workbook);
HSSFRegionUtil.SetBottomBorderColor(color, region, this, _workbook);
HSSFRegionUtil.SetBorderBottom(borderType, region, this, _workbook);
}
//[Obsolete]
//public static void SetBorderLeft(NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet,
// HSSFWorkbook workbook)
//{
// SetBorderLeft(border, toCRA(region), sheet, workbook);
//}
/// <summary>
/// Sets the left border for a region of cells by manipulating the cell style
/// of the individual cells on the left
/// </summary>
/// <param name="border">The new border</param>
/// <param name="region">The region that should have the border</param>
/// <param name="sheet">The sheet that the region is on.</param>
/// <param name="workbook">The workbook that the region is on.</param>
public static void SetBorderLeft(NPOI.SS.UserModel.CellBorderType border, CellRangeAddress region, HSSFSheet sheet,
HSSFWorkbook workbook)
{
int rowStart = region.FirstRow;
int rowEnd = region.LastRow;
int column = region.FirstColumn;
CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_LEFT, (int)border);
for (int i = rowStart; i <= rowEnd; i++)
{
cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column);
}
}
