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