private int CopyChart(SoftwareFX.ChartFX.Chart oChartFx, int iChartRow)
{
string sImageFileName = null;
int iRowCnt = 0;
double dRowHeight = 0;
double dPicHeight = 0;
Excel.Pictures oPic;
sImageFileName = Environment.GetFolderPath(Environment.SpecialFolder.MyPictures)
+ @"\Tmp_Chart"
+ DateTime.Now.Year.ToString()
+ DateTime.Now.Month.ToString()
+ DateTime.Now.Day.ToString()
+ DateTime.Now.Hour.ToString()
+ DateTime.Now.Minute.ToString()
+ DateTime.Now.Second.ToString()
+ ".bmp";
oChartFx.Export(SoftwareFX.ChartFX.FileFormat.Bitmap, sImageFileName);
xlSheet.get_Range(xlSheet.Cells[iChartRow, 1], xlSheet.Cells[iChartRow, 1]).Select();
oPic = (Excel.Pictures)xlSheet.Pictures(Missing.Value);
oPic.Insert(sImageFileName, Missing.Value).Select(true);
dPicHeight = oPic.Insert(sImageFileName, Missing.Value).Height;
//Row의 폭
dRowHeight = Convert.ToDouble(xlSheet.get_Range(xlSheet.Cells[iChartRow, 1], xlSheet.Cells[iChartRow, 1]).Height);
//그림의 폭
iRowCnt = Convert.ToInt16((dPicHeight / dRowHeight)) + 2;
System.IO.File.Delete(sImageFileName);
return(iChartRow + iRowCnt);
}
/// <summary>
/// Spread와 Chart의 내용을 엑셀로 Export함.시작위치는 내부에서 자동 계산.
/// </summary>
/// <param name="oSpread"> Spread컨트롤 명 </param>
/// <param name="oChartFx"> ChartFX컨트롤 명 명 </param>
/// <param name="sFileTitle"> 화면 명 </param>
/// <param name="sHeadL"> 엑셀 머릿말(왼쪽) </param>
/// <param name="sHeadR"> 엑셀 머릿말(오른쪽) </param>
public void subMakeExcel(FpSpread oSpread, SoftwareFX.ChartFX.Chart oChartFx,
string sFileTitle, string sHeadL, string sHeadR)
{
this.subMakeExcel(oSpread, oChartFx, sFileTitle, sHeadL, sHeadR, true);
}
/// <summary>
/// Spread와 Chart의 내용을 엑셀로 Export함.시작위치는 내부에서 자동 계산.
/// </summary>
/// <param name="oSpread"> Spread컨트롤 명 </param>
/// <param name="oChartFx"> ChartFX컨트롤 명 명 </param>
/// <param name="sFileTitle"> 화면 명 </param>
/// <param name="sHeadL"> 엑셀 머릿말(왼쪽) </param>
/// <param name="sHeadR"> 엑셀 머릿말(오른쪽) </param>
/// <param name="autofit">오토피트(자동너비계산)</param>
public void subMakeExcel(FpSpread oSpread, SoftwareFX.ChartFX.Chart oChartFx,
string sFileTitle, string sHeadL, string sHeadR, bool bAutoFit)
{
DialogResult dlg;
bool IsMerge = true;
bool bResult = true;
int iSCol = 1;
int iSRow = 0;
int iMergeColSize = 0;
int iChartRow = 0;
int iTmp = 0;
int jTmp = 0;
try
{
if (oSpread.ActiveSheet.Rows.Count < 1)
{
MessageBox.Show("저장할 Data가 없습니다.", "Excel");
}
if (oSpread.ActiveSheet.Rows.Count > 600)
{
dlg = MessageBox.Show("데이타 건수가 많아 셀병합 작업시 속도가 느려집니다. 셀병합후 엑셀로 저장하시겠습니까?", "Excel Export", MessageBoxButtons.YesNo);
if (dlg == DialogResult.No)
{
IsMerge = false;
}
}
int iECol = 0;
int iERow = 0;
int iGridHeadCnt = 0;
if (sFileTitle == null)
{
sFileTitle = "";
}
if (sHeadL == null)
{
sHeadL = "";
}
if (sHeadR == null)
{
sHeadR = "";
}
xlApp = new Excel.Application();
xlBooks = xlApp.Workbooks;
xlBook = xlBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
xlSheet = (Excel.Worksheet)xlBook.ActiveSheet;
xlApp.Visible = false;
//xlApp.Visible = true;
xlApp.Cells.ClearContents();
xlApp.Cells.ClearFormats();
iGridHeadCnt = oSpread.ActiveSheet.ColumnHeaderRowCount;
DataTable dt = null;
String strSql = null;
strSql = " SELECT USER_DESC FROM RWEBUSRDEF WHERE USER_ID = '" + GlobalVariable.gsUserID + "' ";
dt = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", strSql);
if (sHeadR == "")
{
sHeadR = "사 용 자 : " + dt.Rows[0][0].ToString() + " (" + GlobalVariable.gsUserID + ")";
}
else
{
sHeadR = sHeadR + "^사 용 자 : " + dt.Rows[0][0].ToString() + " (" + GlobalVariable.gsUserID + ")";
}
if (oChartFx == null)
{
//iSRow값을 내부에서 계산하게 수정.
if (sHeadL != "")
{
iTmp = sHeadL.Split('^').Length;
}
if (sHeadR != "")
{
jTmp = sHeadR.Split('^').Length;
}
iSRow = (iTmp > jTmp ? iTmp : jTmp) + 5 + 1;
//페이지 여백설정
subPageSetup(iSRow + iGridHeadCnt - 1, true);
}
else
{
//iSRow값을 내부에서 계산하게 수정.
if (sHeadL != "")
{
iTmp = sHeadL.Split('^').Length;
}
if (sHeadR != "")
{
jTmp = sHeadR.Split('^').Length;
}
// 머릿말 Row수 + 타이틀이 차지하는 Row수(5)
iChartRow = (iTmp > jTmp ? iTmp : jTmp) + 5 + 1;
//Chart복사
iSRow = CopyChart(oChartFx, iChartRow);
//페이지 여백설정
subPageSetup(iSRow + iGridHeadCnt - 1, false);
}
//Header항목이 2라인일 경우
iECol = CopySpView(oSpread, iGridHeadCnt, iSCol, iSRow, ref iMergeColSize); //Excel에서 마지막 Col의 위치값
ColSize = iECol;
iERow = oSpread.ActiveSheet.Rows.Count + iSRow + iGridHeadCnt - 1; //Excel에서 마지막 Row의 위치값
//Header의 라인 작성(Head부분의 색적용, Data라인 작성)
HeaderLine(iGridHeadCnt, iSCol, iSRow, iECol, iERow, iMergeColSize);
////각각의 머리글을 작성
setHeader(iSCol, iECol, sHeadL, sHeadR);
//파일의 타이틀을 작성
setTitle(iSCol, iECol, sFileTitle);
//Data부분 셀 Merge (600건 넘어가면 속도 작살임..)
if (IsMerge == true)
{
string[] tmpData = null;
int iRepeatRow = 0;
if (oSpread.ActiveSheet.Tag != null)
{
tmpData = oSpread.ActiveSheet.Tag.ToString().Split('^');
iRepeatRow = Convert.ToInt16(tmpData[1].ToString()) - 1;
}
DataMerge(iSCol, iSRow + iGridHeadCnt, iMrgECol, iERow, iMergeColSize, iRepeatRow);
}
xlSheet.get_Range(xlSheet.Cells[iSRow, iSCol], xlSheet.Cells[iSRow, iSCol]).Select();
oSpread.ActiveSheet.SetActiveCell(iSRow, iSCol);
if (bAutoFit == true)
{
//왼쪽 머릿말과 오른쪽 머리말 부분을 뺀 나머지만 AutoFit을 한다.
if (sHeadL == "" && sHeadR == "")
{
xlSheet.get_Range(xlSheet.Cells[iSRow, 1], xlSheet.Cells[iSRow, iECol]).EntireColumn.AutoFit();
}
else if (sHeadL == "" && sHeadR != "")
{
xlSheet.get_Range(xlSheet.Cells[iSRow, 1], xlSheet.Cells[iSRow, iECol - 1]).EntireColumn.AutoFit();
}
else if (sHeadL != "" && sHeadR == "")
{
xlSheet.get_Range(xlSheet.Cells[iSRow, 2], xlSheet.Cells[iSRow, iECol]).EntireColumn.AutoFit();
}
else
{
xlSheet.get_Range(xlSheet.Cells[iSRow, 2], xlSheet.Cells[iSRow, iECol - 1]).EntireColumn.AutoFit();
}
}
bResult = true;
}
catch (Exception ex)
{
String errorMessage = "";
errorMessage = String.Concat(errorMessage, ex.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, ex.Source);
CmnFunction.ShowMsgBox(errorMessage, "Error [" + ex.Source + "]", MessageBoxButtons.OK, 1);
bResult = false;
}
finally
{
// 사용자에게 저장 여부를 묻는다.
xlBook.Saved = false;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks);
if (bResult == false)
{
//Make Excel application close.
xlApp.DisplayAlerts = false;
//xlBook.Saved = true;
xlBooks.Close(); // ***** 이 함수를 호출 하지 않으면 작업프로세스에 EXCEL.EXE가 죽지 않고 계속 남아 있음 *****
xlApp.Quit();
}
else
{
// 모든 엑셀의 경고메시지가 나타나도록 한다.
//xlApp.DisplayAlerts = true;
//Make Excel visible and give the user control.
xlApp.Visible = true;
xlApp.UserControl = true;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();
System.Windows.Forms.Cursor.Current = Cursors.Default;
}
} //public void subMakeExcel(FpSpread oSpread, Chart oChartFx, string sFileTitle, string sHeadL, string sHeadR)