/// <summary>
/// 해당 컬럼에 값이 일치 하면 글자색을 바꿔준다..
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="intCols"></param>
/// <param name="str"></param>
/// <param name="colBackColor"></param>
public static void Invoke_SearchThenChForecolor(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, int[] intCols, string str, Color colForecolor)
{
if (fp.InvokeRequired)
{
fp.Invoke(new delInvoke_SearchThenChForecolor(Invoke_SearchThenChForecolor), new object[] { fp, sv, intCols, str, colForecolor });
return;
}
int intColCnt = sv.ColumnCount - 1;
for (int y = 0; y < sv.Rows.Count; y++)
{
foreach (int intCol in intCols)
{
int rst = sv.Cells[y, intCol].Text.IndexOf(str);
if (rst >= 0)
{
sv.Cells[y, 0, y, intColCnt].ForeColor = colForecolor;
break;
}
}
}
}
public frmSpreadGridConfig2005(FarPoint.Win.Spread.FpSpread spread)
: this()
{
this.spread = spread;
this.Init();
}
public bool Printer(FarPoint.Win.Spread.FpSpread fpSpread, string paperType, int rowCount)
{
try
{
FarPoint.Win.Spread.PrintInfo pi = new FarPoint.Win.Spread.PrintInfo();
pi.Margin.Header = 20;
pi.Margin.Bottom = 70;
pi.Margin.Top = 100;
pi.Margin.Left = 20;
pi.Margin.Right = 20;
pi.ColStart = fpSpread.ActiveSheet.Models.Selection.AnchorColumn;
pi.ColEnd = fpSpread.ActiveSheet.Models.Selection.LeadColumn;
pi.RowStart = fpSpread.ActiveSheet.Models.Selection.AnchorRow;
pi.RowEnd = fpSpread.ActiveSheet.Models.Selection.LeadRow;
if (paperType == "가로")
{
pi.Orientation = FarPoint.Win.Spread.PrintOrientation.Landscape;
}
if (paperType == "세로")
{
pi.Orientation = FarPoint.Win.Spread.PrintOrientation.Portrait;
}
//Headr Show 설정
pi.ShowRowHeader = FarPoint.Win.Spread.PrintHeader.Hide;
pi.ShowColumnHeader = FarPoint.Win.Spread.PrintHeader.Hide;
pi.ShowBorder = false;
pi.ShowGrid = false;
//인쇄 페이지 마다 출력될 반복 페이지를 설정한다.
pi.RepeatColEnd = 12;
pi.RepeatColStart = 1;
pi.RepeatRowEnd = 5;
pi.RepeatRowStart = 4;
//한 페이지에 출력이 가능하도록 맞춘다. 축소옵션
pi.ZoomFactor = 0.9f;
//정렬
pi.Centering = FarPoint.Win.Spread.Centering.Horizontal;
////PageNo 출력
pi.Footer = "/l QP-751-04(Rev.1) /c(주)율촌 /rA4(210*297mm) ";
pi.PrintType = FarPoint.Win.Spread.PrintType.All;
//인쇄옵션 보기
pi.ShowPrintDialog = true;
//인쇄미리보기
pi.Preview = true;
fpSpread등록대장인쇄.ActiveSheet.PrintInfo = pi;
fpSpread등록대장인쇄.PrintSheet(fpSpread등록대장인쇄.ActiveSheet);
return(true);
}
catch
{
return(false);
}
}
public void UpdateChart(FarPoint.Win.Spread.FpSpread FpSpread)
{
foreach (FarPoint.Win.Spread.SheetView Sheet in FpSpread.Sheets)
{
//支持嵌入的图表
int RowCount = Sheet.GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Style);
int ColumnCount = Sheet.GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Style);
for (int i = 0; i <= RowCount; i++)
{
for (int j = 0; j <= ColumnCount; j++)
{
if (Sheet.Cells[i, j].CellType is ChartCellType)
{
ChartCellType ChartType = Sheet.Cells[i, j].CellType as ChartCellType;
Rectangle r = FpSpread.GetCellRectangle(0, 0, i, j);
ChartType.ChartSize = r.Size;
ChartType.ActiveSheet = Sheet;
ChartType.UpdateChart();
}
}
}
//支持浮动的图表
foreach (IElement Element in Sheet.DrawingContainer.ContainedObjects)
{
if (Element is ChartShape)
{
ChartShape Shape = Element as ChartShape;
Shape.ActiveSheet = Sheet;
Shape.Locked = false;
Shape.UpdateChart();
}
}
}
}
public static bool Excel(FarPoint.Win.Spread.FpSpread fpSpread, string fileName)
{
try
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "excel files (*.xls)|*.xls|All files (*.*)|*.*";
saveFileDialog1.FilterIndex = 0;
saveFileDialog1.Title = "Excel 파일저장";
saveFileDialog1.AddExtension = true;
saveFileDialog1.RestoreDirectory = true;
saveFileDialog1.FileName = fileName + ".xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
//SaveFlags로 ColumnHeader를 Excel파일에 함께 저장함
fpSpread.SaveExcel(saveFileDialog1.FileName, FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders);
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show("Error : [" + ex.Message + "]", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
}
/// <summary>
/// 범위에 검색하는 문자가 있으면 범위에 row단위로 글자색/배경색을 변경 하여준다..
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="strInStr"></param>
/// <param name="intChkCol"></param>
/// <param name="intStCol"></param>
/// <param name="intEdCol"></param>
/// <param name="colFore">null 이면 변경 않함</param>
/// <param name="colBack">null 이면 변경 않함</param>
public static void Invoke_CellRange_InStr_SetRowColor(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, string strInStr,
int intChkCol, int intStCol, int intEdCol, object colFore, object colBack)
{
if (fp.InvokeRequired)
{
fp.Invoke(new delInvoke_CellRange_InStr_SetRowColor(Invoke_CellRange_InStr_SetRowColor), new object[] { fp, sv, strInStr, intChkCol, intStCol,
intEdCol, colFore, colBack });
return;
}
for (int y = 0; y < sv.Rows.Count; y++)
{
if (sv.Cells[y, intChkCol].Text.IndexOf(strInStr) >= 0)
{
if (colFore != null)
{
sv.Cells[y, intStCol, y, intEdCol].ForeColor = (Color)colFore;
}
if (colBack != null)
{
sv.Cells[y, intStCol, y, intEdCol].BackColor = (Color)colBack;
}
}
}
fp.Refresh();
}
/// <summary>
/// Cell에 Fomula셑팅을 한다.
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="cell"></param>
/// <param name="formulaString"></param>
/// <param name="celltype">변경할 셀 타입 FarPoint.Win.Spread.CellType 네임스페이스에서 새로운 클래스생성 하여 넘긴다. 무변경 null</param>
public static void Invoke_CellFormula_Set(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, FarPoint.Win.Spread.Cell cell, string formulaString,
FarPoint.Win.Spread.CellType.BaseCellType celltype)
{
if (fp.InvokeRequired)
{
fp.BeginInvoke(new delInvoke_CellFormula_Set(Invoke_CellFormula_Set), new object[] { fp, sv, cell, formulaString, celltype });
return;
}
string textvalue = string.Empty;
cell.Formula = formulaString;
if (celltype != null)
{
cell.CellType = celltype;
}
/*
* textvalue = cell.Text;
* FarPoint.Win.Spread.CellType.TextCellType t = new FarPoint.Win.Spread.CellType.TextCellType();
* cell.CellType = t;
* cell.Formula = "";
* cell.Value = textvalue;
*/
}
//Spread 离开行变色
public static void Spread_LeaveCellBColor(FarPoint.Win.Spread.FpSpread Spd, FarPoint.Win.Spread.LeaveCellEventArgs e, Color bColor)
{
if (e.NewRow < Spd.ActiveSheet.RowCount)
{
Spd.ActiveSheet.Rows[0, Spd.ActiveSheet.Rows.Count - 1].BackColor = Color.Empty;
Spd.ActiveSheet.Rows[e.NewRow].BackColor = bColor;
}
}
//Spread 单击行变色
public static void Spread_CellClickChangeBColor(FarPoint.Win.Spread.FpSpread Spd, FarPoint.Win.Spread.CellClickEventArgs e, Color bColor)
{
if (e.Row < Spd.ActiveSheet.RowCount)
{
Spd.ActiveSheet.Rows[0, Spd.ActiveSheet.Rows.Count - 1].BackColor = Color.Empty;
Spd.ActiveSheet.Rows[e.Row].BackColor = bColor;
}
}
private void Initex(FarPoint.Win.Spread.FpSpread fp)
{
foreach (FarPoint.Win.Spread.SheetView she in fp.Sheets)
{
she.ColumnCount = she.NonEmptyColumnCount;
she.RowCount = she.NonEmptyRowCount;
//txEdit1.ExportRangeToHTML(she, 0, 0, she.NonEmptyRowCount, she.NonEmptyColumnCount, "11", "22", 1);
}
}
/// <summary>
/// 원하는 셀을 보여 준다.
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="row"></param>
/// <param name="col"></param>
public static void Invoke_ShowCell(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, int row, int col)
{
if (fp.InvokeRequired)
{
fp.BeginInvoke(new delShowCell(Invoke_ShowCell), new object[] { fp, sv, row, col });
return;
}
sv.SetActiveCell(row, col, true);
fp.ShowActiveCell(VerticalPosition.Center, HorizontalPosition.Center);
}
/// <summary>
/// row count를 변경한다.
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="intRowCount"></param>
public static void Invoke_RowCount(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, int intRowCount)
{
if (fp.InvokeRequired)
{
fp.Invoke(new delInvoke_RowCount(Invoke_RowCount), new object[] { fp, sv, intRowCount });
return;
}
sv.RowCount = intRowCount;
}
/// <summary>
/// 셀 범위에 값을 변경한다.
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="Y"></param>
/// <param name="X"></param>
/// <param name="Y2"></param>
/// <param name="X2"></param>
/// <param name="objValue"></param>
public static void Invoke_CellRange_Value(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv,
int Y, int X, int Y2, int X2, object objValue)
{
if (fp.InvokeRequired)
{
fp.Invoke(new delInvoke_CellRange_Value(Invoke_CellRange_Value), new object[] { fp, sv, Y, X, Y2, X2, objValue });
return;
}
sv.Cells[Y, X, Y2, X2].Value = objValue;
}
/// <summary>
/// 셀을 스팬(머지) 한다.
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="intRow"></param>
/// <param name="intCol"></param>
/// <param name="intRowSpanCnt"></param>
/// <param name="intColSpanCnt"></param>
public static void Invoke_CellSpan(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, int intRow, int intCol, int intRowSpanCnt, int intColSpanCnt)
{
if (fp.InvokeRequired)
{
fp.Invoke(new delInvoke_CellSpan(Invoke_CellSpan), new object[] { fp, sv, intRow, intCol, intRowSpanCnt, intColSpanCnt });
return;
}
sv.Cells[intRow, intCol].ColumnSpan = intColSpanCnt;
sv.Cells[intRow, intCol].RowSpan = intRowSpanCnt;
}
/// <summary>
/// fpSheet에 Row를 추가 하여준다.
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="intAddIndex">들어갈 위치 : '-1'이면 마지막에 위치 시킨다.</param>
/// <param name="intMaxRowCount">최대 행 유지 갯수 : 0이면 무제한 증가..</param>
public static void Invoke_AddRowData(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, int intAddIndex,
int intMaxRowCount, object [] objData)
{
if (fp.InvokeRequired)
{
fp.Invoke(new delInvoke_AddRowData(Invoke_AddRowData), new object[] { fp, sv, intAddIndex, intMaxRowCount, objData });
return;
}
try
{
if (intAddIndex < 0)
{
intAddIndex = sv.Rows.Count;
}
sv.Rows.Add(intAddIndex, 1);
int intCol = 0;
foreach (object obj in objData)
{
//if (sv.ColumnCount <= intCol) break;
sv.Cells[intAddIndex, intCol].Value = obj;
intCol++;
}
//최대행 유지를 위해 Row를 삭제 한다.
if (intMaxRowCount > 0 && sv.Rows.Count >= intMaxRowCount)
{
int intDelIndex = 0;
int intDelCount = 1 + intMaxRowCount - sv.Rows.Count;
//아래 추가 시만 위에 삭제, 그외 경우는 아래 부분 삭제..
if (intAddIndex < 0)
{
intDelIndex = 0;
}
else
{
intDelIndex = sv.Rows.Count - intDelCount;
}
sv.Rows.Remove(intDelIndex, 1);
}
}
catch (Exception ex)
{
log.WLog_Exception(string.Format("Invoke_AddRowData[fp]{0}[AddIdx]{1}[MaxRow]{2}", fp.Name, intAddIndex, intMaxRowCount), ex);
}
}
/// <summary>
/// 导入EXCEL
/// </summary>
/// <param name="filepach"></param>
/// <param name="filedList"></param>
/// <param name="capList"></param>
/// <returns></returns>
public static DataTable GetExcel(string filepach, IList <string> filedList, IList <string> capList)
{
string str;
FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();
try
{
fpSpread1.OpenExcel(filepach);
}
catch
{
string filepath1 = Path.GetTempPath() + "\\" + Path.GetFileName(filepach);
File.Copy(filepach, filepath1);
fpSpread1.OpenExcel(filepath1);
File.Delete(filepath1);
}
DataTable dt = new DataTable();
IList <string> fie = new List <string>();
IList <int> yxzl = new List <int>();//有效的那几列
int m = 1;
for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
{
if (capList.Contains(fpSpread1.Sheets[0].Cells[0, j].Text))
{
fie.Add(filedList[capList.IndexOf(fpSpread1.Sheets[0].Cells[0, j].Text)]);
yxzl.Add(j);
}
}
for (int k = 0; k < fie.Count; k++)
{
dt.Columns.Add(fie[k]);
}
for (int i = m; i <= fpSpread1.Sheets[0].GetLastNonEmptyRow(FarPoint.Win.Spread.NonEmptyItemFlag.Data); i++)
{
DataRow dr = dt.NewRow();
// for (int j = 0; j < fpSpread1.Sheets[0].GetLastNonEmptyColumn(FarPoint.Win.Spread.NonEmptyItemFlag.Data) + 1; j++)
int n = 0;
foreach (int j in yxzl)
{
dr[fie[n]] = fpSpread1.Sheets[0].Cells[i, j].Text;
n++;
}
dt.Rows.Add(dr);
}
return(dt);
}
/// <summary>
/// 스프레드에서 원하는 값을 가지 row를 찾는다.
/// </summary>
/// <param name="fp"></param>
/// <param name="intSheetIndex"></param>
/// <param name="strSearchText"></param>
/// <returns></returns>
public static int[] SearchData(FarPoint.Win.Spread.FpSpread fp, int intSheetIndex, string strSearchText)
{
Dictionary <int, int> q = new Dictionary <int, int>();
int x = 0;
int y = 0;
while (y != -1)
{
fp.Search(intSheetIndex, strSearchText, false, false, false, true, y, x, ref y, ref x);
if (y == -1)
{
break;
}
if (y != -1 && !q.ContainsKey(y))
{
q.Add(y, y);
}
if (x >= fp.Sheets[intSheetIndex].Columns.Count - 1)
{
if (y >= fp.Sheets[intSheetIndex].Rows.Count - 1)
{
break;
}
else
{
x = 0;
y++;
}
}
else
{
x++;
}
}
int[] intRows = new int[q.Count];
y = 0;
foreach (object intRow in q.Values)
{
intRows[y] = (int)intRow;
y++;
}
return(intRows);
}
private void SetActiveCell(FarPoint.Win.Spread.FpSpread spread, FarPoint.Win.Spread.SheetView sheet, int row, int column)
{
if (sheet.RowCount == 0 || sheet.Cells[row, column] == null)
{
return;
}
if (sheet.ActiveRow != null)
{
sheet.ActiveRow.BackColor = Color.White;
}
sheet.Rows[row].BackColor = Constants.Constants.ACTIVE_COLOR;
sheet.SetActiveCell(row, column);
spread.ShowActiveCell(VerticalPosition.Nearest, HorizontalPosition.Nearest);
}
public void UpdateEquation(FarPoint.Win.Spread.FpSpread FpSpread)
{
foreach (FarPoint.Win.Spread.SheetView Sheet in FpSpread.Sheets)
{
//支持浮动的公式
foreach (IElement Element in Sheet.DrawingContainer.ContainedObjects)
{
if (Element is EquationShape)
{
EquationShape Shape = Element as EquationShape;
Shape.Locked = false;
}
}
}
}
/// <summary>
/// row를 삭제 한다.
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="intDelIndex">삭제 위치 : '-1'이면 마지막부터 위로 삭제 시킨다.</param>
/// <param name="intDelRowCount"></param>
public static void Invoke_DeleteRow(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, int intDelIndex, int intDelRowCount)
{
if (fp.InvokeRequired)
{
fp.Invoke(new delInvoke_AddRow(Invoke_DeleteRow), new object[] { fp, sv, intDelIndex, intDelRowCount });
return;
}
if (intDelIndex < 0)
{
intDelIndex = sv.Rows.Count - intDelRowCount;
}
sv.Rows.Remove(intDelIndex, intDelRowCount);
}
public static bool Excel_SaveAs(FarPoint.Win.Spread.FpSpread fp)
{
SaveFileDialog sf = new SaveFileDialog();
sf.Filter = "Excel files (*.xls)|*.xls |All files (*.*) | *.*";
if (sf.ShowDialog() != DialogResult.OK)
{
return(false);
}
//if (sf.FileName == null || sf.FileName == string.Empty) return;
fp.SaveExcel(sf.FileName, FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly);
return(true);
}
/// <summary>
/// 셀에 값을 변경한다.
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="Y"></param>
/// <param name="X"></param>
/// <param name="objValue"></param>
public static void Invoke_Cell_Value(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, int Y, int X, object objValue)
{
if (fp.InvokeRequired)
{
fp.Invoke(new delInvoke_Cell_Value(Invoke_Cell_Value), new object[] { fp, sv, Y, X, objValue });
return;
}
try
{
sv.Cells[Y, X].Value = objValue;
}
catch (Exception ex)
{
log.WLog_Exception(string.Format("{0}[sv]{1}[y}{2}[x]{3][obj]{4}", "Invoke_Cell_Value", fp.Name, Y, X, objValue), ex);
}
}
private static void CreateTitle(string fname, string title, string dw)
{
FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread();
fps.OpenExcel(fname);
SheetView sv = fps.Sheets[0];
int ColumnCount = sv.NonEmptyColumnCount;
int RowCount = sv.NonEmptyRowCount;
//sv.ColumnCount = ColumnCount;
//sv.RowCount = RowCount;
sv.AddRows(0, 2);
sv.Cells[0, 0].Text = title;
sv.Cells[0, 0].Font = new System.Drawing.Font("宋体", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[0, 0].HorizontalAlignment = CellHorizontalAlignment.Center;
sv.Cells[0, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[0, 0].Row.Height = 50;
sv.Cells[0, 0].ColumnSpan = ColumnCount;
sv.Cells[1, 0].Text = dw;
sv.Cells[1, 0].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[1, 0].HorizontalAlignment = CellHorizontalAlignment.Right;
sv.Cells[1, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[1, 0].ColumnSpan = ColumnCount;
for (int i = 0; i < ColumnCount; i++)
{
sv.Cells[2, i].Row.Height = 40;
sv.Cells[2, i].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
}
sv.AddRows(RowCount + 2, 2);
sv.Cells[RowCount + 2, 0].Text = "建表时间:" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;
sv.Cells[RowCount + 2, 0].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[RowCount + 2, 0].HorizontalAlignment = CellHorizontalAlignment.Right;
sv.Cells[RowCount + 2, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[RowCount + 2, 0].ColumnSpan = ColumnCount;
fps.SaveExcel(fname);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器
/// 修改此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.fpSpread1 = new FarPoint.Win.Spread.FpSpread();
this.fpSpread1_Sheet1 = new FarPoint.Win.Spread.SheetView();
((System.ComponentModel.ISupportInitialize)(this.fpSpread1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.fpSpread1_Sheet1)).BeginInit();
this.SuspendLayout();
//
// fpSpread1
//
this.fpSpread1.Dock = System.Windows.Forms.DockStyle.Fill;
this.fpSpread1.EditModePermanent = true;
this.fpSpread1.EditModeReplace = true;
this.fpSpread1.Location = new System.Drawing.Point(0, 0);
this.fpSpread1.Name = "fpSpread1";
this.fpSpread1.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
this.fpSpread1_Sheet1
});
this.fpSpread1.Size = new System.Drawing.Size(536, 360);
this.fpSpread1.TabIndex = 0;
this.fpSpread1.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.fpSpread1_KeyPress);
this.fpSpread1.EditModeOn += new System.EventHandler(this.fpSpread1_EditModeOn);
this.fpSpread1.EditModeOff += new System.EventHandler(this.fpSpread1_EditModeOff);
this.fpSpread1.ComboSelChange += new FarPoint.Win.Spread.EditorNotifyEventHandler(this.fpSpread1_ComboSelChange);
this.fpSpread1.Change += new FarPoint.Win.Spread.ChangeEventHandler(this.fpSpread1_Change);
this.fpSpread1.EditChange += new FarPoint.Win.Spread.EditorNotifyEventHandler(this.fpSpread1_EditChange);
//
// fpSpread1_Sheet1
//
this.fpSpread1_Sheet1.Reset();
this.fpSpread1_Sheet1.SheetName = "Sheet1";
//
// fpInput
//
this.Controls.Add(this.fpSpread1);
this.Name = "fpInput";
this.Size = new System.Drawing.Size(536, 360);
this.Load += new System.EventHandler(this.fpInput_Load);
((System.ComponentModel.ISupportInitialize)(this.fpSpread1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.fpSpread1_Sheet1)).EndInit();
this.ResumeLayout(false);
}
/// <summary>
/// 범위에 ForeColor와 BackColor를 변경하여 준다..
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="intStRow">시작 row</param>
/// <param name="intEdRow">종료 row</param>
/// <param name="intStCol">시작 col</param>
/// <param name="intEdCol">종료 col</param>
/// <param name="colFore">object color - 변경 안할시에는 null</param>
/// <param name="colBack">object color - 변경 안할시에는 null</param>
public static void Invoke_CellRange_SetRowColor(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, int intStRow, int intEdRow, int intStCol, int intEdCol,
object colFore, object colBack)
{
if (fp.InvokeRequired)
{
fp.Invoke(new delInvoke_CellRange_SetRowColor(Invoke_CellRange_SetRowColor), new object[] { fp, sv, intStRow, intEdRow, intStCol, intEdCol, colFore, colBack });
return;
}
if (colFore != null)
{
sv.Cells[intStRow, intStCol, intEdRow, intEdCol].ForeColor = (Color)colFore;
}
if (colBack != null)
{
sv.Cells[intStRow, intStCol, intEdRow, intEdCol].BackColor = (Color)colBack;
}
fp.Refresh();
}
/// <summary>
/// databinding with field
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="dt"></param>
/// <param name="strField"></param>
public static void Invoke_DataSource(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, DataView dt, string[] strField, StBindingOption bindingOption)
{
if (fp.InvokeRequired)
{
fp.Invoke(new delInvoke_DataSource2(Invoke_DataSource), new object[] { fp, sv, dt, strField, bindingOption });
return;
}
try
{
sv.AutoGenerateColumns = bindingOption.AutoGenerateColumns;
sv.DataAutoHeadings = bindingOption.DataAutoHeadings;
sv.DataAutoCellTypes = bindingOption.DataAutoCellTypes;
sv.DataAutoSizeColumns = bindingOption.DataAutoSizeColumns;
sv.Rows.Count = 0;
if (dt != null)
{
sv.DataSource = dt;
}
if (strField == null)
{
return;
}
for (int x = 0; x < strField.Length; x++)
{
sv.Columns[x].DataField = strField[x];
}
}
catch (Exception ex)
{
log.WLog_Exception(string.Format("{0}[fp]{1}", "Invoke_DataSource", fp.Name), ex);
}
}
/// <summary>
/// 해당 Row의 forecolor과 backcolor을 변경한다.
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="intRow"></param>
/// <param name="colFore">null 이면 변경 않</param>
/// <param name="colBack">null 이면 변경 않</param>
public static void Invoke_Row_SetColor(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, int intRow, object colFore, object colBack)
{
if (fp.InvokeRequired)
{
fp.Invoke(new delInvoke_Row_SetColor(Invoke_Row_SetColor), new object[] { fp, sv, intRow, colFore, colBack });
return;
}
if (intRow >= sv.RowCount)
{
return;
}
if (colBack != null)
{
sv.Rows[intRow].BackColor = (Color)colBack;
}
if (colFore != null)
{
sv.Rows[intRow].ForeColor = (Color)colFore;
}
fp.Refresh();
}
protected virtual void Export()
{
if (this.bTabControl1.SelectedIndex == 0)
{
//this.bsprData.Export(false);
string file = "";
bool bProtect = this.bsprData.ActiveSheet.Protect;
this.bsprData.ActiveSheet.Protect = false;
SaveFileDialog openDlg = new SaveFileDialog();
openDlg.Filter = "Excel Files (*.xls)|*.xls";
openDlg.FileName = "";
openDlg.DefaultExt = ".xls";
openDlg.CheckFileExists = false;
openDlg.CheckPathExists = true;
DialogResult res = openDlg.ShowDialog();
if (res != DialogResult.OK)
{
return;
}
file = openDlg.FileName;
FarPoint.Win.Spread.SheetView spread_Sheet1 = new FarPoint.Win.Spread.SheetView();
spread_Sheet1.SheetName = "_ExcelExportSheet";
FarPoint.Win.Spread.FpSpread spread = new FarPoint.Win.Spread.FpSpread();
spread.Sheets.Add(spread_Sheet1);
spread_Sheet1.Visible = true;
spread.ActiveSheet = spread_Sheet1;
byte[] buffer = null;
System.IO.MemoryStream stream = null;
this.bsprData.SetFilterVisible(false);
try
{
stream = new System.IO.MemoryStream();
this.bsprData.Save(stream, false);
buffer = stream.ToArray();
stream.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (stream != null)
{
stream.Dispose();
stream = null;
}
}
stream = new System.IO.MemoryStream(buffer);
spread.Open(stream);
if (stream != null)
{
stream.Dispose();
stream = null;
}
for (int i = spread.ActiveSheet.Columns.Count - 1; i >= 0; i--)
{
if (!spread.ActiveSheet.Columns[i].Visible)
{
spread.ActiveSheet.Columns[i].Remove();
}
}
spread.SaveExcel(file, FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly);
this.bsprData.ActiveSheet.Protect = bProtect;
string strMessage = "It was saved successfully. Do you open saved file?";
DialogResult result = MessageBox.Show(strMessage, "Open", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Classes\Applications\EXCEL.EXE");
if (key == null)
{
MSGHandler.DisplayMessage(MSGType.Error, "SPC_INFO_NEED_MS_OFFICE", null, null);
}
else
{
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo.FileName = file;
process.Start();
}
}
}
else
{
string file = "";
bool bProtect = this.bsprRawData.ActiveSheet.Protect;
this.bsprRawData.ActiveSheet.Protect = false;
SaveFileDialog openDlg = new SaveFileDialog();
openDlg.Filter = "Excel Files (*.xls)|*.xls";
openDlg.FileName = "";
openDlg.DefaultExt = ".xls";
openDlg.CheckFileExists = false;
openDlg.CheckPathExists = true;
DialogResult res = openDlg.ShowDialog();
if (res != DialogResult.OK)
{
return;
}
file = openDlg.FileName;
FarPoint.Win.Spread.SheetView spread_Sheet1 = new FarPoint.Win.Spread.SheetView();
spread_Sheet1.SheetName = "_ExcelExportSheet";
FarPoint.Win.Spread.FpSpread spread = new FarPoint.Win.Spread.FpSpread();
spread.Sheets.Add(spread_Sheet1);
spread_Sheet1.Visible = true;
spread.ActiveSheet = spread_Sheet1;
byte[] buffer = null;
System.IO.MemoryStream stream = null;
this.bsprRawData.SetFilterVisible(false);
try
{
stream = new System.IO.MemoryStream();
this.bsprRawData.Save(stream, false);
buffer = stream.ToArray();
stream.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (stream != null)
{
stream.Dispose();
stream = null;
}
}
stream = new System.IO.MemoryStream(buffer);
spread.Open(stream);
if (stream != null)
{
stream.Dispose();
stream = null;
}
for (int i = spread.ActiveSheet.Columns.Count - 1; i >= 0; i--)
{
if (!spread.ActiveSheet.Columns[i].Visible)
{
spread.ActiveSheet.Columns[i].Remove();
}
}
spread.SaveExcel(file, FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly);
this.bsprRawData.ActiveSheet.Protect = bProtect;
string strMessage = "It was saved successfully. Do you open saved file?";
DialogResult result = MessageBox.Show(strMessage, "Open", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Classes\Applications\EXCEL.EXE");
if (key == null)
{
MSGHandler.DisplayMessage(MSGType.Error, "SPC_INFO_NEED_MS_OFFICE", null, null);
}
else
{
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo.FileName = file;
process.Start();
}
}
}
}
public static void ExportExcel(GridControl gridControl)
{
//try
//{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = "";
saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
fname = saveFileDialog1.FileName;
//File.SetAttributes(fname, File.GetAttributes(fname) | FileAttributes.ReadOnly);
////Create the file.
//using (FileStream fs = new FileStream(fname, FileMode.OpenOrCreate, FileAccess.Read))
//{
// if (!fs.CanWrite)
// {
// MsgBox.Show("�ļ����ܱ������������ļ��Ƿ�");
// return;
// }
//}
// try
//{
// File.Move(fname,fname+"1");
//}
//catch
//{
// MsgBox.Show("������"+fname+"�����������ļ��������ļ������ļ���������λ�á�");
// return;
//}
//finally
//{
// File.Move(fname + "1",fname);
//}
try
{
gridControl.ExportToExcelOld(fname);
FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread();
fps.OpenExcel(fname);
SheetView sv = fps.Sheets[0];
for (int j = 0; j < sv.NonEmptyRowCount; j++)
{
for (int k = 0; k < sv.NonEmptyColumnCount; k++)
{
FarPoint.Win.Spread.CellType.NumberCellType temptype=new FarPoint.Win.Spread.CellType.NumberCellType();
sv.Cells[j, k].CellType = temptype;
}
}
fps.SaveExcel(fname);
// ����Ҫʹ�õ�Excel ����ӿ�
// ����Application ����,�˶����ʾ����Excel ����
Microsoft.Office.Interop.Excel.Application excelApp = null;
// ����Workbook����,�˶�����������
Microsoft.Office.Interop.Excel.Workbook workBook;
// ����Worksheet ����,�˶����ʾExecel �е�һ�Ź�����
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//ȡ������������
ws.Unprotect(Missing.Value);
//�����ݵ�����
int row = ws.UsedRange.Rows.Count;
//�����ݵ�����
int col = ws.UsedRange.Columns.Count;
//����һ������
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//�������ڵĵ�Ԫ���Զ�����
range.Select();
range.NumberFormatLocal="G/ͨ�ø�ʽ";
//����������
ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//���湤����
workBook.Save();
//�رչ�����
excelApp.Workbooks.Close();
if (MsgBox.ShowYesNo("�����ɹ����Ƿ���ĵ���") != DialogResult.Yes)
return;
System.Diagnostics.Process.Start(fname);
}
catch
{
MsgBox.Show("������"+fname+"�����������ļ��������ļ������ļ���������λ�á�");
return;
}
}
//return true;
//}
//catch { }
}
/// <summary>
/// databinding with field
/// </summary>
/// <param name="fp"></param>
/// <param name="sv"></param>
/// <param name="dt"></param>
/// <param name="strField"></param>
public static void Invoke_DataSource(FarPoint.Win.Spread.FpSpread fp, FarPoint.Win.Spread.SheetView sv, DataView dt, string[] strField)
{
Invoke_DataSource(fp, sv, dt, strField, new StBindingOption(false));
}
private static void CreateTitle(string fname,string title,string dw)
{
FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread();
fps.OpenExcel(fname);
SheetView sv = fps.Sheets[0];
int ColumnCount = sv.NonEmptyColumnCount;
int RowCount = sv.NonEmptyRowCount;
//sv.ColumnCount = ColumnCount;
//sv.RowCount = RowCount;
sv.AddRows(0, 2);
sv.Cells[0, 0].Text = title;
sv.Cells[0, 0].Font = new System.Drawing.Font("����", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[0, 0].HorizontalAlignment = CellHorizontalAlignment.Center;
sv.Cells[0, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[0, 0].Row.Height = 50;
sv.Cells[0, 0].ColumnSpan = ColumnCount;
sv.Cells[1, 0].Text = dw;
sv.Cells[1, 0].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[1, 0].HorizontalAlignment = CellHorizontalAlignment.Right;
sv.Cells[1, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[1, 0].ColumnSpan = ColumnCount;
for (int i = 0; i < ColumnCount; i++)
{
sv.Cells[2, i].Row.Height = 40;
sv.Cells[2, i].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
}
sv.AddRows(RowCount + 2, 2);
sv.Cells[RowCount + 2, 0].Text = "����ʱ��:" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;
sv.Cells[RowCount + 2, 0].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[RowCount + 2, 0].HorizontalAlignment = CellHorizontalAlignment.Right;
sv.Cells[RowCount + 2, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[RowCount + 2, 0].ColumnSpan = ColumnCount;
fps.SaveExcel(fname);
}
/// <summary>
/// 프린터
/// </summary>
/// <param name="fp"></param>
public static void PrintOption_Show(FarPoint.Win.Spread.FpSpread fp)
{
frmPrintOpts frm = new frmPrintOpts(fp);
frm.ShowDialog();
}
public static void ExportToExcelOld(string title, string dw, GridControl gc)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = "";
saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
fname = saveFileDialog1.FileName;
gc.ExportToExcelOld(fname);
// gc.ExportToExcelOld(fname);
FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread();
fps.OpenExcel(fname);
SheetView sv = fps.Sheets[0];
int ColumnCount = sv.NonEmptyColumnCount+2;
int RowCount = sv.NonEmptyRowCount;
//sv.ColumnCount = ColumnCount;
//sv.RowCount = RowCount;
sv.AddRows(0, 2);
sv.Cells[0, 0].Text = title;
sv.Cells[0, 0].Font = new System.Drawing.Font("����", 16F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[0, 0].HorizontalAlignment = CellHorizontalAlignment.Center;
sv.Cells[0, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[0, 0].Row.Height = 50;
sv.Cells[0, 0].ColumnSpan = ColumnCount;
sv.Cells[1, 0].Text = dw;
sv.Cells[1, 0].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[1, 0].HorizontalAlignment = CellHorizontalAlignment.Right;
sv.Cells[1, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[1, 0].ColumnSpan = ColumnCount;
for (int i = 0; i < ColumnCount; i++)
{
sv.Cells[2, i].Column.Width = 150;
sv.Cells[2, i].Row.Height = 40;
sv.Cells[2, i].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
}
//FarPoint.Win.Spread.CellType.NumberCellType numberCellTypes= new FarPoint.Win.Spread.CellType.NumberCellType();
//numberCellTypes.DecimalPlaces = 1;
//for (int i = 0; i < ColumnCount; i++)
//{
// for (int j = 2; i < RowCount; i++)
// {
// sv.Cells[j, i].CellType = numberCellTypes;
// }
//}
sv.AddRows(RowCount + 2, 2);
sv.Cells[RowCount + 2, 0].Text = "����ʱ��:" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;
sv.Cells[RowCount + 2, 0].Font = new System.Drawing.Font("����", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
sv.Cells[RowCount + 2, 0].HorizontalAlignment = CellHorizontalAlignment.Right;
sv.Cells[RowCount + 2, 0].VerticalAlignment = CellVerticalAlignment.Center;
sv.Cells[RowCount + 2, 0].ColumnSpan = ColumnCount;
//sv.SetColumnVisible(ColumnCount - 1, false);
//sv.SetColumnVisible(ColumnCount - 2, false);
for (int j = 0; j < sv.NonEmptyRowCount; j++)
{
for (int k = 0; k < sv.NonEmptyColumnCount; k++)
{
sv.Cells[j,k].CellType = new FarPoint.Win.Spread.CellType.NumberCellType();
}
}
fps.SaveExcel(fname);
// ����Ҫʹ�õ�Excel ����ӿ�
// ����Application ����,�˶����ʾ����Excel ����
Microsoft.Office.Interop.Excel.Application excelApp = null;
// ����Workbook����,�˶�����������
Microsoft.Office.Interop.Excel.Workbook workBook;
// ����Worksheet ����,�˶����ʾExecel �е�һ�Ź�����
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//ȡ������������
ws.Unprotect(Missing.Value);
//�����ݵ�����
int row = ws.UsedRange.Rows.Count;
//�����ݵ�����
int col = ws.UsedRange.Columns.Count;
//����һ������
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//�������ڵĵ�Ԫ���Զ�����
range.Select();
range.NumberFormatLocal = "G/ͨ�ø�ʽ";
//����������
ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//���湤����
workBook.Save();
//�رչ�����
excelApp.Workbooks.Close();
if (MsgBox.ShowYesNo("�����ɹ����Ƿ���ĵ���") != DialogResult.Yes)
return;
System.Diagnostics.Process.Start(fname);
}
catch
{
MsgBox.Show("������" + fname + "�����������ļ��������ļ������ļ���������λ�á�");
return;
}
}
}
public static void ExportExcel(GridControl gridControl)
{
//try
//{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = "";
saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
fname = saveFileDialog1.FileName;
try
{
gridControl.ExportToExcelOld(fname);
FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread();
fps.OpenExcel(fname);
SheetView sv = fps.Sheets[0];
for (int j = 0; j < sv.NonEmptyRowCount; j++)
{
for (int k = 0; k < sv.NonEmptyColumnCount; k++)
{
sv.Cells[j,k].CellType = new FarPoint.Win.Spread.CellType.NumberCellType();
}
}
fps.SaveExcel(fname);
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//取消保护工作表
ws.Unprotect(Missing.Value);
//有数据的行数
int row = ws.UsedRange.Rows.Count;
//有数据的列数
int col = ws.UsedRange.Columns.Count;
//创建一个区域
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//设区域内的单元格自动换行
range.Select();
range.NumberFormatLocal = "G/通用格式";
//保护工作表
ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//保存工作簿
workBook.Save();
//关闭工作簿
excelApp.Workbooks.Close();
if (MsgBox.ShowYesNo("导出成功，是否打开该文档？") != DialogResult.Yes)
return;
System.Diagnostics.Process.Start(fname);
}
catch
{
MsgBox.Show("无法保存" + fname + "。请用其他文件名保存文件，或将文件存至其他位置。");
return;
}
}
//return true;
//}
//catch { }
}
public static void ExportToExcelOld(GridControl gridControl,string title,string dw)
{
//try
//{
// Control.CheckForIllegalCrossThreadCalls = false;
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = "";
saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
FrmGress frm = new FrmGress();
// Thread thread = new Thread(new ThreadStart(frm.Show));
// thread.Start();
fname = saveFileDialog1.FileName;
try
{
gridControl.ExportToExcelOld(fname);
CreateTitle(fname, title, dw);
FarPoint.Win.Spread.FpSpread fps = new FarPoint.Win.Spread.FpSpread();
fps.OpenExcel(fname);
SheetView sv = fps.Sheets[0];
for (int j = 0; j < sv.NonEmptyRowCount; j++)
{
for (int k = 0; k < sv.NonEmptyColumnCount; k++)
{
sv.Cells[j, k].CellType = new FarPoint.Win.Spread.CellType.NumberCellType();
}
}
fps.SaveExcel(fname);
// ����Ҫʹ�õ�Excel ����ӿ�
// ����Application ����,�˶����ʾ����Excel ����
Microsoft.Office.Interop.Excel.Application excelApp = null;
// ����Workbook����,�˶�����������
Microsoft.Office.Interop.Excel.Workbook workBook;
// ����Worksheet ����,�˶����ʾExecel �е�һ�Ź�����
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//ȡ������������
ws.Unprotect(Missing.Value);
//�����ݵ�����
int row = ws.UsedRange.Rows.Count;
//�����ݵ�����
int col = ws.UsedRange.Columns.Count;
//����һ������
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//�������ڵĵ�Ԫ���Զ�����
range.Select();
range.NumberFormatLocal = "G/ͨ�ø�ʽ";
//����������
ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//���湤����
workBook.Save();
//�رչ�����
excelApp.Workbooks.Close();
if (MsgBox.ShowYesNo("�����ɹ����Ƿ���ĵ���") != DialogResult.Yes)
return;
System.Diagnostics.Process.Start(fname);
}
catch
{
MsgBox.Show("������" + fname + "�����������ļ��������ļ������ļ���������λ�á�");
return;
}
}
}
