private void AddData(FarPoint.Win.Spread.FpSpread obj)
{
try
{
wait1 = new WaitDialogForm("", "正在加载数据, 请稍候...");
//打开Excel表格
//清空工作表
fpSpread1.Sheets.Clear();
obj.OpenExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet10_1.xls");
PF.SpreadRemoveEmptyCells(obj);
this.barEditItem1.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
this.AddCellChanged();
S10_11.AddBarEditItems(this.barEditItem1, this.barEditItem2, this);//添加下拉菜单内容
wait1.Close();
}
catch (System.Exception e)
{
//如果打开出错则重新生成并保存
LoadData();
//判断文件夹是否存在，不存在则创建
if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls"))
{
Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls");
}
//保存EXcel文件
obj.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet10_1.xls", FarPoint.Excel.ExcelSaveFlags.NoFlagsSet);
// 定义要使用的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();
string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\Sheet10_1.xls";
workBook = excelApp.Workbooks.Open(filename, 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.WrapText = true;
//保护工作表
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();
}
wait1.Close();
}
//保存
private void barBtnSave_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
WaitDialogForm wait = null;
wait = new WaitDialogForm("", "正在保存数据, 请稍候...");
//判断文件夹是否存在，不存在则创建
if (!Directory.Exists(Tcommon.CurrentPath + Tcommon.ExcelDir))
{
Directory.CreateDirectory(Tcommon.CurrentPath + Tcommon.ExcelDir);
}
//保存excel文件
try
{
//保存excel文件
fpSpread1.SaveExcel(Tcommon.ExcelFilePath + ExcelName);
//以下是打开文件设表格自动换行
// 定义要使用的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();
string filename = Tcommon.ExcelFilePath + ExcelName;
workBook = excelApp.Workbooks.Open(filename, 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.WrapText = true;
//保护工作表
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();
wait.Close();
MessageBox.Show("保存成功");
}
catch (System.Exception ee)
{
wait.Close();
MessageBox.Show("保存错误！确定您安装有Office Excel,或者关闭所有Excel文件重试");
}
}
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);
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;
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;
//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 { }
}
public void WriteExcell(DataSet ds, string FileName, string strSavePath, string FilePath)
{
string path = FilePath + @"CTP_RFQ_FormatFile.xls";
ExlApp = new Microsoft.Office.Interop.Excel.Application();
ExlWrkBook = ExlApp.Workbooks.Open(path, 0,
true,
5,
"",
"",
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
ExlWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExlWrkBook.ActiveSheet;
ExlWrkSheet.Cells[6, 15] = ds.Tables[0].Rows[0]["Quotation_ID"].ToString();
ExlWrkSheet.Cells[2, 3] = ds.Tables[0].Rows[0]["QTN_Contract_Code"].ToString();
ExlWrkSheet.Cells[11, 3] = ds.Tables[0].Rows[0]["System_Description"].ToString();
ExlWrkSheet.Cells[7, 3] = ds.Tables[0].Rows[0]["Full_NAME"].ToString();
ExlWrkSheet.Cells[5, 13] = DateTime.Now.ToString("yyyy/MM/dd");
ExlWrkSheet.Cells[10, 3] = ds.Tables[0].Rows[0]["Dept_Name"].ToString();
int i = 15;
foreach (DataRow dr in ds.Tables[1].Rows)
{
ExlWrkSheet.Cells[i, 1] = dr["ROWNUM"].ToString();
ExlWrkSheet.Cells[i, 3] = dr["Part_Number"].ToString();
ExlWrkSheet.Cells[i, 4] = dr["QTN_Item_ID"].ToString();
ExlWrkSheet.Cells[i, 5] = dr["Short_Description"].ToString();
ExlWrkSheet.Cells[i + 1, 5] = dr["Long_Description"].ToString();
ExlWrkSheet.Cells[i + 2, 5] = dr["Purchaser_Remark"].ToString();
ExlWrkSheet.Cells[i, 6] = dr["Unit_and_Packings"].ToString();
ExlWrkSheet.Cells[i, 7] = dr["Unit_and_Packings"].ToString();
i = i + 3;
}
ExlWrkSheet.get_Range("A" + (ds.Tables[1].Rows.Count * 3 + 15).ToString(), "N1639").Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
ExlWrkSheet.get_Range("G9", "G9").NumberFormat = "#0.00";
ExlWrkSheet.get_Range("M1", "M10").EntireColumn.Hidden = true;
ExlWrkSheet.Protect("tessmave", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing);
ExlWrkBook.SaveAs(strSavePath + FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
string destFile = Server.MapPath("../Uploads/Purchase") + "\\" + FileName;;
File.Copy(strSavePath + FileName, destFile, true);
ExlWrkBook.Close(null, null, null);
//ExlApp.Workbooks.Close();
ExlApp.Quit();
Marshal.ReleaseComObject(ExlApp);
Marshal.ReleaseComObject(ExlWrkSheet);
Marshal.ReleaseComObject(ExlWrkBook);
}
public void WriteExcell(DataSet ds, string Requisition, string FileName, string strSavePath, string FilePath)
{
CheckExcellProcesses();
//Pick up the RFQ format file
// string strPath= Server.MapPath(".") + "\Technical\ExcelFile\RFQ_FormatFile.xls";
// string path = System.AppDomain.CurrentDomain.BaseDirectory + @"RFQ_FormatFile.xls";
string path = FilePath + @"RFQ_FormatFile.xls";
ExlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
ExlWrkBook = ExlApp.Workbooks.Open(path, 0,
true,
5,
"",
"",
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
ExlWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExlWrkBook.ActiveSheet;
ExlWrkSheet.Cells[1, 3] = ds.Tables[2].Rows[0]["Vessel_name"].ToString();
ExlWrkSheet.Cells[2, 3] = ds.Tables[0].Rows[0]["QUOTATION_CODE"].ToString();
ExlWrkSheet.Cells[3, 3] = ds.Tables[0].Rows[0]["Quotation_Due_Date"].ToString();
ExlWrkSheet.Cells[4, 3] = ds.Tables[0].Rows[0]["SHORT_NAME"].ToString();
ExlWrkSheet.Cells[11, 3] = ds.Tables[0].Rows[0]["System_Description"].ToString();
ExlWrkSheet.Cells[6, 12] = ds.Tables[0].Rows[0]["BUYER_COMMENTS"].ToString();
ExlWrkSheet.Cells[7, 3] = ds.Tables[0].Rows[0]["SHORT_NAME"].ToString();
ExlWrkSheet.Cells[1, 13] = ds.Tables[0].Rows[0]["Vessel_code"].ToString();
ExlWrkSheet.Cells[2, 13] = ds.Tables[0].Rows[0]["DOCUMENT_CODE"].ToString();
ExlWrkSheet.Cells[3, 13] = ds.Tables[0].Rows[0]["ITEM_SYSTEM_CODE"].ToString();
ExlWrkSheet.Cells[5, 13] = DateTime.Now.ToString("yyyy/MM/dd");
ExlWrkSheet.Cells[6, 13] = ds.Tables[0].Rows[0]["Quotation_CODE"].ToString();
ExlWrkSheet.Cells[7, 13] = ds.Tables[0].Rows[0]["QUOTATION_SUPPLIER"].ToString();
ExlWrkSheet.Cells[10, 3] = ds.Tables[0].Rows[0]["Name_Dept"].ToString();
if (ds.Tables[4].Rows.Count > 0)
{
ExlWrkSheet.Cells[10, 7] = ds.Tables[4].Rows[0]["MechInfo"].ToString();
ExlWrkSheet.Cells[11, 7] = ds.Tables[4].Rows[0]["Model_Type"].ToString();
ExlWrkSheet.Cells[12, 7] = ds.Tables[4].Rows[0]["MakerName"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerAddress"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerCity"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerEmail"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerCONTACT"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerPhone"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerFax"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["MakerTELEX"].ToString()
+ ' ' + ds.Tables[4].Rows[0]["System_Serial_Number"].ToString();
}
int i = 15;
foreach (DataRow dr in ds.Tables[1].Rows)
{
ExlWrkSheet.Cells[i, 1] = dr[0].ToString();
ExlWrkSheet.Cells[i, 2] = dr[10].ToString();
ExlWrkSheet.Cells[i, 3] = dr[9].ToString();
ExlWrkSheet.Cells[i, 4] = dr[1].ToString();
ExlWrkSheet.Cells[i, 5] = dr[4].ToString();
ExlWrkSheet.Cells[i + 1, 5] = dr[5].ToString();
ExlWrkSheet.Cells[i + 2, 5] = dr[8].ToString();
ExlWrkSheet.Cells[i, 6] = dr[6].ToString();
ExlWrkSheet.Cells[i, 7] = dr[7].ToString();
// value will come in case of CTP
if (!dr.IsNull("quoted_rate"))
{
ExlWrkSheet.Cells[i, 8] = dr["quoted_rate"].ToString();
}
if (!dr.IsNull("quoted_discount"))
{
ExlWrkSheet.Cells[i, 9] = dr["quoted_discount"].ToString();
}
i = i + 3;
}
ExlWrkSheet.get_Range("A" + (ds.Tables[1].Rows.Count * 3 + 15).ToString(), "N1639").Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
ExlWrkSheet.Cells[ds.Tables[1].Rows.Count * 3 + 15, 1] = ds.Tables[3].Rows[0]["LegalTerm"].ToString();
//ExlWrkSheet.get_Range("F15", "H" + (ds.Tables[1].Rows.Count * 3 + 14).ToString()).Locked = false;
//ExlWrkSheet.get_Range("J15", "J" + (ds.Tables[1].Rows.Count * 3 + 14).ToString()).Locked = false;
//ExlWrkSheet.get_Range("I7", "I9").Locked = false;
//ExlWrkSheet.get_Range("I10", "K11").Locked = false;
ExlWrkSheet.get_Range("G9", "G9").NumberFormat = "#0.00";
//ExlWrkSheet.get_Range("I5", "I6").Locked = false;
//ExlWrkSheet.get_Range("L1", "M10").EntireColumn.Hidden = true;
ExlWrkSheet.get_Range("M1", "M10").EntireColumn.Hidden = true;
ExlWrkSheet.Protect("tessmave", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing);
ExlWrkBook.SaveAs(strSavePath + FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
string destFile = Server.MapPath("../Uploads/Purchase") + "\\" + FileName;;
File.Copy(strSavePath + FileName, destFile, true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
ExlWrkBook.Close(null, null, null);
ExlApp.Workbooks.Close();
ExlApp.Quit();
KillExcel();
}
}
/// <summary>
/// 导出EXCEL
/// </summary>
/// <param name="gridControl"></param>
/// <param name="title"></param>
/// <param name="dw"></param>
public static void ExportToExcelOld(GridControl gridControl, string title, string dw)
{
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 = 1; j < sv.NonEmptyColumnCount; j++)
//{
// for (int k = 0; k < sv.NonEmptyRowCount; k++)
// {
// sv.Cells[k, j].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();
// CreateTitle(fname, title, dw);
if (MsgBox.ShowYesNo("导出成功，是否打开该文档？") != DialogResult.Yes)
{
return;
}
System.Diagnostics.Process.Start(fname);
}
catch (Exception ee)
{
MsgBox.Show("无法保存" + fname + "。请用其他文件名保存文件，或将文件存至其他位置。");
return;
}
}
}
private void fpSpread_addsheet()
{
WaitDialogForm wait = null;
wait = new WaitDialogForm("", "正在加载数据, 请稍候...");
try
{
//打开Excel表格
//清空工作表
fpSpread1.Sheets.Clear();
fpSpread1.OpenExcel(Tcommon.ExcelFilePath + ExcelName);
TC.SpreadRemoveEmptyCells(fpSpread1);
}
catch (System.Exception e)
{
//如果打开出错则重新生成并保存
fpSpread1.Sheets.Clear();
Firstadddata();
//判断文件夹是否存在，不存在则创建
if (!Directory.Exists(Tcommon.CurrentPath + Tcommon.ExcelDir))
{
Directory.CreateDirectory(Tcommon.CurrentPath + Tcommon.ExcelDir);
}
//保存excel文件
fpSpread1.SaveExcel(Tcommon.ExcelFilePath + ExcelName);
//以下是打开文件设表格自动换行
// 定义要使用的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();
string filename = Tcommon.ExcelFilePath + ExcelName;
workBook = excelApp.Workbooks.Open(filename, 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.WrapText = true;
//保护工作表
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();
}
wait.Close();
}
//private Microsoft.Office.Interop.Excel.Range range;
public void WriteExcell(DataSet ds, string Requisition, string FileName, string strSavePath)
{
string path = System.AppDomain.CurrentDomain.BaseDirectory + @"Technical\ExcelFile\PO_FormatFile.xls";
ExlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
ExlWrkBook = ExlApp.Workbooks.Open(path, 0,
true,
5,
"",
"",
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
ExlWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExlWrkBook.ActiveSheet;
ExlWrkSheet.Cells[2, 3] = ds.Tables[0].Rows[0].ItemArray[1].ToString();
ExlWrkSheet.Cells[2, 6] = ds.Tables[0].Rows[0].ItemArray[0].ToString();
ExlWrkSheet.Cells[3, 6] = DateTime.Now.ToString("dd/MM/yyyy");
ExlWrkSheet.Cells[3, 3] = DateTime.Now.ToString("dd/MM/yyyy");
ExlWrkSheet.Cells[4, 3] = ds.Tables[0].Rows[0].ItemArray[8].ToString();
ExlWrkSheet.Cells[5, 3] = ds.Tables[0].Rows[0].ItemArray[11].ToString();
//ExlWrkSheet.Cells[3, 4] = "";
//ExlWrkSheet.Cells[1, 11] = ds.Tables[2].Rows[0].ItemArray[0].ToString();
ExlWrkSheet.Cells[2, 11] = "'" + ds.Tables[0].Rows[0].ItemArray[1].ToString() + "'";
ExlWrkSheet.Cells[3, 11] = "'" + ds.Tables[0].Rows[0].ItemArray[3].ToString() + "'";
ExlWrkSheet.Cells[4, 11] = ds.Tables[0].Rows[0].ItemArray[2].ToString();
ExlWrkSheet.Cells[5, 11] = ds.Tables[0].Rows[0].ItemArray[4].ToString();
ExlWrkSheet.Cells[4, 3] = ds.Tables[0].Rows[0].ItemArray[8].ToString();
ExlWrkSheet.Cells[8, 3] = ds.Tables[0].Rows[0].ItemArray[9].ToString();
ExlWrkSheet.Cells[9, 3] = ds.Tables[0].Rows[0].ItemArray[7].ToString();
ExlWrkSheet.Cells[6, 11] = ds.Tables[0].Rows[0].ItemArray[6].ToString();
int i = 11;
if (ds.Tables[2].Rows.Count > 0)
{
ExlWrkSheet.Cells[1, 3] = ds.Tables[2].Rows[0].ItemArray[1].ToString();
ExlWrkSheet.Cells[1, 11] = ds.Tables[2].Rows[0].ItemArray[0].ToString();
}
//ExlWrkSheet.get_Range("A15", "I" + (ds.Tables[1].Rows.Count *3).ToString()).Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
foreach (DataRow dr in ds.Tables[1].Rows)
{
ExlWrkSheet.Cells[i, 1] = dr[0].ToString();
ExlWrkSheet.Cells[i, 2] = dr[1].ToString();
ExlWrkSheet.Cells[i, 3] = dr[4].ToString();
ExlWrkSheet.Cells[i + 1, 3] = dr[5].ToString();
ExlWrkSheet.Cells[i + 2, 3] = dr[8].ToString();
ExlWrkSheet.Cells[i, 4] = dr[6].ToString();
ExlWrkSheet.Cells[i, 5] = dr[7].ToString();
ExlWrkSheet.Cells[i, 6] = dr[9].ToString();
ExlWrkSheet.Cells[i, 7] = dr[10].ToString();
//ExlWrkSheet.Cells[i, 6] = dr[8].ToString();
ExlWrkSheet.Cells[i, 9] = dr[11].ToString();
i = i + 3;
}
ExlWrkSheet.Cells[1637, 7] = ds.Tables[0].Rows[0].ItemArray[13].ToString();
ExlWrkSheet.Cells[1639, 5] = ds.Tables[0].Rows[0].ItemArray[12].ToString();
ExlWrkSheet.Cells[1640, 5] = ds.Tables[1].Rows[0].ItemArray[13].ToString();
ExlWrkSheet.Cells[1641, 5] = ds.Tables[1].Rows[0].ItemArray[12].ToString();
ExlWrkSheet.get_Range("A" + (ds.Tables[1].Rows.Count * 3 + 11).ToString(), "I1633").Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
//ExlWrkSheet.get_Range("F11", "G" + (ds.Tables[1].Rows.Count * 3 + 10).ToString()).Locked = false;
//ExlWrkSheet.get_Range("I11", "I" + (ds.Tables[1].Rows.Count * 3 + 10).ToString()).Locked = false;
ExlWrkSheet.get_Range("G9", "G9").Locked = false;
ExlWrkSheet.get_Range("G9", "G9").NumberFormat = "#0.00";
ExlWrkSheet.get_Range("J1", "K5").EntireColumn.Hidden = true;
ExlWrkSheet.Protect("tessmave", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing);
if (!Directory.Exists(PO_RFQ_Generate.ExcelCleanup.GetDrive() + @"PURC/PO"))
{
Directory.CreateDirectory(PO_RFQ_Generate.ExcelCleanup.GetDrive() + @"PURC/PO");
}
//string Path = "";
//Path = @".\PURC\PO_Excel\" + Supplier;
ExlWrkBook.SaveAs(strSavePath + FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
ExlWrkBook.Close(null, null, null);
ExlApp.Workbooks.Close();
ExlApp.Quit();
Marshal.ReleaseComObject(ExlApp);
Marshal.ReleaseComObject(ExlWrkSheet);
Marshal.ReleaseComObject(ExlWrkBook);
}
}
public static void ExportToExcelOld(GridControl gridControl, int row, string col, bool bHe)
{
//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);
if (bHe)
{
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 row1 = ws.UsedRange.Rows.Count;
//有数据的列数
int col1 = ws.UsedRange.Columns.Count;
//创建一个区域
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row1, col1]);
//设区域内的单元格自动换行
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();
ChangeExcel(fname, row, col);
}
else
{
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 row1 = ws.UsedRange.Rows.Count;
//有数据的列数
int col1 = ws.UsedRange.Columns.Count;
//创建一个区域
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row1, col1]);
//设区域内的单元格自动换行
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;
}
}
}
