protected void ApplyPercentageFormatting(int column, int rowFromInclusive, int rowtoInclusive)
{
string colString = CellReference.ConvertNumToColString(column);
string range = String.Format("{0}{1}:{0}{2}", colString, rowFromInclusive, rowtoInclusive);
var region = new CellRangeAddress[] { CellRangeAddress.ValueOf(range) };
sheet.SheetConditionalFormatting.AddConditionalFormatting(region, PercentageFormattingRules);
}
private void CreateAdresseArmoire()
{
var merge = new CellRangeAddress(6, 7, 1, 5);
_cadreCreator.Create(6, 1, 7, 5, new byte[3] { 255, 255, 255 }, BorderStyle.Medium, merge, true, true);
_sheet.GetRow(6).GetCell(1).SetCellValue(" - 69400 VILLEFRANCHE-SUR-SAONE");
}
public void Test_47701()
{
byte[] data = HexRead.ReadFromString(
"15, 00, 12, 00, 12, 00, 02, 00, 11, 20, " +
"00, 00, 00, 00, 80, 3D, 03, 05, 00, 00, " +
"00, 00, 0C, 00, 14, 00, 00, 00, 00, 00, " +
"00, 00, 00, 00, 00, 00, 01, 00, 0A, 00, " +
"00, 00, 10, 00, 01, 00, 13, 00, EE, 1F, " +
"10, 00, 09, 00, 40, 9F, 74, 01, 25, 09, " +
"00, 0C, 00, 07, 00, 07, 00, 07, 04, 00, " +
"00, 00, 08, 00, 00, 00");
RecordInputStream in1 = TestcaseRecordInputStream.Create(ObjRecord.sid, data);
// check read OK
ObjRecord record = new ObjRecord(in1);
Assert.AreEqual(3, record.SubRecords.Count);
SubRecord sr = record.SubRecords[(2)];
Assert.IsTrue(sr is LbsDataSubRecord);
LbsDataSubRecord lbs = (LbsDataSubRecord)sr;
Assert.AreEqual(4, lbs.NumberOfItems);
Assert.IsTrue(lbs.Formula is AreaPtg);
AreaPtg ptg = (AreaPtg)lbs.Formula;
CellRangeAddress range = new CellRangeAddress(
ptg.FirstRow, ptg.LastRow, ptg.FirstColumn, ptg.LastColumn);
Assert.AreEqual("H10:H13", range.FormatAsString());
// check that it re-Serializes to the same data
byte[] ser = record.Serialize();
TestcaseRecordInputStream.ConfirmRecordEncoding(ObjRecord.sid, data, ser);
}
/**
* Intersect this range with the specified range.
*
* @param crB - the specified range
* @return code which reflects how the specified range is related to this range.<br/>
* Possible return codes are:
* NO_INTERSECTION - the specified range is outside of this range;<br/>
* OVERLAP - both ranges partially overlap;<br/>
* INSIDE - the specified range is inside of this one<br/>
* ENCLOSES - the specified range encloses (possibly exactly the same as) this range<br/>
*/
public static int Intersect(CellRangeAddress crA, CellRangeAddress crB)
{
int firstRow = crB.FirstRow;
int lastRow = crB.LastRow;
int firstCol = crB.FirstColumn;
int lastCol = crB.LastColumn;
if
(
gt(crA.FirstRow, lastRow) ||
lt(crA.LastRow, firstRow) ||
gt(crA.FirstColumn, lastCol) ||
lt(crA.LastColumn, firstCol)
)
{
return NO_INTERSECTION;
}
else if (Contains(crA, crB))
{
return INSIDE;
}
else if (Contains(crB, crA))
{
return ENCLOSES;
}
else
{
return OVERLAP;
}
}
private void CreateAdresse()
{
var merge = new CellRangeAddress(6, 7, BeginCadreInformation, BeginCadreInformation + LongeurCadre);
_cadreCreator.Create(6, BeginCadreInformation, 7, BeginCadreInformation + LongeurCadre, new byte[3] { 255, 255, 255 }, BorderStyle.Medium, merge, true, true);
_sheet.GetRow(6).GetCell(BeginCadreInformation).SetCellValue(" - 69400 VILLEFRANCHE-SUR-SAONE");
}
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;
}
}
static void Main(string[] args)
{
InitializeWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("new sheet");
IRow row = sheet.CreateRow(0);
row.HeightInPoints = 30;
ICell cell = row.CreateCell(0);
//set the title of the sheet
cell.SetCellValue("Sales Report");
ICellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
//create a font style
IFont font = hssfworkbook.CreateFont();
font.FontHeight = 20*20;
style.SetFont(font);
cell.CellStyle = style;
//merged cells on single row
//ATTENTION: don't use Region class, which is obsolete
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));
//merged cells on mutiple rows
CellRangeAddress region = new CellRangeAddress(2, 4, 2, 4);
sheet.AddMergedRegion(region);
//set enclosed border for the merged region
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.DOTTED, NPOI.HSSF.Util.HSSFColor.RED.index);
WriteToFile();
}
private void CreateRow(IEnumerable<Cell> row, Row currentRow)
{
int columnOrdinal = 0;
foreach (var cell in row)
{
if (cell.ColumnSpan > 1)
{
int rangeStartColumn = columnOrdinal;
for (int i = 0; i < cell.ColumnSpan; i++)
{
NPOI.SS.UserModel.Cell current = CreateCell(cell, currentRow, columnOrdinal);
if (i == 0) current.SetCellValue(cell.Value);
columnOrdinal++;
}
var cra = new CellRangeAddress(currentRow.RowNum, currentRow.RowNum, rangeStartColumn,
rangeStartColumn + (cell.ColumnSpan - 1));
_sheet.AddMergedRegion(cra);
}
else
{
CreateCell(cell, currentRow, columnOrdinal).SetCellValue(cell.Value);
columnOrdinal++;
}
}
}
public CFHeaderRecord(CellRangeAddress[] regions, int nRules)
{
CellRangeAddress[] unmergedRanges = regions;
CellRangeAddress[] mergeCellRanges = CellRangeUtil.MergeCellRanges(unmergedRanges);
CellRanges= mergeCellRanges;
field_1_numcf = nRules;
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sh">工作簿</param>
/// <param name="firstRow">开始行号</param>
/// <param name="lastRow">结束行号</param>
/// <param name="firstCol">开始列号</param>
/// <param name="lastCol">结束列号</param>
/// <returns></returns>
public static NPOI.SS.Util.CellRangeAddress CellRange(ISheet sh, int firstRow, int lastRow, int firstCol, int lastCol)
{
NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sh.AddMergedRegion(region);
return(region);
}
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);
}
/**
* Check if the specified range is located inside of this cell range.
*
* @param crB
* @return true if this cell range Contains the argument range inside if it's area
*/
public static bool Contains(CellRangeAddress crA, CellRangeAddress crB)
{
int firstRow = crB.FirstRow;
int lastRow = crB.LastRow;
int firstCol = crB.FirstColumn;
int lastCol = crB.LastColumn;
return le(crA.FirstRow, firstRow) && ge(crA.LastRow, lastRow)
&& le(crA.FirstColumn, firstCol) && ge(crA.LastColumn, lastCol);
}
private void CreateAdresseChambrePB(ref int hauteur)
{
var bleu = new byte[3] { 153, 204, 255 };
var endHauteur = hauteur + 1;
var merge = new CellRangeAddress(hauteur, endHauteur, 20, 23);
_cadreCreator.Create(hauteur, 20, endHauteur, 23, bleu, BorderStyle.Medium, merge, true);
_sheet.GetRow(hauteur).GetCell(20).SetCellValue(" - 69400 VILLEFRANCHE-SUR-SAONE");
hauteur = hauteur + 2;
}
public void TestLoad()
{
CellRangeAddress cref = new CellRangeAddress(
TestcaseRecordInputStream.Create(0x000, data)
);
Assert.AreEqual(2, cref.FirstRow);
Assert.AreEqual(4, cref.LastRow);
Assert.AreEqual(0, cref.FirstColumn);
Assert.AreEqual(3, cref.LastColumn);
Assert.AreEqual(8, CellRangeAddress.ENCODED_SIZE);
}
/**
* 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;
}
public void Render(ICell cell)
{
NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell);
ISheet sheet = cell.Sheet;
IDrawing draw = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
IClientAnchor anchor = region != null?
draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
PicArea != null?
draw.CreateAnchor(20, 20, 0, 0, PicArea.ColIndex, PicArea.RowIndex, PicArea.ColIndex + PicArea.ColCount, PicArea.RowIndex + PicArea.RowCount) :
draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
draw.CreatePicture(anchor, sheet.Workbook.AddPicture(PicSource, PictureType.JPEG));
}
//[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);
}
}
/**
* 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);
}
}
public static CellRangeAddress MergeCellsRange(this ISheet worksheet, int firstRow, int lastRow, string firstColumn, string lastColumn)
{
if (worksheet.LastRowNum < lastRow)
{
worksheet.CreateRow(lastRow);
}
int iFirstColumn = firstColumn.ToCharArray().Select(c => c - 'A' + 1).Reverse().Select((v, i) => v * (int)Math.Pow(26, i)).Sum() - 1;
int iLastColumn = lastColumn.ToCharArray().Select(c => c - 'A' + 1).Reverse().Select((v, i) => v * (int)Math.Pow(26, i)).Sum() - 1;
var cra1 = new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, iFirstColumn, iLastColumn);
worksheet.AddMergedRegion(cra1);
return(cra1);
}
public void Create(
byte[] color,
int line,
int beginLongeurCadre,
int endLongeurCadre,
string[] values,
bool isMerged,
BorderStyle tailleBorder,
bool withCadre = true,
byte[] fontColor = null)
{
if(fontColor == null)
{
fontColor = new byte[3] { 0, 0, 0 };
}
_tailleBorder = tailleBorder;
var merged = new CellRangeAddress(line, line, beginLongeurCadre, endLongeurCadre);
var longeur = beginLongeurCadre;
foreach (var value in values)
{
var cell = _sheet.GetRow(line).GetCell(longeur);
var style = CreateStyle(color, fontColor);
if (withCadre)
{
SetStyleWithCadre(longeur, beginLongeurCadre, endLongeurCadre, style);
}
else
{
SetStyleWithoutCadre(longeur, beginLongeurCadre, endLongeurCadre, style);
}
cell.CellStyle = style;
if (isMerged)
{
_sheet.AddMergedRegion(merged);
}
cell.SetCellValue(value);
longeur++;
}
}
/**
* Create an enclosing CellRange for the two cell ranges.
*
* @return enclosing CellRange
*/
public static CellRangeAddress CreateEnclosingCellRange(CellRangeAddress crA, CellRangeAddress crB)
{
if (crB == null)
{
return crA.Copy();
}
return
new CellRangeAddress(
lt(crB.FirstRow, crA.FirstRow) ? crB.FirstRow : crA.FirstRow,
gt(crB.LastRow, crA.LastRow) ? crB.LastRow : crA.LastRow,
lt(crB.FirstColumn, crA.FirstColumn) ? crB.FirstColumn : crA.FirstColumn,
gt(crB.LastColumn, crA.LastColumn) ? crB.LastColumn : crA.LastColumn
);
}
public void Create(
int beginHauteurCadre,
int beginLongeurCadre,
int endHauteurCadre,
int endLongeurCadre,
byte[] color,
BorderStyle tailleBorder,
CellRangeAddress merge = null,
bool wrapText = false,
bool center = false,
bool forcedRightBorder = false)
{
_tailleBorder = tailleBorder;
if (merge != null)
{
_sheet.AddMergedRegion(merge);
}
for (var hauteur = beginHauteurCadre; hauteur <= endHauteurCadre; hauteur++)
{
var row = _sheet.GetRow(hauteur);
if (row == null)
{
row = _sheet.CreateRow(hauteur);
}
for (var longeur = beginLongeurCadre; longeur <= endLongeurCadre; longeur++)
{
var cell = row.CreateCell(longeur);
var style = CreateStyle(color);
SetStyle(hauteur, longeur, beginHauteurCadre, beginLongeurCadre, endHauteurCadre, endLongeurCadre, style, forcedRightBorder);
style.WrapText = wrapText;
if (center)
{
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
}
cell.CellStyle = style;
}
}
}
public void testMCTable_bug46009()
{
MergedCellsTable mct = new MergedCellsTable();
ArrayList recList = new ArrayList();
CellRangeAddress[] cras = new CellRangeAddress[] {
new CellRangeAddress(0, 0, 0, 3),
};
recList.Add(new MergeCellsRecord(cras, 0, 1));
RecordStream rs = new RecordStream(recList, 0);
mct.Read(rs);
try
{
mct.VisitContainedRecords(dummyRecordVisitor);
}
catch (Exception)
{
throw new AssertionException("Identified bug 46009");
}
}
public void TestStore()
{
CellRangeAddress cref = new CellRangeAddress(0, 0, 0, 0);
byte[] recordBytes;
//ByteArrayOutputStream baos = new ByteArrayOutputStream();
MemoryStream baos = new MemoryStream();
LittleEndianOutputStream output = new LittleEndianOutputStream(baos);
try
{
// With nothing set
cref.Serialize(output);
recordBytes = baos.ToArray();
Assert.AreEqual(recordBytes.Length, data.Length);
for (int i = 0; i < data.Length; i++)
{
Assert.AreEqual(0, recordBytes[i], "At offset " + i);
}
// Now set the flags
cref.FirstRow = ((short)2);
cref.LastRow = ((short)4);
cref.FirstColumn = ((short)0);
cref.LastColumn = ((short)3);
// Re-test
//baos.reset();
baos.Seek(0, SeekOrigin.Begin);
cref.Serialize(output);
recordBytes = baos.ToArray();
Assert.AreEqual(recordBytes.Length, data.Length);
for (int i = 0; i < data.Length; i++)
{
Assert.AreEqual(data[i], recordBytes[i], "At offset " + i);
}
}
finally
{
//output.Close();
}
}
public void TestHSSFSetArrayFormula_SingleCell()
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2);
ICell[] cells = sheet.SetArrayFormula("SUM(C11:C12*D11:D12)", range).FlattenedCells;
Assert.AreEqual(1, cells.Length);
// sheet.SetArrayFormula Creates rows and cells for the designated range
Assert.IsNotNull(sheet.GetRow(2));
ICell cell = sheet.GetRow(2).GetCell(2);
Assert.IsNotNull(cell);
Assert.IsTrue(cell.IsPartOfArrayFormulaGroup);
//retrieve the range and check it is the same
Assert.AreEqual(range.FormatAsString(), cell.GetArrayFormulaRange().FormatAsString());
FormulaRecordAggregate agg = (FormulaRecordAggregate)(((HSSFCell)cell).CellValueRecord);
Assert.AreEqual(range.FormatAsString(), agg.GetArrayFormulaRange().FormatAsString());
Assert.IsTrue(agg.IsPartOfArrayFormula);
}
public static int MergeTo(this ICell self, ICell target)
{
int firstRow = self.RowIndex;
int lastRow = target.RowIndex;
int firstCol = self.ColumnIndex;
int lastCol = target.ColumnIndex;
if (self.RowIndex > target.RowIndex)
{
firstRow = target.RowIndex;
lastRow = self.RowIndex;
}
if (self.ColumnIndex > target.ColumnIndex)
{
firstCol = target.ColumnIndex;
lastCol = self.ColumnIndex;
}
CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
return self.Sheet.AddMergedRegion(cellRangeAddress);
}
private static CellRangeAddress[] ToArray(ArrayList temp)
{
CellRangeAddress[] result = new CellRangeAddress[temp.Count];
result = (CellRangeAddress[])temp.ToArray(typeof(CellRangeAddress));
return result;
}
// TODO - write junit test for this
static CellRangeAddress[] ResolveRangeOverlap(CellRangeAddress rangeA, CellRangeAddress rangeB)
{
if (rangeA.IsFullColumnRange)
{
if (rangeA.IsFullRowRange)
{
// Excel seems to leave these unresolved
return null;
}
return SliceUp(rangeA, rangeB);
}
if (rangeA.IsFullRowRange)
{
if (rangeB.IsFullColumnRange)
{
// Excel seems to leave these unresolved
return null;
}
return SliceUp(rangeA, rangeB);
}
if (rangeB.IsFullColumnRange)
{
return SliceUp(rangeB, rangeA);
}
if (rangeB.IsFullRowRange)
{
return SliceUp(rangeB, rangeA);
}
return SliceUp(rangeA, rangeB);
}
/**
* Do all possible cell merges between cells of the list so that:
* if a cell range is completely inside of another cell range, it s removed from the list
* if two cells have a shared border, merge them into one bigger cell range
* @param cellRangeList
* @return updated List of cell ranges
*/
public static CellRangeAddress[] MergeCellRanges(CellRangeAddress[] cellRanges)
{
if (cellRanges.Length < 1)
{
return cellRanges;
}
ArrayList temp = MergeCellRanges(NPOI.Util.Arrays.AsList(cellRanges));
return ToArray(temp);
}
private MemoryStream ListToStream <T>(IList <T> list, IDictionary <string, string> propertyNameList, string title)
{
//创建内存流
using (MemoryStream ms = new MemoryStream())
{
//将控制excel表头的参数写入到一个临时集合
//List<IDictionary<string, string>> propertyNameList = new List<IDictionary<string, string>>();
//if (propertyName != null)
//{
// propertyNameList.AddRange(propertyName);
//}
//创建NOPI对象
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
//表头的行号 如果有标题就是1 没有标题就是0
int headerRowNum = 0;
if (!string.IsNullOrEmpty(title))
{
headerRowNum = 1;
IRow titleRow = sheet.CreateRow(0);
titleRow.Height = 999;
ICell titleCell = titleRow.CreateCell(0);
titleCell.SetCellValue(title);
ICellStyle cellStyle = workbook.CreateCellStyle();
//设置字体
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 18;
font.FontName = "微软雅黑";
cellStyle.SetFont(font);
//对齐方式
cellStyle.Alignment = HorizontalAlignment.Center;
titleCell.CellStyle = cellStyle;
}
IRow headerRow = sheet.CreateRow(headerRowNum);
if (list.Count <= 0)
{
return(null);
}
//通过反射得到对象的属性集合
PropertyInfo[] propertys = list[0].GetType().GetProperties();
//遍历属性集合生成excel的表头标题
int cellIndex = 0;
if (propertyNameList == null || propertyNameList.Count == 0)
{
//如果没有传入自定义的导出表头
for (int i = 0; i < propertys.Count(); i++)
{
headerRow.CreateCell(i).SetCellValue(propertys[i].Name);
}
}
else
{
//用户自定义的
foreach (KeyValuePair <string, string> item in propertyNameList)
{
for (int i = 0; i < propertys.Count(); i++)
{
if (propertys[i].Name.Equals(item.Key))
{
headerRow.CreateCell(cellIndex).SetCellValue(item.Value);
cellIndex++;
break;
}
}
}
}
#region 面的方式可以根据传入的列的顺序导入
//for (int i = 0; i < propertys.Count(); i++)
//{
// //判断excel表头是否是用户定义
// if (propertyNameList.Count == 0)
// {
// headerRow.CreateCell(i).SetCellValue(propertys[i].Name);
// }
// else
// {
// foreach (KeyValuePair<string, string> item in propertyNameList)
// {
// if (item.Key.Equals(propertys[i].Name))
// {
// headerRow.CreateCell(cellIndex).SetCellValue(item.Value);
// cellIndex++;
// break;
// }
// }
// }
//}
#endregion
//遍历生成excel的行集数据
int rowIndex = headerRowNum + 1;
if (propertyNameList == null || propertyNameList.Count == 0)
{
for (int i = 0; i < list.Count; i++)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int j = 0; j < propertys.Count(); j++)
{
object obj = propertys[j].GetValue(list[i], null);
dataRow.CreateCell(j).SetCellValue(obj == null ? "" : obj.ToString());
}
rowIndex++;
}
}
else
{
for (int i = 0; i < list.Count; i++)
{
int cellIndex2 = 0;
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (KeyValuePair <string, string> item in propertyNameList)
{
for (int j = 0; j < propertys.Count(); j++)
{
if (item.Key.Equals(propertys[j].Name))
{
object obj = propertys[j].GetValue(list[i], null);
dataRow.CreateCell(cellIndex2).SetCellValue(obj == null ? "" : obj.ToString());
cellIndex2++;
break;
}
}
}
rowIndex++;
}
}
#region 面的方式可以根据传入的列的顺序导入
//int rowIndex = 1;
//for (int i = 0; i < list.Count; i++)
//{
// IRow dataRow = sheet.CreateRow(rowIndex);
// int cellIndex2 = 0;
// for (int j = 0; j < propertys.Count(); j++)
// {
// //指定了excel表头信息
// if (propertyNameList.Count == 0)
// {
// object obj = propertys[j].GetValue(list[i], null);
// dataRow.CreateCell(j).SetCellValue(obj == null ? "" : obj.ToString());
// }
// else
// {
// foreach (KeyValuePair<string, string> item in propertyNameList)
// {
// if (item.Key.Equals(propertys[j].Name))
// {
// object obj = propertys[j].GetValue(list[i], null);
// dataRow.CreateCell(cellIndex2).SetCellValue(obj == null ? "" : obj.ToString());
// cellIndex2++;
// break;
// }
// }
// }
// }
// rowIndex++;
//}
#endregion
//求合并的列数
int rangesize = headerRow.Cells.Count;
//合并单元格
NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, rangesize - 1);
sheet.AddMergedRegion(cellRangeAddress);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
private void CreateRowValueSheet_2(ParkingParserItem item)
{
NPOI.SS.UserModel.IRow row;
ICell cell;
int type;
String nametype = "";
int start_row, end_row;
if (!(item != null && item.GetListItem() != null && item.GetListItem().Count > 0))
{
return;
}
//Clear header value
row = sheet_2.CreateRow(sheet_2_rowIndex);
sheet_2_rowIndex++;
start_row = sheet_2_rowIndex + 1;
if (CheckExistsSize_2(item.GetListItem().ElementAt(0).GetProductSize()) < 0)
{
type = 1;
}
else
{
type = 2;
}
for (int run = 0; run < Sheet_2_GetNumberOfColum(); run++)
{
cell = row.CreateCell(run);
cell.SetCellValue(Sheet_2_GetHeaderString(type, run));
if (item.GetNameProduct().StartsWith("R"))
{
nametype = " - BRASSIERES";
}
else
{
nametype = " - BRIEFS";
}
if (run == 0)
{
cell.SetCellValue(item.GetNameProduct() + nametype);
cell.CellStyle = sheet_2_headerStyle_1;
}
else
{
cell.CellStyle = sheet_2_headerStyle_2;
}
}
NPOI.SS.Util.CellRangeAddress range = new NPOI.SS.Util.CellRangeAddress(sheet_2_rowIndex - 1, sheet_2_rowIndex - 1, 0, 4);
sheet_2.AddMergedRegion(range);
foreach (PackingListItem packItem in item.GetListItem())
{
if (packItem.GetMergerList() != null && packItem.GetMergerList().Count > 0)
{
CreateMergerItem(type, item, packItem);
}
else
{
CreateNotMergerItem(type, item, packItem);
}
}
end_row = sheet_2_rowIndex;
//sum row
row = sheet_2.CreateRow(sheet_2_rowIndex);
sheet_2_rowIndex++;
for (int run = 0; run < Sheet_2_GetNumberOfColum(); run++)
{
cell = row.CreateCell(run);
if (run == 2)
{
cell.CellStyle = sheet_2_bottomStyle_2;
}
else
{
cell.CellStyle = sheet_2_bottomStyle_1;
}
switch (run)
{
case 0:
cell.SetCellValue("P'KGS");
break;
case 2:
cell.SetCellValue("SUB TOTAL:");
break;
case 3:
case 4:
break;
default:
{
String colum = Sheet_2_GetStringOfColum(run + 1);
String fomula = "SUM(" + colum + start_row + ":" + colum + end_row + ")";
cell.SetCellFormula(fomula);
break;
}
}
}
}
/**
* Add a cell range structure.
*
* @param firstRow - the upper left hand corner's row
* @param firstCol - the upper left hand corner's col
* @param lastRow - the lower right hand corner's row
* @param lastCol - the lower right hand corner's col
* @return the index of this ADDR structure
*/
public void AddCellRangeAddress(int firstRow, int firstCol, int lastRow, int lastCol)
{
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
AddCellRangeAddress(region);
}
public static void CopyRow(ref XSSFSheet worksheet, int sourceRowNum, int destinationRowNum, bool IsCoverRow = false, bool IsRemoveSrcRow = false, bool copyRowHeight = true, bool resetOriginalRowHeight = true)
{
XSSFRow newRow = worksheet.GetRow(destinationRowNum) as XSSFRow;
XSSFRow sourceRow = worksheet.GetRow(sourceRowNum) as XSSFRow;
XSSFCell oldCell, newCell;
int i;
if (newRow == null)
{
newRow = worksheet.CreateRow(destinationRowNum) as XSSFRow;
}
else
{
if (!IsCoverRow)
{
ShiftRows(ref worksheet, destinationRowNum, worksheet.LastRowNum, 1);
}
}
// Loop through source columns to add to new row
for (i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
oldCell = sourceRow.GetCell(i) as XSSFCell;
newCell = newRow.GetCell(i) as XSSFCell;
if (newCell == null)
{
newCell = newRow.CreateCell(i) as XSSFCell;
}
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
newCell.CellStyle = oldCell.CellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.CellFormula = oldCell.CellFormula;
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
List <NPOI.SS.Util.CellRangeAddress> cellRangeList = new List <NPOI.SS.Util.CellRangeAddress>();
NPOI.SS.Util.CellRangeAddress cellRangeAddress = null, newCellRangeAddress = null;
for (i = 0; i < worksheet.NumMergedRegions; i++)
{
cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
cellRangeList.Add(cellRangeAddress);
}
}
if (cellRangeList.Any())
{
foreach (var cellRange in cellRangeList)
{
newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress(newRow.RowNum, (newRow.RowNum + (cellRange.LastRow - cellRange.FirstRow)), cellRange.FirstColumn, cellRange.LastColumn);
worksheet.AddMergedRegion(newCellRangeAddress);
}
}
//複製行高到新列
if (copyRowHeight)
{
newRow.Height = sourceRow.Height;
}
//重製原始列行高
if (resetOriginalRowHeight)
{
sourceRow.Height = worksheet.DefaultRowHeight;
}
//清掉原列
if (IsRemoveSrcRow == true)
{
worksheet.RemoveRow(sourceRow);
bool next = true;
do
{
next = false;
for (i = 0; i < worksheet.NumMergedRegions; i++)
{
if (worksheet.GetMergedRegion(i).FirstRow == sourceRow.RowNum)
{
worksheet.RemoveMergedRegion(i);
next = true;
}
}
} while (next);
}
}
public void AddCellRangeAddress(CellRangeAddress cra)
{
_list.Add(cra);
}
/// <summary>
/// constructor
/// </summary>
/// <param name="sheet">the sheet where data located.</param>
/// <param name="range">the range within that sheet.</param>
public DataMarker(ISheet sheet, CellRangeAddress range)
{
this.sheet = sheet;
this.range = range;
}
/**
* @return the total size of for the specified number of ranges,
* including the initial 2 byte range count
*/
public static int GetEncodedSize(int numberOfRanges)
{
return(2 + CellRangeAddress.GetEncodedSize(numberOfRanges));
}
public static bool ContainsCell(CellRangeAddress cr, int rowIx, int colIx)
{
return(cr.IsInRange(rowIx, colIx));
}
/**
* Compute width of a single cell
*
* @param cell the cell whose width is to be calculated
* @param defaultCharWidth the width of a single character
* @param formatter formatter used to prepare the text to be measured
* @param useMergedCells whether to use merged cells
* @return the width in pixels
*/
public static double GetCellWidth(ICell cell, int defaultCharWidth, DataFormatter formatter, bool useMergedCells)
{
ISheet sheet = cell.Sheet;
IWorkbook wb = sheet.Workbook;
IRow row = cell.Row;
int column = cell.ColumnIndex;
int colspan = 1;
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.GetMergedRegion(i);
if (ContainsCell(region, row.RowNum, column))
{
if (!useMergedCells)
{
// If we're not using merged cells, skip this one and move on to the next.
return(-1);
}
cell = row.GetCell(region.FirstColumn);
colspan = 1 + region.LastColumn - region.FirstColumn;
}
}
ICellStyle style = cell.CellStyle;
CellType cellType = cell.CellType;
IFont defaultFont = wb.GetFontAt((short)0);
Font windowsFont = IFont2Font(defaultFont);
// for formula cells we compute the cell width for the cached formula result
if (cellType == CellType.Formula)
{
cellType = cell.CachedFormulaResultType;
}
IFont font = wb.GetFontAt(style.FontIndex);
//AttributedString str;
//TextLayout layout;
double width = -1;
using (Bitmap bmp = new Bitmap(2048, 100))
{
Graphics g = Graphics.FromImage(bmp);
if (cellType == CellType.String)
{
IRichTextString rt = cell.RichStringCellValue;
String[] lines = rt.String.Split("\n".ToCharArray());
for (int i = 0; i < lines.Length; i++)
{
String txt = lines[i] + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.length());
windowsFont = IFont2Font(font);
if (rt.NumFormattingRuns > 0)
{
// TODO: support rich text fragments
}
//layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
//AffineTransform trans = new AffineTransform();
//trans.concatenate(AffineTransform.getRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.concatenate(
// AffineTransform.getScaleInstance(1, fontHeightMultiple)
// );
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
SizeF sf = g.MeasureString(txt, windowsFont);
double x1 = Math.Abs(sf.Height * Math.Sin(angle));
double x2 = Math.Abs(sf.Width * Math.Cos(angle));
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
//width = Math.Max(width,
// ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
}
else
{
//width = Math.Max(width,
// ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
double w = Math.Round(g.MeasureString(txt, windowsFont).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
else
{
String sval = null;
if (cellType == CellType.Numeric)
{
// Try to get it formatted to look the same as excel
try
{
sval = formatter.FormatCellValue(cell, dummyEvaluator);
}
catch (Exception)
{
sval = cell.NumericCellValue.ToString();
}
}
else if (cellType == CellType.Boolean)
{
sval = cell.BooleanCellValue.ToString().ToUpper();
}
if (sval != null)
{
String txt = sval + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.length());
windowsFont = IFont2Font(font);
//layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
//AffineTransform trans = new AffineTransform();
//trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
//trans.concatenate(
// AffineTransform.getScaleInstance(1, fontHeightMultiple)
// );
//width = Math.max(width,
// ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
SizeF sf = g.MeasureString(txt, windowsFont);
double x1 = sf.Height * Math.Sin(angle);
double x2 = sf.Width * Math.Cos(angle);
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
else
{
//width = Math.max(width,
// ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
double w = Math.Round(g.MeasureString(txt, windowsFont).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
}
return(width);
}
/**
* Compute width of a single cell
*
* @param cell the cell whose width is to be calculated
* @param defaultCharWidth the width of a single character
* @param formatter formatter used to prepare the text to be measured
* @param useMergedCells whether to use merged cells
* @return the width in pixels or -1 if cell is empty
*/
public static double GetCellWidth(ICell cell, int defaultCharWidth, DataFormatter formatter, bool useMergedCells)
{
ISheet sheet = cell.Sheet;
IWorkbook wb = sheet.Workbook;
IRow row = cell.Row;
int column = cell.ColumnIndex;
int colspan = 1;
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.GetMergedRegion(i);
if (ContainsCell(region, row.RowNum, column))
{
if (!useMergedCells)
{
// If we're not using merged cells, skip this one and move on to the next.
return(-1);
}
cell = row.GetCell(region.FirstColumn);
colspan = 1 + region.LastColumn - region.FirstColumn;
}
}
ICellStyle style = cell.CellStyle;
CellType cellType = cell.CellType;
IFont defaultFont = wb.GetFontAt((short)0);
Font windowsFont = IFont2Font(defaultFont);
// for formula cells we compute the cell width for the cached formula result
if (cellType == CellType.Formula)
{
cellType = cell.CachedFormulaResultType;
}
IFont font = wb.GetFontAt(style.FontIndex);
double width = -1;
using (Bitmap bmp = new Bitmap(1, 1))
using (Graphics g = Graphics.FromImage(bmp))
{
if (cellType == CellType.String)
{
IRichTextString rt = cell.RichStringCellValue;
String[] lines = rt.String.Split("\n".ToCharArray());
for (int i = 0; i < lines.Length; i++)
{
String txt = lines[i] + defaultChar;
//AttributedString str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.length());
windowsFont = IFont2Font(font);
if (rt.NumFormattingRuns > 0)
{
// TODO: support rich text fragments
}
width = GetCellWidth(defaultCharWidth, colspan, style, width, txt, g, windowsFont, cell);
}
}
else
{
String sval = null;
if (cellType == CellType.Numeric)
{
// Try to get it formatted to look the same as excel
try
{
sval = formatter.FormatCellValue(cell, dummyEvaluator);
}
catch (Exception)
{
sval = cell.NumericCellValue.ToString();
}
}
else if (cellType == CellType.Boolean)
{
sval = cell.BooleanCellValue.ToString().ToUpper();
}
if (sval != null)
{
String txt = sval + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.length());
windowsFont = IFont2Font(font);
width = GetCellWidth(defaultCharWidth, colspan, style, width, txt, g, windowsFont, cell);
}
}
}
return(width);
}
public void UpdateExcelWithNPOI(DataTable dt, DateTime fromDate, DateTime toDate, String accountNo, String accountName)
{
// Open Template
FileStream fs = new FileStream("C:\\DRRTemplate\\CorporateFinance\\Corporate_Account_Performance_Master.xlsx", FileMode.Open, FileAccess.Read);
//FileStream fs = new FileStream(Server.MapPath(@"\Reports\DRR_Grogol_Master.xlsx"), FileMode.Open, FileAccess.Read);
// Load the template into a NPOI workbook
XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs);
// Load the sheet you are going to use as a template into NPOI
ISheet sheetMacro = templateWorkbook.GetSheet("Macro");
#region Cell Style
IDataFormat format = templateWorkbook.CreateDataFormat();
var fontBold = templateWorkbook.CreateFont();
fontBold.FontHeightInPoints = 10;
fontBold.FontName = "Calibri";
fontBold.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var fontSmall = templateWorkbook.CreateFont();
fontSmall.FontHeightInPoints = 10;
fontSmall.FontName = "Calibri";
//fontSmall.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
ICellStyle dateStyle = templateWorkbook.CreateCellStyle();
dateStyle.DataFormat = format.GetFormat("dd/MM/yyyy");
dateStyle.Alignment = HorizontalAlignment.Right;
dateStyle.VerticalAlignment = VerticalAlignment.Center;
dateStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
dateStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
dateStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
dateStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
dateStyle.SetFont(fontSmall);
ICellStyle amountStyle = templateWorkbook.CreateCellStyle();
amountStyle.DataFormat = format.GetFormat("#,##0"); // #.00
amountStyle.Alignment = HorizontalAlignment.Right;
amountStyle.VerticalAlignment = VerticalAlignment.Center;
amountStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
amountStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
amountStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
amountStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
amountStyle.SetFont(fontSmall);
ICellStyle percentageStyle = templateWorkbook.CreateCellStyle();
percentageStyle.DataFormat = format.GetFormat("#,##0.00"); // #.00
percentageStyle.Alignment = HorizontalAlignment.Right;
percentageStyle.VerticalAlignment = VerticalAlignment.Center;
percentageStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
percentageStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
percentageStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
percentageStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
percentageStyle.SetFont(fontSmall);
ICellStyle normalStyleLeft = templateWorkbook.CreateCellStyle();
normalStyleLeft.Alignment = HorizontalAlignment.Left;
normalStyleLeft.VerticalAlignment = VerticalAlignment.Center;
normalStyleLeft.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
normalStyleLeft.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
normalStyleLeft.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
normalStyleLeft.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
normalStyleLeft.SetFont(fontSmall);
ICellStyle normalStyleCenter = templateWorkbook.CreateCellStyle();
normalStyleCenter.Alignment = HorizontalAlignment.Center;
normalStyleCenter.VerticalAlignment = VerticalAlignment.Center;
normalStyleCenter.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
normalStyleCenter.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
normalStyleCenter.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
normalStyleCenter.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
normalStyleCenter.SetFont(fontSmall);
var CenterBoldStyle = templateWorkbook.CreateCellStyle();
CenterBoldStyle.VerticalAlignment = VerticalAlignment.Center;
CenterBoldStyle.Alignment = HorizontalAlignment.Center;
CenterBoldStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
CenterBoldStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
CenterBoldStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
CenterBoldStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
CenterBoldStyle.SetFont(fontBold);
#endregion
#region Merged Column Header
//make merged header row for month and year name
int mergedRowNumber = 5;
IRow rowMonthName = sheetMacro.CreateRow(mergedRowNumber);
//create merged column based on number of month
int totalColumn = dt.Columns.Count;
int numberOfColumnInOneMonth = 4;
int numberOfMonth = (totalColumn - 2) / numberOfColumnInOneMonth;//exclude 2 first column (CustNo and CustName)
int startCol = 2, endCol = 5;
for (int j = 0; j < numberOfMonth; j++)
{
var cra = new NPOI.SS.Util.CellRangeAddress(mergedRowNumber, mergedRowNumber, startCol, endCol);
sheetMacro.AddMergedRegion(cra);
ICell cell = rowMonthName.CreateCell(startCol);
cell.SetCellType(CellType.String);
//add border in merged cells
RegionUtil.SetBorderBottom(1, cra, sheetMacro, templateWorkbook);
RegionUtil.SetBorderTop(1, cra, sheetMacro, templateWorkbook);
RegionUtil.SetBorderLeft(1, cra, sheetMacro, templateWorkbook);
RegionUtil.SetBorderRight(1, cra, sheetMacro, templateWorkbook);
cell.CellStyle = CenterBoldStyle;
cell.SetCellValue(fromDate.AddMonths(j).ToString("MMM yyyy"));
startCol += numberOfColumnInOneMonth;
endCol += numberOfColumnInOneMonth;
//to remove
//<start>
//var cra1 = new NPOI.SS.Util.CellRangeAddress(5, 5, 2, 5);
//var cra2 = new NPOI.SS.Util.CellRangeAddress(5, 5, 6, 9);
//sheetMacro.AddMergedRegion(cra1);
//sheetMacro.AddMergedRegion(cra2);
//ICell cell1 = rowMonthName.CreateCell(2);
////ICell cell1 = sheetMacro.GetRow(5).GetCell(2);
//cell1.SetCellType(CellType.String);
//cell1.SetCellValue("Month 1");
//ICell cell2 = rowMonthName.CreateCell(6);
////ICell cell2 = sheetMacro.GetRow(5).GetCell(6);
//cell2.SetCellType(CellType.String);
//cell2.SetCellValue("Month 2");
//<end>
}
#endregion
#region Column Name
//make a header row for colmun name
//starting row 6 (zero based index=6) >
//CustNo, CustName, InvoiceM1, InvoiceM2,..InvoiceM[n], PaymentM1, PaymentM2,..PaymentM[n], LastPostingDateM1,LastPostingDateM2,..LastPostingDateM[n]
IRow row1 = sheetMacro.CreateRow(6);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.CellStyle = CenterBoldStyle;
String columnName = dt.Columns[j].ToString().Split('-')[0].ToString();
if (columnName == "CustNo")
{
columnName = "Customer No.";
}
if (columnName == "CustName")
{
columnName = "Name";
}
else if (columnName == "Invoice")
{
columnName = "Actual";
}
else if (columnName == "Payment")
{
columnName = "Collection";
}
else if (columnName == "LastPostingDate")
{
columnName = "Col Date";
}
else if (columnName == "Percentage")
{
columnName = "%";
}
cell.SetCellValue(columnName);
}
#endregion
#region Data
//loops through data
//get column lastPostingDate
int dateColumnStart = 4, percentageColumnStart = 5;
int numberofColumnPerMonth = 4;
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheetMacro.CreateRow(i + 7);//starting row 7, for data
for (int j = 0; j < dt.Columns.Count; j++)
{
//set decimal for invoice and payment
//date for last posting date
//text for custNo and custName
if (j <= 1)
{
//text
ICell cell = row.CreateCell(j);
String columnName = dt.Columns[j].ToString();
cell.SetCellType(CellType.String);
cell.CellStyle = normalStyleLeft;
cell.SetCellValue(dt.Rows[i][columnName].ToString());
}
else if ((j - dateColumnStart) % numberofColumnPerMonth == 0)//column 4 8 12 ..
{
//date
ICell cell = row.CreateCell(j);
String columnName = dt.Columns[j].ToString();
cell.SetCellType(CellType.String);
cell.CellStyle = dateStyle;
if (dt.Rows[i][columnName] != DBNull.Value)
{
cell.SetCellValue(Convert.ToDateTime(dt.Rows[i][columnName]));
}
}
else if ((j - percentageColumnStart) % numberofColumnPerMonth == 0) //column 5 9 13 ..
{
//percentage
ICell cell = row.CreateCell(j);
String columnName = dt.Columns[j].ToString();
cell.SetCellType(CellType.Numeric);
cell.CellStyle = percentageStyle;
if (dt.Rows[i][columnName] != DBNull.Value)
{
cell.SetCellValue(Convert.ToDouble(dt.Rows[i][columnName]));
}
else
{
cell.SetCellValue(0);
}
}
else
{
//decimal amount
ICell cell = row.CreateCell(j);
String columnName = dt.Columns[j].ToString();
cell.SetCellType(CellType.Numeric);
cell.CellStyle = amountStyle;
if (dt.Rows[i][columnName] != DBNull.Value)
{
cell.SetCellValue(Convert.ToDouble(dt.Rows[i][columnName]));
}
else
{
cell.SetCellValue(0);
}
}
}
}
#endregion
//Auto size column
for (int j = 2; j < dt.Columns.Count; j++)
{
sheetMacro.AutoSizeColumn(j);
}
#region Parameter
//set parameter value
int columnParameter = 1;
IRow rowFromDate = sheetMacro.GetRow(1);
IRow rowToDate = sheetMacro.GetRow(2);
IRow rowAccountNo = sheetMacro.GetRow(3);
IRow rowAccountName = sheetMacro.GetRow(4);
//cellstyle
//rowFromDate.GetCell(columnParameter).CellStyle
rowFromDate.GetCell(columnParameter).SetCellValue(fromDate);
rowToDate.GetCell(columnParameter).SetCellValue(toDate);
rowAccountNo.GetCell(columnParameter).SetCellValue(accountNo);
rowAccountName.GetCell(columnParameter).SetCellValue(accountName);
#endregion
#region Save to File
XSSFFormulaEvaluator.EvaluateAllFormulaCells(templateWorkbook);
using (var exportData = new MemoryStream())
{
Response.Clear();
templateWorkbook.Write(exportData);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "Corporate_AccountSummary_" + fromDate.ToString("ddMMMyyyy") + "_" + toDate.ToString("ddMMMyyyy") + ".xlsx"));
Response.BinaryWrite(exportData.ToArray());
Response.End();
}
#endregion
}
/// <summary>
/// 为Excel添加表头
/// </summary>
/// <param name="sheet"></param>
/// <param name="headerRow">GridView的HeaderRow属性</param>
/// <param name="headerCellStyle">表头格式</param>
/// <param name="flagNewLine">转行标志</param>
/// <param name="colCount">Excel表列数</param>
/// <returns>Excel表格行数</returns>
private int AddSheetHeader(ISheet sheet, GridViewRow headerRow, ICellStyle headerCellStyle, string flagNewLine, out int colCount)
{
//int
colCount = 0; //记录GridView列数
int rowInex = 0; //记录表头的行数
IRow row = sheet.CreateRow(0);
ICell cell;
int groupCount = 0; //记录分组数
int colIndex = 0; //记录列索引，并于结束表头遍历后记录总列数
for (int i = 0; i < headerRow.Cells.Count; i++)
{
if (rowInex != groupCount)//新增了标题行时重新创建
{
row = sheet.CreateRow(rowInex);
groupCount = rowInex;
}
#region 是否跳过当前单元格
for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
{
NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
//当前单元格是处于合并区域内
if (a.FirstColumn <= colIndex && a.LastColumn >= colIndex &&
a.FirstRow <= rowInex && a.LastRow >= rowInex)
{
colIndex++;
m = 0;//重新遍历所有合并区域判断新单元格是否位于合并区域
}
}
#endregion
cell = row.CreateCell(colIndex);
cell.CellStyle = headerCellStyle;
TableCell tablecell = headerRow.Cells[i];
//跨列属性可能为添加了html属性colspan，也可能是由cell的ColumnSpan属性指定
int colSpan = 0;
int rowSpan = 0;
#region 获取跨行跨列属性值
//跨列
if (!string.IsNullOrEmpty(tablecell.Attributes["colspan"]))
{
colSpan = int.Parse(tablecell.Attributes["colspan"].ToString());
colSpan--;
}
if (tablecell.ColumnSpan > 1)
{
colSpan = tablecell.ColumnSpan;
colSpan--;
}
//跨行
if (!string.IsNullOrEmpty(tablecell.Attributes["rowSpan"]))
{
rowSpan = int.Parse(tablecell.Attributes["rowSpan"].ToString());
rowSpan--;
}
if (tablecell.RowSpan > 1)
{
rowSpan = tablecell.RowSpan;
rowSpan--;
}
#endregion
//添加excel合并区域
if (colSpan > 0 || rowSpan > 0)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + rowSpan, colIndex, colIndex + colSpan));
colIndex += colSpan + 1;//重新设置列索引
}
else
{
colIndex++;
}
string strHeader = headerRow.Cells[i].Text;
if (strHeader.Contains(flagNewLine))//换行标记，当只存在一行标题时不存在</th></tr><tr>，此时colCount无法被赋值
{
rowInex++;
colCount = colIndex;
colIndex = 0;
strHeader = strHeader.Substring(0, strHeader.IndexOf("</th></tr><tr>"));
}
cell.SetCellValue(strHeader);
}
if (groupCount == 0)//只有一行标题时另外为colCount赋值
{
colCount = colIndex;
}
rowInex++;//表头结束后另起一行开始记录控件数据行索引
return(rowInex);
}
public static IRow CopyRow(this IRow dstRow, ISheet srcSheet, int srcRowNum)
{
IRow sourceRow = srcSheet.GetRow(srcRowNum) as IRow;
ICell oldCell, newCell;
int i;
// Loop through source columns to add to new row
for (i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
oldCell = sourceRow.GetCell(i) as ICell;
newCell = dstRow.GetCell(i) as ICell;
if (newCell == null)
{
newCell = dstRow.CreateCell(i) as ICell;
}
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
newCell.CellStyle = oldCell.CellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.CellFormula = oldCell.CellFormula;
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
//If there are are any merged regions in the source row, copy to new row
NPOI.SS.Util.CellRangeAddress cellRangeAddress = null, newCellRangeAddress = null;
for (i = 0; i < srcSheet.NumMergedRegions; i++)
{
cellRangeAddress = srcSheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress(dstRow.RowNum,
(dstRow.RowNum + (cellRangeAddress.LastRow - cellRangeAddress.FirstRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
dstRow.Sheet.AddMergedRegion(newCellRangeAddress);
}
}
//複製行高到新列
//if (copyRowHeight)
dstRow.Height = sourceRow.Height;
////重製原始列行高
//if (resetOriginalRowHeight)
// sourceRow.Height = worksheet.DefaultRowHeight;
////清掉原列
//if (IsRemoveSrcRow == true)
// worksheet.RemoveRow(sourceRow);
return(dstRow);
}
public void SetRepeatingRowsAndColumns(int sheetIndex,
int startColumn, int endColumn,
int startRow, int endRow)
{
XSSFSheet sheet = (XSSFSheet)GetSheetAt(sheetIndex);
CellRangeAddress rows = null;
CellRangeAddress cols = null;
if (startRow != -1)
{
rows = new CellRangeAddress(startRow, endRow, -1, -1);
}
if (startColumn != -1)
{
cols = new CellRangeAddress(-1, -1, startColumn, endColumn);
}
sheet.RepeatingRows=(rows);
sheet.RepeatingColumns=(cols);
}
////////////////////////////////////////////////////////////////////
private void CreateRowValueSheet_1(PackingListItem item, DatabaseItem databaseItem)
{
NPOI.SS.UserModel.IRow r;
if (item.GetQuantity() < databaseItem.GetMaxPacketSize())
{
r = sheet_1.CreateRow(rowIndex_sheet_1);
rowIndex_sheet_1++;
numberPacket_sheet_1++;
double valueMerger = item.GetQuantity();
if (item.GetMergerList() != null && item.GetMergerList().Count > 0)
{
foreach (MergerItem merger in item.GetMergerList())
{
valueMerger += merger.GetQuantity();
}
}
for (int idex = 0; idex < Sheet_1_Header.Length; idex++)
{
ICell cell = r.CreateCell(idex);
cell.CellStyle = sheet_1_headerStyle;
switch (idex)
{
case 0:
cell.SetCellValue(this.packingParser.GetDateTime());
break;
case 1:
cell.SetCellValue(item.GetNameProduct());
break;
case 2:
cell.SetCellValue(item.GetColor1());
break;
case 3:
cell.SetCellValue(item.GetColor2());
break;
case 4:
cell.SetCellValue(item.GetProductSize());
break;
case 5:
cell.SetCellValue(item.GetQuantity());
if (valueMerger > item.GetQuantity())
{
cell.CellStyle = sheet_1_headerStyle_2;
}
break;
case 6:
cell.SetCellValue(1);
break;
case 7:
cell.SetCellValue(numberPacket_sheet_1);
break;
case 8:
if (valueMerger <= item.GetQuantity())
{
cell.SetCellValue(valueMerger);
}
break;
case 9:
cell.SetCellValue(databaseItem.GetNetWeight());
break;
case 10:
cell.SetCellValue(databaseItem.GetAllWeight());
break;
case 11:
cell.SetCellValue(databaseItem.GetPacketInformation());
break;
default:
break;
}
}
}
else
{
for (int tmp = 0; tmp < (int)(item.GetQuantity() / databaseItem.GetMaxPacketSize()); tmp++)
{
numberPacket_sheet_1++;
r = sheet_1.CreateRow(rowIndex_sheet_1);
rowIndex_sheet_1++;
for (int idex = 0; idex < Sheet_1_Header.Length; idex++)
{
ICell cell = r.CreateCell(idex);
cell.CellStyle = sheet_1_headerStyle;
switch (idex)
{
case 0:
cell.SetCellValue(this.packingParser.GetDateTime());
break;
case 1:
cell.SetCellValue(item.GetNameProduct());
break;
case 2:
cell.SetCellValue(item.GetColor1());
break;
case 3:
cell.SetCellValue(item.GetColor2());
break;
case 4:
cell.SetCellValue(item.GetProductSize());
break;
case 5:
cell.SetCellValue(databaseItem.GetMaxPacketSize());
break;
case 6:
cell.SetCellValue(1);
break;
case 7:
cell.SetCellValue(numberPacket_sheet_1);
break;
case 8:
cell.SetCellValue(databaseItem.GetMaxPacketSize());
break;
case 9:
cell.SetCellValue(databaseItem.GetNetWeight());
break;
case 10:
cell.SetCellValue(databaseItem.GetAllWeight());
break;
case 11:
cell.SetCellValue(databaseItem.GetPacketInformation());
break;
default:
break;
}
}
}
}
if (item.GetMergerList() != null && item.GetMergerList().Count > 0)
{
foreach (MergerItem merger in item.GetMergerList())
{
r = sheet_1.CreateRow(rowIndex_sheet_1);
rowIndex_sheet_1++;
for (int idex = 0; idex < Sheet_1_Header.Length; idex++)
{
ICell cell = r.CreateCell(idex);
cell.CellStyle = sheet_1_headerStyle;
switch (idex)
{
case 0:
cell.SetCellValue(this.packingParser.GetDateTime());
break;
case 1:
cell.SetCellValue(merger.GetNameProduct());
break;
case 2:
cell.SetCellValue(merger.GetColor1());
break;
case 3:
cell.SetCellValue(merger.GetColor2());
break;
case 4:
cell.SetCellValue(merger.GetProductSize());
break;
case 5:
cell.SetCellValue(merger.GetQuantity());
cell.CellStyle = sheet_1_headerStyle_2;
break;
case 6:
break;
case 7:
cell.SetCellValue(numberPacket_sheet_1);
break;
case 8:
break;
case 9:
//cell.SetCellValue(databaseItem.GetNetWeight());
break;
case 10:
//cell.SetCellValue(databaseItem.GetAllWeight());
break;
case 11:
//cell.SetCellValue(databaseItem.GetPacketInformation());
break;
default:
break;
}
}
}
r = sheet_1.GetRow(rowIndex_sheet_1 - (1 + item.GetMergerList().Count));
String fomular = "SUM(F" + (rowIndex_sheet_1 - item.GetMergerList().Count) + ":F" + rowIndex_sheet_1 + ")";
if (r.GetCell(8) != null)
{
r.GetCell(8).CellFormula = fomular;
}
NPOI.SS.Util.CellRangeAddress range_1 = new NPOI.SS.Util.CellRangeAddress(rowIndex_sheet_1 - (1 + item.GetMergerList().Count),
rowIndex_sheet_1 - 1, 6, 6);
NPOI.SS.Util.CellRangeAddress range_2 = new NPOI.SS.Util.CellRangeAddress(rowIndex_sheet_1 - (1 + item.GetMergerList().Count),
rowIndex_sheet_1 - 1, 8, 8);
sheet_1.AddMergedRegion(range_1);
sheet_1.AddMergedRegion(range_2);
}
}
/**
* @return the new range(s) to replace the supplied ones. <c>null</c> if no merge is possible
*/
private static CellRangeAddress[] MergeRanges(CellRangeAddress range1, CellRangeAddress range2)
{
int x = Intersect(range1, range2);
switch (x)
{
case CellRangeUtil.NO_INTERSECTION:
if (HasExactSharedBorder(range1, range2))
{
return new CellRangeAddress[] { CreateEnclosingCellRange(range1, range2), };
}
// else - No intersection and no shared border: do nothing
return null;
case CellRangeUtil.OVERLAP:
return ResolveRangeOverlap(range1, range2);
case CellRangeUtil.INSIDE:
// Remove range2, since it is completely inside of range1
return new CellRangeAddress[] { range1, };
case CellRangeUtil.ENCLOSES:
// range2 encloses range1, so replace it with the enclosing one
return new CellRangeAddress[] { range2, };
}
throw new InvalidOperationException("unexpected intersection result (" + x + ")");
}
public static IRow CopyRow(ISheet sheet, int sourceRowIndex, int targetRowIndex)
{
if (sourceRowIndex == targetRowIndex)
{
throw new ArgumentException("sourceIndex and targetIndex cannot be same");
}
// Get the source / new row
IRow newRow = sheet.GetRow(targetRowIndex);
IRow sourceRow = sheet.GetRow(sourceRowIndex);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
sheet.ShiftRows(targetRowIndex, sheet.LastRowNum, 1);
}
else
{
newRow = sheet.CreateRow(targetRowIndex);
}
// Loop through source columns to add to new row
for (int i = sourceRow.FirstCellNum; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
ICell oldCell = sourceRow.GetCell(i);
// If the old cell is null jump to next cell
if (oldCell == null)
{
continue;
}
ICell newCell = newRow.CreateCell(i);
if (oldCell.CellStyle != null)
{
// apply style from old cell to new cell
newCell.CellStyle = oldCell.CellStyle;
}
// If there is a cell comment, copy
if (oldCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = sheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.LastRow - cellRangeAddress.FirstRow
)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
sheet.AddMergedRegion(newCellRangeAddress);
}
}
return(newRow);
}
/**
* @param crB never a full row or full column range
* @return an array including <b>this</b> <tt>CellRange</tt> and all parts of <tt>range</tt>
* outside of this range
*/
private static CellRangeAddress[] SliceUp(CellRangeAddress crA, CellRangeAddress crB)
{
ArrayList temp = new ArrayList();
// Chop up range horizontally and vertically
temp.Add(crB);
if (!crA.IsFullColumnRange)
{
temp = CutHorizontally(crA.FirstRow, temp);
temp = CutHorizontally(crA.LastRow + 1, temp);
}
if (!crA.IsFullRowRange)
{
temp = CutVertically(crA.FirstColumn, temp);
temp = CutVertically(crA.LastColumn + 1, temp);
}
CellRangeAddress[] crParts = ToArray(temp);
// form result array
temp.Clear();
temp.Add(crA);
for (int i = 0; i < crParts.Length; i++)
{
CellRangeAddress crPart = crParts[i];
// only include parts that are not enclosed by this
if (Intersect(crA, crPart) != ENCLOSES)
{
temp.Add(crPart);
}
}
return ToArray(temp);
}
private static Region ConvertToRegion(CellRangeAddress cr)
{
return(new Region(cr.FirstRow, cr.FirstColumn, cr.LastRow, cr.LastColumn));
}
/// <summary>
/// 读取上传文件中的Excel（返回表格，如果需要导入，还需要格式化表头）
/// </summary>
/// <param name="workbook">Excel工作本</param>
/// <param name="sheetIndex">第N个Sheet表格</param>
/// <param name="isMergedCellName">是否把合并列头的名称连接起来</param>
/// <param name="startIndex">开始索引</param>
/// <param name="headCrossRowNum">头部跨行数（为0时自动识别）</param>
/// <returns></returns>
private static MDataTable ReadExcel(IWorkbook workbook, Stream stream = null, int sheetIndex = 0, int startIndex = 0, int headCrossRowNum = 0, bool isMergedCellName = false)
{
MDataTable dt = new MDataTable();
try
{
if (workbook != null)
{
ISheet sheet = workbook.GetSheetAt(sheetIndex);
dt.TableName = sheet.SheetName;
dt.DynamicData = sheet;
IRow excelRow = sheet.GetRow(startIndex);
int dataRowStart = startIndex;
if (headCrossRowNum <= 0)
{
dataRowStart += 1;
#region 遍历、找出(头部跨行数)最大行。用最大行进行遍历列（如果为空，往上一级找）
int mIndex = 0;
for (int i = 0; i < excelRow.Cells.Count; i++)
{
ICell cell = excelRow.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);// .Cells[i];
if (cell.IsMergedCell)
{
NPOI.SS.Util.CellRangeAddress range = sheet.GetCellRange(cell);//获取范围块。
if (range != null)
{
dataRowStart = Math.Max(dataRowStart, range.LastRow + 1);//设置数据的读取行数。
mIndex++;
i += range.LastColumn - range.FirstColumn;
}
}
}
#endregion
}
else
{
dataRowStart += headCrossRowNum;
}
//读取列头。
if (dataRowStart > 1)
{
excelRow = sheet.GetRow(dataRowStart - 1);
}
dt.RecordsAffected = dataRowStart;
dt.Columns.CheckDuplicate = false;
#region 读取列头
//if (excelRow.FirstCellNum > 0)
//{
// for (int i = 0; i < excelRow.FirstCellNum; i++)
// {
// string columnName = "该列头为空_" + i;
// dt.Columns.Add(columnName);
// }
//}
int emptyCellCount = 0;//兼容处理错误的Excel格式（读了256个空格列）
for (int i = 0; i < excelRow.Cells.Count; i++)
{
string columnName = string.Empty;
for (int j = dataRowStart; j > startIndex; j--)
{
#region MyRegion
IRow row = sheet.GetRow(j - 1);
ICell cell;
try
{
cell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);//不能用GetCell(i)，会多出一行导致下面错误位。
}
catch (Exception)
{
continue;
}
string name = cell.ToString().Trim(); // .StringCellValue.Trim();
if (!string.IsNullOrEmpty(name) && !columnName.Contains(name)) //
{
columnName += name + "_";
}
else if (j != dataRowStart && cell.IsMergedCell)
{
cell = sheet.GetMergedRegion(cell);//获取范围块。
if (cell != null)
{
name = cell.ToString().Trim();
if (!string.IsNullOrEmpty(name) && !columnName.Contains(name))
{
columnName += name + "_";
}
}
}
if (!isMergedCellName && !string.IsNullOrEmpty(columnName))
{
break;
}
#endregion
}
columnName = columnName.TrimEnd('_').Trim();
if (string.IsNullOrEmpty(columnName))
{
if (emptyCellCount > 30)//连续30次空格列
{
break;
}
emptyCellCount++;
columnName = "该列头为空_" + i;
}
else
{
emptyCellCount = 0;//只要一个正常，即回归索引
}
if (dt.Columns.Contains(columnName))
{
columnName += "_" + i;
}
dt.Columns.Add(columnName);
}
//移除空格列
if (emptyCellCount > 0)
{
dt.Columns.RemoveRange(dt.Columns.Count - emptyCellCount, emptyCellCount);
}
if (dt.Columns.Count > 0)
{
dt.Conn = dt.Columns.Count.ToString();//找个变量存储实际的列的长度，在SetError中使用。（dt可能在SetError前列被变更)
}
#endregion
ICell sheetCell;
int emptyCount = 0;
for (int i = dataRowStart; i <= sheet.LastRowNum; i++)
{
excelRow = sheet.GetRow(i);
if (excelRow == null)
{
break;
}
MDataRow tbRow = dt.NewRow();
bool isOk = false;
for (int j = 0; j < dt.Columns.Count; j++)
{
#region 读一行
sheetCell = excelRow.GetCell(j, MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (sheetCell != null)
{
string value = string.Empty;
if (sheetCell.CellType == CellType.Numeric)
{
try
{
if (sheetCell.ToString().Split('/', '-').Length > 1)
{
value = sheetCell.DateCellValue.ToString();
}
else
{
value = sheetCell.NumericCellValue.ToString();
}
}
catch
{
value = sheetCell.ToString();
}
}
else if (sheetCell.CellType == CellType.Formula)
{
try
{
/*公式不一定是Numeric的取值，也有可能是=G4 这种，然而单元格G4不是数字;
* 公式单元格也有可能读取错误 #VALUE! #REF! 等*/
CellType resultType = sheetCell.CachedFormulaResultType;
switch (resultType)
{
case CellType.Boolean:
value = sheetCell.BooleanCellValue.ToString();
break;
case CellType.Numeric:
value = sheetCell.NumericCellValue.ToString();
break;
case CellType.Blank:
case CellType.Error:
case CellType.Unknown:
value = string.Empty;
break;
default:
value = sheetCell.StringCellValue;
break;
}
//value = sheetCell.NumericCellValue.ToString(); //由公式取值
}
catch
{
}
}
else
{
value = sheetCell.ToString();
}
value = value.Trim();
if (!isOk && !string.IsNullOrEmpty(value))
{
isOk = true;
}
if (!string.IsNullOrEmpty(value))//空值当Null值处理，避免字段有Check对空值的约束
{
tbRow.Set(sheetCell.ColumnIndex, value);
}
}
#endregion
}
if (isOk)//忽略空行数据。
{
dt.Rows.Add(tbRow);
}
else
{
if (dt.Rows.Count == 0)
{
dt.RecordsAffected++;
}
emptyCount++;
if (emptyCount > 1)//超过2次空格行，跳出。
{
break;
}
}
}
workbook.Close();//关闭了，dt.DynamicData带出了Sheet,还是可以后续使用（估计NPOI的没处理）
}
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
return(dt);
}
public static ICellStyle Getcellstyle(IWorkbook wb, stylexls str, NPOI.SS.Util.CellRangeAddress region, HSSFSheet sh)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 12;
font12.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font12.FontName = "微软雅黑";
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Black.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "微软雅黑";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //BorderLeft
if (region != null)
{
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row = HSSFCellUtil.GetRow(i, sh);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = cellStyle;
}
}
}
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.RightBorderColor = HSSFColor.OliveGreen.Black.Index;
//背景图形，我没有用到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OliveGreen.Black.Index;
//cellStyle.FillForegroundColor = HSSFColor.OliveGreen.Black.Index;
//cellStyle.FillPattern = FillPatternType.NO_FILL;
//cellStyle.FillForegroundColor = HSSFColor.White.Index;
//cellStyle.FillBackgroundColor = HSSFColor.Black.Index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
//cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palette = ((HSSFWorkbook)wb).GetCustomPalette();
//HSSFColor newColor = palette.AddColor((byte)153, (byte)204, (byte)255);
palette.SetColorAtIndex((short)10, (byte)227, (byte)232, (byte)227);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy-mm-dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
//IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("¥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.None;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
case stylexls.居中:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.SetFont(font);
break;
case stylexls.商品导入头:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette pale = ((HSSFWorkbook)wb).GetCustomPalette();
pale.SetColorAtIndex((short)30, (byte)11, (byte)87, (byte)235);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
cellStyle.SetFont(font12);
break;
case stylexls.导入说明:
HSSFPalette pale1 = ((HSSFWorkbook)wb).GetCustomPalette();
pale1.SetColorAtIndex((short)61, (byte)227, (byte)232, (byte)227);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightCornflowerBlue.Index;
IFont font2 = wb.CreateFont();
font2.FontName = "微软雅黑";
font2.FontHeightInPoints = 9;
cellStyle.SetFont(font2);
break;
case stylexls.导入红字:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palebgss = ((HSSFWorkbook)wb).GetCustomPalette();
palebgss.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
IFont fontRed = wb.CreateFont();
fontRed.FontName = "微软雅黑";
fontRed.Color = HSSFColor.OliveGreen.Red.Index;
cellStyle.SetFont(fontRed);
break;
case stylexls.导入错误提示:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette paleError = ((HSSFWorkbook)wb).GetCustomPalette();
paleError.SetColorAtIndex((short)10, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
cellStyle.SetFont(font);
break;
case stylexls.导入背景色:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palebgs = ((HSSFWorkbook)wb).GetCustomPalette();
palebgs.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
/**
* Check if the specified cell range has a shared border with the current range.
*
* @return <c>true</c> if the ranges have a complete shared border (i.e.
* the two ranges toher make a simple rectangular region.
*/
public static bool HasExactSharedBorder(CellRangeAddress crA, CellRangeAddress crB)
{
int oFirstRow = crB.FirstRow;
int oLastRow = crB.LastRow;
int oFirstCol = crB.FirstColumn;
int oLastCol = crB.LastColumn;
if (crA.FirstRow > 0 && crA.FirstRow - 1 == oLastRow ||
oFirstRow > 0 && oFirstRow - 1 == crA.LastRow)
{
// ranges have a horizontal border in common
// make sure columns are identical:
return crA.FirstColumn == oFirstCol && crA.LastColumn == oLastCol;
}
if (crA.FirstColumn > 0 && crA.FirstColumn - 1 == oLastCol ||
oFirstCol > 0 && crA.LastColumn == oFirstCol - 1)
{
// ranges have a vertical border in common
// make sure rows are identical:
return crA.FirstRow == oFirstRow && crA.LastRow == oLastRow;
}
return false;
}
private void CreateMergerItem(int type, ParkingParserItem item, PackingListItem packItem)
{
NPOI.SS.UserModel.IRow row;
int start_index;
int end_index;
int total = Sheet_2_GetNumberOfColum();
CombineMergerListItem combine = new CombineMergerListItem(packItem);
combine.RunCombineMergerListItem();
List <CombineItem> listCombine = combine.GetListCombineItem();
ICell cell;
ICell cellSum = null;
start_index = sheet_2_rowIndex;
for (int itemIndex = 0; itemIndex < listCombine.Count; itemIndex++)
{
//create new row
row = sheet_2.CreateRow(sheet_2_rowIndex);
sheet_2_rowIndex++;
for (int run = 0; run < Sheet_2_GetNumberOfColum(); run++)
{
cell = row.CreateCell(run);
cell.CellStyle = sheet_2_mainStyle_2;
switch (run)
{
case 0:
if (itemIndex == 0)
{
sheet_2_numberPacket++;
cell.SetCellValue(sheet_2_numberPacket + "");
}
break;
case 1:
if (itemIndex == 0)
{
cell.SetCellValue(1);
}
break;
case 2:
cell.SetCellValue(packItem.GetNameProduct());
break;
case 3:
cell.SetCellValue(listCombine.ElementAt(itemIndex).GetColor1());
break;
case 4:
cell.SetCellValue(listCombine.ElementAt(itemIndex).GetColor2());
break;
default:
if ((total - 4) == run)
{
if (itemIndex == 0)
{
cellSum = cell;
}
}
else if ((total - 3) == run)
{
if (itemIndex == 0)
{
cell.SetCellValue("PCS");
}
}
else if ((total - 2) == run)
{
if (itemIndex == 0)
{
String formula = item.GetDatabaseItem().GetNetWeight() + "*B" + sheet_2_rowIndex;
cell.SetCellFormula(formula);
}
}
else if ((total - 1) == run)
{
if (itemIndex == 0)
{
String formula = item.GetDatabaseItem().GetAllWeight() + "*B" + sheet_2_rowIndex;
cell.SetCellFormula(formula);
}
}
else
{
cell.SetCellValue("");
}
break;
}
}
foreach (CombineEle element in listCombine.ElementAt(itemIndex).GetListElement())
{
int size_index = Sheet_2_GetColumIndex(type, element.GetSize());
ICell cellSize = row.GetCell(size_index);
cellSize.SetCellValue(element.GetQuality());
}
}
end_index = sheet_2_rowIndex - 1;
String columName = Sheet_2_GetStringOfColum(Sheet_2_GetNumberOfColum() - 4);
String formulaSum = "SUM(F" + (start_index + 1) + ":" + columName + (end_index + 1) + ")";
if (cellSum != null)
{
cellSum.SetCellFormula(formulaSum);
}
if (end_index != start_index)
{
NPOI.SS.Util.CellRangeAddress range_1 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, 0, 0);
NPOI.SS.Util.CellRangeAddress range_2 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, 1, 1);
NPOI.SS.Util.CellRangeAddress range_3 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 4, total - 4);
NPOI.SS.Util.CellRangeAddress range_4 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 3, total - 3);
NPOI.SS.Util.CellRangeAddress range_5 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 2, total - 2);
NPOI.SS.Util.CellRangeAddress range_6 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 1, total - 1);
sheet_2.AddMergedRegion(range_1);
sheet_2.AddMergedRegion(range_2);
sheet_2.AddMergedRegion(range_3);
sheet_2.AddMergedRegion(range_4);
sheet_2.AddMergedRegion(range_5);
sheet_2.AddMergedRegion(range_6);
sheet_2.GetRow(start_index).GetCell(0).CellStyle = sheet_2_mergerStyle;
sheet_2.GetRow(start_index).GetCell(1).CellStyle = sheet_2_mergerStyle;
sheet_2.GetRow(start_index).GetCell(total - 4).CellStyle = sheet_2_mergerStyle;
sheet_2.GetRow(start_index).GetCell(total - 3).CellStyle = sheet_2_mergerStyle;
sheet_2.GetRow(start_index).GetCell(total - 2).CellStyle = sheet_2_mergerStyle;
sheet_2.GetRow(start_index).GetCell(total - 1).CellStyle = sheet_2_mergerStyle;
}
}