C# ISheet.AddMergedRegion方法代码示例

        private static void OutputDateRow(ISheet sheet, IEnumerable<string> distinctDates, int rowIndex)
        {
            var colSpan = 3;
            var startCol = 1;

            var dateRow = sheet.CreateRow(rowIndex);
            var headerRow = sheet.CreateRow(rowIndex + 1);

            dateRow.CreateCell(0).SetCellValue("");
            headerRow.CreateCell(0).SetCellValue("");

            for (var i = 0; i < distinctDates.Count(); i++)
            {
                var cell = dateRow.CreateCell(startCol);
                cell.SetCellValue(distinctDates.ElementAt(i));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, startCol, colSpan * (i + 1)));

                headerRow.CreateCell(startCol).SetCellValue(ValueHeader);
                headerRow.CreateCell(startCol + 1).SetCellValue(PrefixHeader);
                headerRow.CreateCell(startCol + 2).SetCellValue(DlHeader);

                startCol = startCol + colSpan;
            }

        }

 internal static void columnMerge(ISheet sheet, ICellStyle cellStyle, int row, int startColumn, int endColumn)
 {
     sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row, row, startColumn, endColumn));
     cellStyle.BorderBottom = BorderStyle.Thin;
     cellStyle.BorderTop = BorderStyle.Thin;
     cellStyle.BorderLeft = BorderStyle.Thin;
     cellStyle.BorderRight = BorderStyle.Thin;
 }

        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;
        }

            private void PutNamesWithValidations(ISheet ws, ICellStyle cs1, ICellStyle cs2, ICellStyle cs3, params string[][] names)
            {
                var rn0 = ws.PhysicalNumberOfRows;
                var rn = rn0;
                for (var i = 0; i < names.Length; ++i)
                {
                    var rnc = rn++;
                    var r = ws.CreateRow(rnc);
                    if (1 < names[i].Length)
                    {
                        r.RowStyle = cs3;
                        var vh = ws.GetDataValidationHelper();
                        var vl = names[i].Skip(1).ToList();
                        vl.Sort();
                        var vd = vh.CreateValidation(
                            vh.CreateExplicitListConstraint(vl.ToArray()),
                            new CellRangeAddressList(rnc, rnc, 1, _excelVer.LastColumnIndex)
                            );
                        vd.ShowErrorBox = false;
                        vd.ShowPromptBox = true;
                        ws.AddValidationData(vd);
                    }

                    var c = r.CreateCell(0);
                    c.SetCellValue(names[i][0]);
                    if (0 == i)
                    {
                        c.CellStyle = cs1;
                        r.RowStyle = cs1;
                    }
                    else
                    {
                        c.CellStyle = cs2;
                    }
                }
                if (1 < names.Length) ws.GroupRow(rn0 + 1, rn - 1);
                //ws.SetRowGroupCollapsed(rn0 + 1, true);
                ws.AddMergedRegion(new CellRangeAddress(rn0, rn0, 0, _excelVer.LastColumnIndex));
            }

        /// <summary>
        /// 设置excel模版错误信息
        /// </summary>
        /// <param name="sheet">数据标签</param>
        /// <param name="rowindex">错误信息显示行</param>
        /// <param name="msg">错误信息</param>
        public static void SetTemplateErrorMsg(ISheet sheet, int rowindex, string msg)
        {
            IRow row = sheet.GetRow(rowindex);
            row = sheet.CreateRow(rowindex);
            if (row != null && !string.IsNullOrEmpty(msg))
            {
                sheet.AddMergedRegion(new CellRangeAddress(rowindex, rowindex, 0, row.LastCellNum));

                ICell cell = row.GetCell(0);
                if (cell == null)
                {
                    cell = row.CreateCell(0);
                }
                ICellStyle cellStyle = sheet.Workbook.CreateCellStyle();
                cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                cellStyle.Alignment = HorizontalAlignment.LEFT;
                IFont font = sheet.Workbook.CreateFont();
                font.FontHeightInPoints = 12;
                font.Color = HSSFColor.RED.index;
                cellStyle.SetFont(font);
                cell.CellStyle = cellStyle;
                cell.SetCellValue(msg);
            }
        }

        private int MSWriteToSheet1(Dictionary<int, SelfNPOICell[]> data, ISheet sheet)
        {
            if (data == null || data.Count == 0)
                return -1;

            List<int> setRow = new List<int>();
            setRow.Add(3);
            setRow.Add(4);
            setRow.Add(5);
            setRow.Add(6);
            setRow.Add(7);

            ICellStyle fontBold = this.setFontBold();
            ICellStyle cellBorder = this.setCellBorder();

            foreach (var r in data)
            {
                IRow row = sheet.CreateRow(r.Key);

                for (int col = 0; col < r.Value.Count(); col++)
                {
                    ICell cell = row.CreateCell(col);
                    cell.SetCellValue(r.Value[col].CellValue);
                    ////给加盟费等标签字体加粗
                    if (r.Value[col].IsFontBold)
                    {
                        cell.CellStyle = fontBold;
                    }

                    if (r.Value[col].HasCellBorder)
                    {
                        cell.CellStyle = cellBorder;
                    }
                }
                ////合并单元格并设置边框
                if (setRow.Contains(r.Key))
                {
                    CellRangeAddress region1 = new CellRangeAddress(r.Key, r.Key, 1, 2);
                    CellRangeAddress region2 = new CellRangeAddress(r.Key, r.Key, 5, 6);
                    sheet.AddMergedRegion(region1);
                    sheet.AddMergedRegion(region2);
                    ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region1, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index);
                    ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region2, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index);
                }
            }
            //设置合并单元格的长度，需全部显示文字
            sheet.SetColumnWidth(2, 30 * 256);
            sheet.SetColumnWidth(6, 30 * 256);

            return 0;
        }

        private void MergeReportExcelColum(List<OutStoreModel> outStoreModels, ISheet sheet)
        {
            var preModel = outStoreModels[0];
            int row_start = 2;
            int row_end = 2;

            for (int i = 1; i < outStoreModels.Count; i++)
            {
                if (preModel.Department == outStoreModels[i].Department)
                {
                    row_end++;
                }
                else
                {
                    if (row_end > row_start)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));
                        sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 6, 6));

                        row_start = row_end;
                    }
                    row_end++;
                    row_start++;
                }

                preModel = outStoreModels[i];
            }

            if (row_end > row_start)
            {
                sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));
                sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 6, 6));
            }
        }

        private void MergeColum(List<StockModel> stockModels, ISheet sheet)
        {
            var preModel = stockModels[0];
            int row_start = 2;
            int row_end = 2;

            for (int i = 1; i < stockModels.Count; i++)
            {
                if (preModel.Type == stockModels[i].Type)
                {
                    row_end++;
                }
                else
                {
                    if (row_end > row_start)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));

                        row_start = row_end;
                    }
                    row_end++;
                    row_start++;
                }

                preModel = stockModels[i];
            }

            if (row_end > row_start)
            {
                sheet.AddMergedRegion(new CellRangeAddress(row_start, row_end, 0, 0));
            }
        }

    /// <summary>
    /// 为Excel添加数据
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="gv"></param>
    /// <param name="colTypeList">GridView每一列的数据类型</param>
    /// <param name="colCount">GridView的总列数</param>
    /// <param name="rowInex">添加Excel数据行的起始索引号</param>
    /// <param name="cellStyle">表格基础格式</param>
    private void AddSheetBody(ISheet sheet, GridView gv, ICellStyle cellStyle, List<int> colTypeList, int colCount, int rowInex)
    {
        IRow row;
        ICell cell;
        ICellStyle cellStyleDecimal = GetCellStyleDecimal(sheet.Workbook);
        ICellStyle cellStyleDateTime = GetCellStyleDateTime(sheet.Workbook);

        int rowCount = gv.Rows.Count;

        for (int i = 0; i < rowCount; i++)
        {
            row = sheet.CreateRow(rowInex);

            for (int j = 0; j < colCount; j++)
            {
                if (gv.Rows[i].Cells[j].Visible == false) continue;

                string cellText = gv.Rows[i].Cells[j].Text.Trim();
                cellText = cellText.Replace("&nbsp;", "");//替换空字符占位符
                cellText = cellText.Replace("&gt;", ">");//替换 > 占位符

                if (string.IsNullOrEmpty(cellText)) continue;//单元格为空跳过

                cell = row.CreateCell(j);
                if (colTypeList.Count == 0 || colTypeList.Count < j || colTypeList[j] <= 0)//无法获取到该列类型
                {
                    cell.SetCellValue(cellText);
                    cell.CellStyle = cellStyle;
                }
                else
                {
                    try
                    {
                        switch (colTypeList[j])
                        {
                            case 1: cell.SetCellValue(int.Parse(cellText));//int类型
                                cell.CellStyle = cellStyle;
                                break;
                            case 2: cell.SetCellValue(double.Parse(cellText));//decimal数据类型
                                cell.CellStyle = cellStyleDecimal;
                                break;
                            case 3: cell.SetCellValue(DateTime.Parse(cellText));//日期类型
                                cell.CellStyle = cellStyleDateTime;
                                break;
                            default: cell.SetCellValue(cellText);
                                cell.CellStyle = cellStyle;
                                break;
                        }
                    }
                    catch
                    {
                        cell.SetCellValue("单元格导出失败");
                        MCSFramework.Common.LogWriter.FILE_PATH = GetAttachmentDirectory();
                        MCSFramework.Common.LogWriter.WriteLog("\r\n第j=" + j + "类发生错误，数据类型为" + colTypeList[j].ToString() + "，数值为" + cellText + "，报表GUID=" + Request.QueryString["Report"] != null ? Request.QueryString["Report"] : "无GUID值" + "\r\n");
                    }
                }

                int MergeAcross = gv.Rows[i].Cells[j].ColumnSpan > 0 ? gv.Rows[i].Cells[j].ColumnSpan - 1 : 0;//跨列，即合并的列数

                int MergeDown = gv.Rows[i].Cells[j].RowSpan > 0 ? gv.Rows[i].Cells[j].RowSpan - 1 : 0;//跨行，即合并的行数

                if (MergeAcross > 0 || MergeDown > 0)//存在要合并的行
                {
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + MergeDown, j, j + MergeAcross));
                    j += MergeAcross;
                }
            }
            rowInex++;
        }
    }

        static void FillTheSoSheet(ISheet sheet,So so, List<SoItemsContentAndState> soitemList)
        {
            sheet.GetRow(0).CreateCell(2).SetCellValue(so.customerName);
               sheet.GetRow(0).CreateCell(6).SetCellValue(so.contact);
               sheet.GetRow(0).CreateCell(9).SetCellValue(AmbleClient.Admin.AccountMgr.AccountMgr.GetNameById(so.salesId));

               if (so.approverId != null)
               {
               sheet.GetRow(0).CreateCell(14).SetCellValue(AmbleClient.Admin.AccountMgr.AccountMgr.GetNameById(so.approverId.Value) + "," + so.approveDate.Value.ToShortDateString());
               }
               sheet.GetRow(1).CreateCell(2).SetCellValue(so.salesOrderNo);
               sheet.GetRow(1).CreateCell(6).SetCellValue(so.orderDate.ToShortDateString());

               sheet.GetRow(1).CreateCell(9).SetCellValue(so.customerPo);
               sheet.GetRow(1).CreateCell(14).SetCellValue(so.paymentTerm);

               sheet.GetRow(2).CreateCell(2).SetCellValue(so.freightTerm);
               sheet.GetRow(2).CreateCell(6).SetCellValue(so.customerAccount);
               sheet.GetRow(2).CreateCell(9).SetCellValue(so.specialInstructions);

               IRow row = sheet.CreateRow(4);
               row.CreateCell(0).SetCellValue(so.billTo);
               row.CreateCell(8).SetCellValue(so.shipTo);

               int itemRowIndex = 9;
               foreach (SoItemsContentAndState scs in soitemList)
               {
               IRow itemRow = sheet.CreateRow(itemRowIndex);
               itemRow.CreateCell(0).SetCellValue(soitemList.IndexOf(scs)+1);
               string strSaleType;
               switch (scs.soitem.saleType)
               {

                   case 0:

                       strSaleType = "OEM EXCESS";
                       break;
                   case 1:
                       strSaleType = "OWN STOCK";
                       break;
                   case 2:
                       strSaleType = "OTHERS";
                       break;
                   default:
                       strSaleType = "ERROR";
                       break;

               }

               itemRow.CreateCell(1).SetCellValue(strSaleType);
               itemRow.CreateCell(2).SetCellValue(scs.soitem.partNo);
               itemRow.CreateCell(3).SetCellValue(scs.soitem.mfg);
               itemRow.CreateCell(4).SetCellValue(scs.soitem.rohs == 1 ? "Y" : "N");
               itemRow.CreateCell(5).SetCellValue(scs.soitem.dc);
               itemRow.CreateCell(6).SetCellValue(scs.soitem.intPartNo);
               itemRow.CreateCell(7).SetCellValue(scs.soitem.shipFrom);
               itemRow.CreateCell(8).SetCellValue(scs.soitem.shipMethod);
               itemRow.CreateCell(9).SetCellValue(scs.soitem.trackingNo);
               itemRow.CreateCell(10).SetCellValue(scs.soitem.qty);
               if (scs.soitem.qtyshipped != null)
               {
                   itemRow.CreateCell(11).SetCellValue(scs.soitem.qtyshipped.Value);
               }
               itemRow.CreateCell(12).SetCellValue(Enum.GetName(typeof(AmbleClient.Currency), scs.soitem.currencyType));
               itemRow.CreateCell(13).SetCellValue(scs.soitem.unitPrice);
               itemRow.CreateCell(14).SetCellValue(scs.soitem.unitPrice * scs.soitem.qty);
               itemRow.CreateCell(15).SetCellValue(scs.soitem.dockDate.ToShortDateString());

               if (scs.soitem.shippedDate != null)
               {
                   itemRow.CreateCell(16).SetCellValue(scs.soitem.shippedDate.Value.ToShortDateString());
               }
               itemRowIndex++;

               IRow infoRow=sheet.CreateRow(itemRowIndex);
               infoRow.CreateCell(1).SetCellValue("Shipping Instructions >>" + scs.soitem.shippingInstruction);
               sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex,itemRowIndex,1,16));

               itemRowIndex++;
               infoRow=sheet.CreateRow(itemRowIndex);
               infoRow.CreateCell(1).SetCellValue("Packing Instructions >>" + scs.soitem.packingInstruction);
               sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex,itemRowIndex,1,16));
               itemRowIndex++;
               }
        }

 /// <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)
 {
     NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(rowstart, rowend, colstart, colend);
     sheet.AddMergedRegion(cellRangeAddress);
 }

 /// <summary>
 /// 合并单元格
 /// </summary>
 /// <param name="sheet"> 要合并单元格所在的sheet </param>
 /// <param name="rowstart"> 开始行的索引 </param>
 /// <param name="rowend"> 结束行的索引 </param>
 /// <param name="colstart"> 开始列的索引 </param>
 /// <param name="colend"> 结束列的索引 </param>
 protected void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
 {
     CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
     sheet.AddMergedRegion(cellRangeAddress);
     // sheet.SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
 }

    /// <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);
