private void ClearRanges() { Excel.Range r = GetRange("D19", "P35") as Excel.Range; if (r != null) { try { r.ClearContents(); } finally { Marshal.ReleaseComObject(r); } } }
/// <summary> /// 合并单元格,并赋值,对指定WorkSheet操作 /// </summary> /// <param name="beginRowIndex">开始行索引</param> /// <param name="beginColumnIndex">开始列索引</param> /// <param name="endRowIndex">结束行索引</param> /// <param name="endColumnIndex">结束列索引</param> /// <param name="text">合并后Range的值</param> private void MergeCells(Excel.Worksheet sheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) { if (sheet == null) return; range = sheet.get_Range(sheet.Cells[beginRowIndex, beginColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]); range.ClearContents(); //先把Range内容清除,合并才不会出错 range.MergeCells = true; range.Value = text; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; }
/// <summary> /// 合并单元格,并赋值,对指定WorkSheet操作 /// </summary> /// <param name="sheetIndex">WorkSheet索引</param> /// <param name="beginRowIndex">开始行索引</param> /// <param name="beginColumnIndex">开始列索引</param> /// <param name="endRowIndex">结束行索引</param> /// <param name="endColumnIndex">结束列索引</param> /// <param name="text">合并后Range的值</param> public void MergeCells(int sheetIndex, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) { if (sheetIndex > this.WorkSheetCount) { this.KillExcelProcess(); throw new Exception("索引超出范围,WorkSheet索引不能大于WorkSheet数量!"); } workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex); range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]); range.ClearContents(); //先把Range内容清除,合并才不会出错 range.MergeCells = true; range.Value = text; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; }
/// <summary> /// 合并单元格,并赋值,对每个WorkSheet操作 /// </summary> /// <param name="beginRowIndex">开始行索引</param> /// <param name="beginColumnIndex">开始列索引</param> /// <param name="endRowIndex">结束行索引</param> /// <param name="endColumnIndex">结束列索引</param> /// <param name="text">合并后Range的值</param> public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) { for (int i = 1; i <= this.WorkSheetCount; i++) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]); range.ClearContents(); //先把Range内容清除,合并才不会出错 range.MergeCells = true; range.Value = text; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; } }
private void DataMerge(int iSCol, int iSRow, int iECol, int iERow, int iMergeColSize, int iRepeatRow) { int iCSRow = 0; int iCERow = 0; string sValue = ""; string sNextValue = ""; for (int iC = iSCol; iC <= iMergeColSize; iC++) { //비교 대상 초기값 sValue = xlSheet.get_Range(xlSheet.Cells[iSRow, iC], xlSheet.Cells[iSRow, iC]).Text.ToString(); iCSRow = iSRow; for (int iR = iSRow + 1; iR <= iERow + 1; iR++) { if (iR < iERow + 1) { sNextValue = xlSheet.get_Range(xlSheet.Cells[iR, iC], xlSheet.Cells[iR, iC]).Text.ToString(); } else { sNextValue = "!@#$%^&*()"; //Dummy } //Sub Total Row의 Column병합을막기위해서. if (sValue.IndexOf("Sub Total") > 0 || sValue == "Total") { //xlSheet.get_Range(xlSheet.Cells[iR, iC], xlSheet.Cells[iR + iRepeatRow, iMergeColSize]); Excel.Range oRange = xlSheet.get_Range(xlSheet.Cells[iCSRow, iC], xlSheet.Cells[iCSRow + iRepeatRow, iMergeColSize]); oRange.Value2 = sValue; } if (sValue != sNextValue) { //두칸이상인 경우만 Merge해줌(세로 데이타 머지) if ((iR - iCSRow) > 1) { iCERow = iR - 1; Excel.Range oRange = xlSheet.get_Range(xlSheet.Cells[iCSRow, iC], xlSheet.Cells[iCERow, iC]); oRange.ClearContents(); oRange.MergeCells = true; oRange.Value2 = sValue; oRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; oRange.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; } iCSRow = iR; sValue = xlSheet.get_Range(xlSheet.Cells[iR, iC], xlSheet.Cells[iR, iC]).Text.ToString(); } } } //Total이나 Sub Total부분 셀 Merge for (int iR = iSRow; iR <= iERow; iR = iR + iRepeatRow + 1) { for (int iC = iMergeColSize - 1; iC >= iSCol; iC--) { //비교 대상 초기값 sValue = xlSheet.get_Range(xlSheet.Cells[iR, iC], xlSheet.Cells[iR, iC]).Text.ToString(); //- Sub Total인 경우 데이타가 엑셀에서 깨어짐. 이값을 보증하기 위한 루틴. if (sValue == "#NAME?") { sValue = xlSheet.get_Range(xlSheet.Cells[iR, iC], xlSheet.Cells[iR, iC]).Formula.ToString(); sValue = sValue.Replace("=", " "); } if (sValue.IndexOf("Sub Total") > 0 || sValue == "Total") { Excel.Range oRangeMgr = xlSheet.get_Range(xlSheet.Cells[iR, iC], xlSheet.Cells[iR + iRepeatRow, iMergeColSize]); oRangeMgr.ClearContents(); oRangeMgr.MergeCells = true; oRangeMgr.Value2 = sValue; // SubTotal 과 Total 부분 Row에 BackColor Excel.Range oRange2 = xlSheet.get_Range(xlSheet.Cells[iR, iC], xlSheet.Cells[iR + iRepeatRow, ColSize]); oRange2.Interior.ColorIndex = 38; oRange2.Font.ColorIndex = 1; if (sValue == "Total") { oRangeMgr.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } else { oRangeMgr.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } oRangeMgr.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; } } } }
private void HeaderLine(int iGridHeadCnt, int iSCol, int iSRow, int iECol, int iERow, int iMergeColSize) { bool isV = false; bool isH = false; string[] tmpHeadMrg = null; string tmpHeadInx = null; string sValue = null; int i = 0; int iCSRow = 0; int iCERow = 0; if (iSCol < iECol) { isV = true; } if (iSRow < iERow) { isH = true; } LineDraw(iSCol, iSRow, iECol, iERow, isV, isH); if (iGridHeadCnt == 2 || iGridHeadCnt == 3) { //각 Merge할 셀의 위치정보파악 tmpHeadInx = ""; for (i = 0; i <= iECol - 2; i++) { if (xlSheet.get_Range(xlSheet.Cells[iSRow, iSCol + i], xlSheet.Cells[iSRow, iSCol + i]).Text.ToString() != "") { tmpHeadInx += Convert.ToString(iSCol + i) + ","; } } if (xlSheet.get_Range(xlSheet.Cells[iSRow, iECol], xlSheet.Cells[iSRow, iECol]).Text.ToString() != "") { tmpHeadInx += Convert.ToString(iECol); } else { tmpHeadInx = tmpHeadInx.Substring(0, tmpHeadInx.Length - 1); } tmpHeadMrg = tmpHeadInx.Split(','); //가로 데이타 머지. for (i = 0; i < tmpHeadMrg.Length - 1; i++) { xlSheet.get_Range(xlSheet.Cells[iSRow, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow, Convert.ToInt16(tmpHeadMrg[i + 1]) - 1]).MergeCells = true; if (xlSheet.get_Range(xlSheet.Cells[iSRow + 1, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow + 1, Convert.ToInt16(tmpHeadMrg[i])]).Text.ToString() == "") { xlSheet.get_Range(xlSheet.Cells[iSRow, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow + 1, Convert.ToInt16(tmpHeadMrg[i])]).MergeCells = true; } } xlSheet.get_Range(xlSheet.Cells[iSRow, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow, iECol]).MergeCells = true; if (xlSheet.get_Range(xlSheet.Cells[iSRow + 1, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow + 1, Convert.ToInt16(tmpHeadMrg[i])]).Text.ToString() == "") { xlSheet.get_Range(xlSheet.Cells[iSRow, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow + 1, Convert.ToInt16(tmpHeadMrg[i])]).MergeCells = true; } //세로 데이타 머지 for (int iC = iSCol; iC <= iMergeColSize; iC++) { //비교 대상 초기값 sValue = xlSheet.get_Range(xlSheet.Cells[iSRow, iC], xlSheet.Cells[iSRow, iC]).Text.ToString(); iCSRow = iSRow; for (int iR = iSRow + 1; iR <= iSRow + iGridHeadCnt - 1; iR++) { if (sValue == xlSheet.get_Range(xlSheet.Cells[iR, iC], xlSheet.Cells[iR, iC]).Text.ToString()) { iCERow = iR; Excel.Range oRangeMgr = xlSheet.get_Range(xlSheet.Cells[iCSRow, iC], xlSheet.Cells[iCERow, iC]); oRangeMgr.ClearContents(); oRangeMgr.MergeCells = true; oRangeMgr.Value2 = sValue; oRangeMgr.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; oRangeMgr.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; iCSRow = iR; sValue = xlSheet.get_Range(xlSheet.Cells[iR, iC], xlSheet.Cells[iR, iC]).Text.ToString(); } } } } //if (iGridHeadCnt == 3) //{ // //각 Merge할 셀의 위치정보파악 // tmpHeadInx = ""; // for (int x = 0; x < 2; x++) // { // tmpHeadInx = ""; // tmpHeadMrg = null; // for (i = 0; i <= iECol - 2; i++) // { // if (xlSheet.get_Range(xlSheet.Cells[iSRow + x, iSCol + i], xlSheet.Cells[iSRow + x, iSCol + i]).Text.ToString() != "") // tmpHeadInx += Convert.ToString(iSCol + i) + ","; // } // if (xlSheet.get_Range(xlSheet.Cells[iSRow + x, iECol], xlSheet.Cells[iSRow + x, iECol]).Text.ToString() != "") // tmpHeadInx += Convert.ToString(iECol); // else // tmpHeadInx = tmpHeadInx.Substring(0, tmpHeadInx.Length - 1); // tmpHeadMrg = tmpHeadInx.Split(','); // //가로 데이타 머지. // for (i = 0; i < tmpHeadMrg.Length - 1; i++) // { // xlSheet.get_Range(xlSheet.Cells[iSRow + x, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow + x, Convert.ToInt16(tmpHeadMrg[i + 1]) - 1]).MergeCells = true; // if (xlSheet.get_Range(xlSheet.Cells[iSRow + 1 + x, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow + 1 + x, Convert.ToInt16(tmpHeadMrg[i])]).Text.ToString() == "") // { // xlSheet.get_Range(xlSheet.Cells[iSRow + x, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow + 1 + x, Convert.ToInt16(tmpHeadMrg[i])]).MergeCells = true; // } // } // xlSheet.get_Range(xlSheet.Cells[iSRow + x, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow + x, iECol]).MergeCells = true; // if (xlSheet.get_Range(xlSheet.Cells[iSRow + 1 + x, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow + 1 + x, Convert.ToInt16(tmpHeadMrg[i])]).Text.ToString() == "") // { // xlSheet.get_Range(xlSheet.Cells[iSRow + x, Convert.ToInt16(tmpHeadMrg[i])], xlSheet.Cells[iSRow + 1 + x, Convert.ToInt16(tmpHeadMrg[i])]).MergeCells = true; // } // //세로 데이타 머지 // for (int iC = iSCol; iC <= iMergeColSize; iC++) // { // //비교 대상 초기값 // sValue = xlSheet.get_Range(xlSheet.Cells[iSRow, iC], xlSheet.Cells[iSRow, iC]).Text.ToString(); // iCSRow = iSRow; // for (int iR = iSRow + 1; iR <= iSRow + iGridHeadCnt - 1; iR++) // { // if (sValue == xlSheet.get_Range(xlSheet.Cells[iR, iC], xlSheet.Cells[iR, iC]).Text.ToString()) // { // iCERow = iR; // Excel.Range oRangeMgr = xlSheet.get_Range(xlSheet.Cells[iCSRow, iC], xlSheet.Cells[iCERow, iC]); // oRangeMgr.ClearContents(); // oRangeMgr.MergeCells = true; // oRangeMgr.Value2 = sValue; // oRangeMgr.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; // oRangeMgr.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; // iCSRow = iR; // sValue = xlSheet.get_Range(xlSheet.Cells[iR, iC], xlSheet.Cells[iR, iC]).Text.ToString(); // } // } // } // } //} Excel.Range oRange = (Excel.Range)xlSheet.get_Range(xlSheet.Cells[iSRow, iSCol], xlSheet.Cells[iSRow + iGridHeadCnt - 1, iECol]); oRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; oRange.Font.Size = 10; oRange.Font.Bold = true; oRange.RowHeight = 17; oRange.Interior.ColorIndex = 41; oRange.Font.ColorIndex = 2; //헤더 아래의 선 //oRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; //oRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium; //oRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; }