/// <summary>
/// 저장및 메모리 해제
/// </summary>
/// <param name="excelApp"></param>
/// <param name="wb"></param>
/// <param name="workSheet"></param>
public static void ExcelDispose(Excel.Application excelApp, Excel.Workbook wb, Excel._Worksheet workSheet)
{
wb.SaveAs(@"C:\users\user\Desktop\수강신청시간표", Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close(Type.Missing, Type.Missing, Type.Missing);
excelApp.Quit();
releaseObject(excelApp);
releaseObject(workSheet);
releaseObject(wb);
}
public void WriteData()
{
string fp = ChooseFilePath(); // Uses function below to find file path
Excel excel = new Excel(fp, 1); // Creates a new instance of the Excel class
excel.WriteToCell(6, 6, "BOOM");
excel.SaveAs(@"BOOM3.xlsx"); //The @ symbol adds the file path This PC/ My Documents
excel.Close();
addListItem("File with new content added saved in ThisPC/My Documents/ BOOM3.xlsx"); //Message saying what has happened
}
public static void SaveExcel(string filePath, Excel.Workbook workbook)
{
try
{
object misValue = System.Reflection.Missing.Value;
workbook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
}
catch (Exception ex)
{
throw ex;
}
}
public static void saveAsWorkBook(Excel.Workbook wb, string path)
{
wb.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
/// <summary>
/// 打印FPC软板批量卡和单体图以及辅料卡
/// </summary>
/// <param name="xlBook">当前工作簿</param>
/// <param name="row">取得的数据行</param>
/// <param name="lotId">当前批量卡序号</param>
/// <param name="danTiTuFullPath">当前单体图全路径</param>
/// <param name="dgvRow">当前dgv行</param>
/// <param name="cadApp">cad对象</param>
/// <param name="xlApp">excel对象</param>
/// <param name="productNum">生产编号</param>
/// <param name="iMaxPnlQty">最多多少pnl需要打一张辅料卡</param>
private void PrintFpcLotCard(
Excel.Workbook xlBook,
DataRow row,
string lotId,
string danTiTuFullPath,
DataGridViewRow dgvRow,
GcadApplication cadApp,
Excel.Application xlApp,
string productNum,
decimal iMaxPnlQty
)
{
//当前工作表
var xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
//设置值
//流程卡卡号
xlSheet.Shapes.AddTextEffect(
MsoPresetTextEffect.msoTextEffect2,
lotId,
"宋体",
18f,
MsoTriState.msoFalse,
MsoTriState.msoFalse,
430f,
15f
);
//所有需要填写数据的单元格
var xlRange = xlSheet.Range[xlSheet.Cells[5, 13], xlSheet.Cells[17, 15]];
xlRange.ShrinkToFit = true;
//下单日期
xlSheet.Range["N5"].Value = Convert.ToDateTime(row["order_date"]).ToString("yyyy-MM-dd");
//交货日期
xlSheet.Range["N6"].Value = Convert.ToDateTime(row["need_date"]).ToString("yyyy-MM-dd");
//订单数量
xlSheet.Range["N7"].Value = string.Format("{0} pcs", row["order_pcs_qty"]);
//总投料量
xlSheet.Range["N8"].Value = string.Format("{0} pcs", row["total_pcs_qty"]);
//本批数量
xlSheet.Range["N9"].Value = string.Format("{0} pnl", row["total_pnl_qty"]);
//单元格拆分
xlRange = xlSheet.Range[xlSheet.Cells[11, 13], xlSheet.Cells[17, 15]];
xlRange.MergeCells = false;
//合并单元格
for (int i = 11; i < 16; i++)
{
xlRange = xlSheet.Range[string.Format("N{0}:O{0}", i)];
xlRange.Merge();
}
for (int i = 16; i < 18; i++)
{
xlRange = xlSheet.Range[string.Format("M{0}:O{0}", i)];
xlRange.Merge();
}
//边框线
xlRange = xlSheet.Range[xlSheet.Cells[5, 13], xlSheet.Cells[17, 15]];
xlRange.ShrinkToFit = true;
xlRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
xlRange.Borders.Weight = Excel.XlBorderWeight.xlThin;
//投料单号
xlSheet.Range["N10"].Value = row["order_num"].ToString();
//起始卡号
xlSheet.Range["M11"].Value = "起始卡号";
xlSheet.Range["N11"].Value = row["first_lot_id"].ToString();
//结束卡号
xlSheet.Range["M12"].Value = "结束卡号";
xlSheet.Range["N12"].Value = row["last_lot_id"].ToString();
//单据状态
xlSheet.Range["M13"].Value = "单据状态";
xlSheet.Range["N13"].Value = row["state"].ToString();
//pnl含pcs数
xlSheet.Range["M14"].Value = "pnl含pcs数";
xlSheet.Range["N14"].Value = row["pnl_count_pcs"].ToString();
//pnl面积
xlSheet.Range["M15"].Value = "pnl面积";
xlSheet.Range["N15"].Value = string.Format("{0} m2", row["pnl_area"]);
//lot数
var iFirst = Convert.ToInt32(row["first_lot_id"].ToString().Substring(4).Replace("S", string.Empty));
var iLast = Convert.ToInt32(row["last_lot_id"].ToString().Substring(4).Replace("S", string.Empty));
if (iLast < iFirst)
{
var i = iLast;
iLast = iFirst;
iFirst = i;
}
var iCurrent = Convert.ToInt32(lotId.Substring(4).Replace("S", string.Empty));
xlSheet.Range["M16"].Value = string.Format("{0} LOT 共 {1} LOT", iCurrent - iFirst + 1, iLast - iFirst + 1);
//本卡开料pnl数
xlRange = xlSheet.Range["M17"];
xlRange.Font.Bold = true;
xlRange.Font.Size = 18;
xlRange.Value = string.Format("{0} pnl", row["pnl_qty"]);
//当前路径
string strPath = Application.ExecutablePath;
int j = strPath.LastIndexOf(@"\");
strPath = strPath.Remove(j + 1);
//待打印成的图片文件的名称
string imgName = string.Format("{0}dwg2jpg.jpg", strPath);
//将单体图读取到lot卡中
switch (cboPrintSettings.Text)
{
case "不打印只保存单元图":
case "不打印保存批量卡和cov卡以及单元图":
case "只打印单元图":
case "打印批量卡和cov卡以及单元图":
//显示状态
dgvRow.Cells["print_msg"].Value = "正在打开单体图...";
//各列列宽
dgvList.AutoResizeColumns();
//显示
Application.DoEvents();
//删除之前的文件
if (File.Exists(imgName))
{
File.Delete(imgName);
}
//显示状态
dgvRow.Cells["print_msg"].Value = "正在生成单体图图片...";
//各列列宽
dgvList.AutoResizeColumns();
//显示
Application.DoEvents();
//执行打印单体图生成图片文件
string strOutPut = ydDwg2Jpg.PrintDanTiTuImage(cadApp, danTiTuFullPath, imgName, cboDwgLayout.Text, cboDwgWay.SelectedIndex);
//for (int iii = 0; iii < 12; iii++)
//{
// ydDwg2Jpg.PrintDanTiTuImage(cadApp, danTiTuFullPath, imgName + iii + ".jpg", iii);
//}
//检测文件是否存在
if (strOutPut.Length > 0 || !File.Exists(imgName))
{
//返回错误
dgvRow.Cells["print_msg"].Value = strOutPut;
return;
}
else
{
//显示状态
dgvRow.Cells["print_msg"].Value = "正在插入单体图图片到批量卡...";
//各列列宽
dgvList.AutoResizeColumns();
//显示
Application.DoEvents();
//设置为活动
((Excel._Workbook)xlBook).Activate();
((Excel._Worksheet)xlSheet).Select();
//在excel中插入该图片并设置分页
string strError = string.Empty;
if (!InsertDanTiTuImageToExcelSheet(xlSheet, imgName, out strError))
{
//返回错误
dgvRow.Cells["print_msg"].Value = "插入单体图图片到批量卡失败！";
return;
}
}
//退出
break;
}
//待打印成的文件名称
string tmpFileName = string.Format("{0}lotcard.xls", strPath);
//删除之前的文件
if (File.Exists(tmpFileName))
{
File.Delete(tmpFileName);
}
//打印设置
var xlPageSetup = xlSheet.PageSetup;
xlPageSetup.FitToPagesWide = 1;
xlPageSetup.FitToPagesTall = 1;
xlPageSetup.RightFooter = @"&""宋体,倾斜""&10打印时间:&D &T";
xlPageSetup.FooterMargin = xlApp.InchesToPoints(0.18d);
//保存该批量卡
xlBook.SaveAs(tmpFileName);
//不打印只保存批量卡和cov卡
//不打印只保存单元图
//不打印保存批量卡和cov卡以及单元图
//只打印批量卡和cov卡
//只打印单元图
//打印批量卡和cov卡以及单元图
//打印
switch (cboPrintSettings.Text.Trim())
{
case "打印批量卡和cov卡以及单元图":
//显示状态
dgvRow.Cells["print_msg"].Value = "正在打印批量卡和cov卡以及单元图...";
//各列列宽
dgvList.AutoResizeColumns();
//显示
Application.DoEvents();
//打印LOT卡和单体图
xlSheet.PrintOut(From: 1, To: 2, Copies: 1, Collate: true);
break;
case "只打印批量卡":
//显示状态
dgvRow.Cells["print_msg"].Value = "正在打印批量卡...";
//各列列宽
dgvList.AutoResizeColumns();
//显示
Application.DoEvents();
//打印LOT卡
xlSheet.PrintOut(From: 1, To: 1, Copies: 1, Collate: true);
break;
case "只打印批量卡和cov卡":
//显示状态
dgvRow.Cells["print_msg"].Value = "正在打印批量卡和cov卡...";
//各列列宽
dgvList.AutoResizeColumns();
//显示
Application.DoEvents();
//打印LOT卡
xlSheet.PrintOut(From: 1, To: 1, Copies: 1, Collate: true);
break;
case "只打印单元图":
//显示状态
dgvRow.Cells["print_msg"].Value = "正在打印单元图...";
//各列列宽
dgvList.AutoResizeColumns();
//显示
Application.DoEvents();
//打印单体图
xlSheet.PrintOut(From: 2, To: 2, Copies: 1, Collate: true);
break;
}
//打印辅料卡
switch (cboPrintSettings.Text.Trim())
{
case "只打印批量卡和cov卡":
case "打印批量卡和cov卡以及单元图":
//之前已经累加存入数量
int iTotalPnlQty = 0;
//检测是否已经打印过辅料卡
if (!_dicProductNums.ContainsKey(productNum))
{
//添加到已经打印过的生产编号清单中
_dicProductNums.Add(productNum, Convert.ToInt32(row["pnl_qty"]));
}
else
{
//获取数量
_dicProductNums.TryGetValue(productNum, out iTotalPnlQty);
//检测数量
if (iTotalPnlQty >= iMaxPnlQty)
{
//总数量置0
iTotalPnlQty = 0;
}
//累加已经打印过的生产编号的数量
_dicProductNums[productNum] = iTotalPnlQty + Convert.ToInt32(row["pnl_qty"]);
}
//总数量为0则需要打印辅料卡
if (iTotalPnlQty == 0 && xlBook.Worksheets.Count > 1)
{
//取第二张表
var xlSheet2 = (Excel.Worksheet)xlBook.Worksheets[2];
//显示状态
dgvRow.Cells["print_msg"].Value = "正在打印cov卡...";
//各列列宽
dgvList.AutoResizeColumns();
//显示
Application.DoEvents();
//设置为最多两页高
xlSheet2.PageSetup.FitToPagesWide = 1;
xlSheet2.PageSetup.FitToPagesTall = 2;
//打印辅料卡
try
{
xlSheet2.PrintOut(From: 1, To: 1, Copies: 1, Collate: true);
xlSheet2.PrintOut(From: 2, To: 2, Copies: 1, Collate: true);
}
catch { }
//注销excel相关对象
xlSheet2 = null;
}
//退出
break;
}
//关闭工作表
xlBook.Close(SaveChanges: false);
//删除生成的图片文件
if (File.Exists(imgName))
{
File.Delete(imgName);
}
//返回成功
//不打印只保存批量卡
//不打印只保存批量卡和cov卡
//不打印只保存单元图
//不打印保存批量卡和cov卡以及单元图
//只打印批量卡
//只打印批量卡和cov卡
//只打印单元图
//打印批量卡和cov卡以及单元图
//打印
switch (cboPrintSettings.Text.Trim())
{
case "不打印只保存批量卡":
case "不打印只保存批量卡和cov卡":
case "不打印只保存单元图":
case "不打印保存批量卡和cov卡以及单元图":
dgvRow.Cells["print_msg"].Value = "保存成功!";
//退出
break;
default:
dgvRow.Cells["print_msg"].Value = "打印成功!";
//退出
break;
}
//注销excel相关对象
xlSheet = null;
}
public static void SaveAsWB(Excel.Workbook xlWb, string filePath)
{
xlWb.SaveAs(filePath, System.Type.Missing, System.Type.Missing, System.Type.Missing
, System.Type.Missing, System.Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, System.Type.Missing
, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
}
/// <summary>
/// 另存为文件
/// </summary>
/// <param name="CurBook">Workbook</param>
/// <param name="strFilePath">文件路径</param>
public void SaveAs(Excel._Workbook CurBook, string strFilePath)
{
CurBook.SaveAs(strFilePath, mValue, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue);
}
/// <summary>
/// Workbookを別名保存
/// </summary>
/// <param name="book">Workbook</param>
/// <param name="oPath">保存先のファイルパス</param>
public void SaveAs(Excel.Workbook book, string oPath)
{
foreach (char c in Path.GetInvalidPathChars()) {
if (oPath.Contains(c))
throw new FormatException(
this.GetType().FullName + "." +
MethodBase.GetCurrentMethod().Name);
}
Excel.XlSaveAsAccessMode oMod = Excel.XlSaveAsAccessMode.xlNoChange;
object oFmt = Excel.XlFileFormat.xlOpenXMLWorkbook;//xlsx
book.SaveAs(
Filename: oPath,
FileFormat: oFmt,
AccessMode: oMod);
}
public static string SaveExportCustom(Excel.Workbook Book, string report_name, bool isXlsFormat = false)
{
object Missing = System.Reflection.Missing.Value;
string str = "";
try
{
report_name = report_name.Remove(report_name.LastIndexOf('.'));
SaveFileDialog sDialog = new SaveFileDialog();
str = string.Format(@"{0}_{1}_{2}", report_name, DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss"), DateTime.Now.Ticks);
sDialog.Filter = isXlsFormat ? "Microsoft Excel (*.xls)|*.xls" : "Microsoft Excel 2007 (*.xlsx)|*.xlsx";
sDialog.Title = LanguageEngine.GetMessageCaption("000007", ConfigEngine.Language);
sDialog.InitialDirectory = Temp;
sDialog.FileName = str;
if (sDialog.ShowDialog() == DialogResult.OK)
{
str = sDialog.FileName;
Book.SaveAs(str, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Missing, Missing, Missing, Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing, Missing, Missing, Missing, Missing);
CommonEngine.ShowMessage(ConfigEngine.Language.Equals("vi") ? "Tải tập tin mẫu thành công!" : "Download template success!", MessageType.Success);
}
}
catch (Exception ex)
{
logger.Error(ex);
}
return str;
}
public static void SaveTempWorkbook(Excel.Workbook tempWorkbook)
{
if (tempWorkbook != null)
{
//string folder = System.Reflection.Assembly.GetExecutingAssembly().CodeBase.Remove(System.Reflection.Assembly.GetExecutingAssembly().CodeBase.LastIndexOf("/"));
//folder = folder.Replace(@"file:///", "");
//if (!Directory.Exists(folder + "/temp"))
// Directory.CreateDirectory(folder + "/temp");
//tempWorkbook.SaveAs(folder + "/temp/book1.xls");
tempWorkbook.SaveAs("book1.xls");
string fullname = tempWorkbook.FullName;
tempWorkbook.Close();
if (File.Exists(fullname))
File.Delete(fullname);
ExcelUtilies.ReleaseComObject(tempWorkbook);
}
}
private void SaveExcel(string file, MSExcel.Application excel, MSExcel.Workbook wbook)
{
try
{
File.Delete(file);
}
catch //(Exception ex)
{
wbook.Close(false);
excel.Quit();
return;
}
try
{
wbook.SaveAs(file, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, MSExcel.XlSaveAsAccessMode.xlExclusive);
}
catch//(Exception ex)
{
}
wbook.Close(false);
excel.Quit();
}