//.........这里部分代码省略.........

        private void CopyRow(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum)
        {
            // Get the source / new row
            IRow destinationRow;
            var getRows = destinationWorksheet.GetRow(destinationRowNum);
            if (getRows == null)
            {
                destinationRow = destinationWorksheet.CreateRow(destinationRowNum);
            }
            else
            {
                destinationRow = getRows;
            }

            IRow sourceRow = sourceWorksheet.GetRow(sourceRowNum);

            // Loop through source columns to add to new row
            for (int i = 0; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                ICell oldCell = sourceRow.GetCell(i);
                ICell newCell = destinationRow.CreateCell(i);

                // 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
                ICellStyle newCellStyle = workbook.CreateCellStyle();
                newCellStyle.CloneStyleFrom(oldCell.CellStyle); ;
                newCell.CellStyle = newCellStyle;

                // 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 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;
                    case CellType.Unknown:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                }
            }

            // If there are are any merged regions in the source row, copy to new row
            for (int i = 0; i < sourceWorksheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = sourceWorksheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow == sourceRow.RowNum)
                {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(destinationRow.RowNum,
                                                                                destinationRow.RowNum + (cellRangeAddress.LastRow - cellRangeAddress.FirstRow),
                                                                                cellRangeAddress.FirstColumn,
                                                                                cellRangeAddress.LastColumn);
                    destinationWorksheet.AddMergedRegion(newCellRangeAddress);
                }
            }
        }

        static void FillThePoSheet(ISheet sheet,po po, List<PoItemContentAndState> poitemList)
        {
            sheet.GetRow(0).CreateCell(2).SetCellValue(po.vendorName);
               sheet.GetRow(0).CreateCell(9).SetCellValue(po.contact);
               sheet.GetRow(0).CreateCell(14).SetCellValue(AmbleClient.Admin.AccountMgr.AccountMgr.GetNameById((int)po.pa));
               sheet.GetRow(1).CreateCell(2).SetCellValue(po.vendorNumber);

               if (po.poDate != null)
               {
               sheet.GetRow(1).CreateCell(5).SetCellValue(po.poDate.ToShortDateString());
               }
               sheet.GetRow(1).CreateCell(9).SetCellValue(po.poNo);
               sheet.GetRow(1).CreateCell(14).SetCellValue(po.paymentTerms);

               sheet.GetRow(2).CreateCell(2).SetCellValue(po.shipMethod);
               sheet.GetRow(2).CreateCell(9).SetCellValue(po.freight);
               sheet.GetRow(2).CreateCell(14).SetCellValue(po.shipToLocation);

               IRow row = sheet.CreateRow(4);
               row.CreateCell(0).SetCellValue(po.billTo);
               row.CreateCell(10).SetCellValue(po.shipTo);

               int itemRowIndex = 9; int totalQty = 0; float totalTotal = 0;int totalQtyRecd = 0;
               foreach (PoItemContentAndState pcs in poitemList)
               {
               IRow itemRow = sheet.CreateRow(itemRowIndex);
               itemRow.CreateCell(0).SetCellValue(poitemList.IndexOf(pcs)+1);
               itemRow.CreateCell(1).SetCellValue(pcs.poItem.partNo);
               itemRow.CreateCell(2).SetCellValue(pcs.poItem.mfg);
               itemRow.CreateCell(3).SetCellValue(pcs.poItem.dc);
               itemRow.CreateCell(4).SetCellValue(pcs.poItem.vendorIntPartNo);
               itemRow.CreateCell(5).SetCellValue(pcs.poItem.coo);
               itemRow.CreateCell(6).SetCellValue(pcs.poItem.qty);
               totalQty += pcs.poItem.qty;

               if (pcs.poItem.qtyRecd != null)
               {
                   itemRow.CreateCell(7).SetCellValue(pcs.poItem.qtyRecd.Value);
                   totalQtyRecd += pcs.poItem.qtyRecd.Value;
               }
               if(pcs.poItem.qtyCorrected!=null)
               itemRow.CreateCell(8).SetCellValue(pcs.poItem.qtyCorrected.Value);

               if(pcs.poItem.qtyAccept!=null)
               itemRow.CreateCell(9).SetCellValue(pcs.poItem.qtyAccept.Value);

               if(pcs.poItem.qtyRejected!=null)
               itemRow.CreateCell(10).SetCellValue(pcs.poItem.qtyRejected.Value);

               if(pcs.poItem.qtyRTV!=null)
                itemRow.CreateCell(11).SetCellValue(pcs.poItem.qtyRTV.Value);

               if(pcs.poItem.qcPending!=null)
               itemRow.CreateCell(12).SetCellValue(pcs.poItem.qcPending.Value);

               itemRow.CreateCell(13).SetCellValue(Enum.GetName(typeof(AmbleClient.Currency), pcs.poItem.currency));
               itemRow.CreateCell(14).SetCellValue(pcs.poItem.unitPrice.Value);
               itemRow.CreateCell(15).SetCellValue(pcs.poItem.qty*pcs.poItem.unitPrice.Value);
               totalTotal += pcs.poItem.qty* pcs.poItem.unitPrice.Value;

               itemRow.CreateCell(16).SetCellValue(pcs.poItem.dockDate.ToShortDateString());

               if(pcs.poItem.receiveDate!=null)
               itemRow.CreateCell(17).SetCellValue(pcs.poItem.receiveDate.Value.ToShortDateString());

               itemRow.CreateCell(18).SetCellValue(pcs.poItem.stepCode);
               itemRow.CreateCell(19).SetCellValue("Unknown");

               itemRowIndex++;

               IRow infoRow = sheet.CreateRow(itemRowIndex);
               infoRow.CreateCell(1).SetCellValue("Note To Vendor >>" + pcs.poItem.noteToVendor);
               sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex, itemRowIndex, 1, 16));

               itemRowIndex++;

               }
               //add total bar
            IRow totalRow=sheet.CreateRow(itemRowIndex);
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(itemRowIndex,itemRowIndex,0,4));
                totalRow.CreateCell(5).SetCellValue("Total");
                totalRow.CreateCell(6).SetCellValue(totalQty);
                totalRow.CreateCell(7).SetCellValue(totalQtyRecd);
                totalRow.CreateCell(15).SetCellValue(totalTotal);
        }