public static bool writeSelfCheckData(carinfor.lljSelfDetectInf cgjdata)
{
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks appworkbook = (Microsoft.Office.Interop.Excel.Workbooks)appexcel.Workbooks;
if (File.Exists(Application.StartupPath + "\\bk\\自检日志.xls"))
{
string filename = Application.StartupPath + "\\bk\\检测信息" + DateTime.Now.ToString("yyMMdd") + ".xls";
if (!File.Exists(filename))
{
File.Copy(Application.StartupPath + "\\bk\\自检日志.xls", filename, true);
}
appworkbook.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Workbook objbook = appworkbook.get_Item(1);
Microsoft.Office.Interop.Excel.Sheets msheets = (Microsoft.Office.Interop.Excel.Sheets)objbook.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet objsheet = (Microsoft.Office.Interop.Excel._Worksheet)msheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range m_objRange = (Microsoft.Office.Interop.Excel.Range)objsheet.get_Range("A1", "F30");
objsheet.Cells[27, 3] = "√正常";
objsheet.Cells[28, 3] = "√正常";
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
m_objRange = null;
objbook.Save();
appworkbook.Close();
//调用方法关闭excel进程
appexcel.Visible = true;
return(true);
}
else
{
return(false);
}
}
private void btneExcelReport_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelApp.Sheets;
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
DataTable dtAccount = new DataTable();
foreach (DataGridViewColumn col in dgvCompany.Columns)
{
dtAccount.Columns.Add(col.HeaderText);
}
foreach (DataGridViewRow row in dgvCompany.Rows)
{
DataRow dRow = dtAccount.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
dRow[cell.ColumnIndex] = cell.Value;
}
dtAccount.Rows.Add(dRow);
}
dtAccount.Columns.Remove("dealerId");
dtAccount.Columns.Remove("जन्मदिनांक");
dtAccount.Columns.Remove("लिंग");
dtAccount.Columns.Remove("व्यापाऱ्याचा पत्ता");
dtAccount.Columns.Remove("ई-मेल");
dtAccount.TableName = "Dealer Report";
xlWorksheet.Name = dtAccount.TableName;
for (int j = 1; j < dtAccount.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = dtAccount.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < dtAccount.Rows.Count; k++)
{
for (int l = 0; l < dtAccount.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
dtAccount.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void create_file()
{
System.Diagnostics.Process p = new System.Diagnostics.Process();
bool createdFile = false;
try
{
excelApp1 = new Microsoft.Office.Interop.Excel.Application();
string filename = foldername + "First Article Sheet Template.xlsx";
// string filename = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\MIM_Menlo Demand TrackerTemplate.xlsx";
//filename = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\Demand Tracker1_"+DateTime.Now.Month.ToString("00") + "_" + DateTime.Now.Day.ToString("00") + "_" + DateTime.Now.Year.ToString("00") + DateTime.Now.Hour.ToString("00") + DateTime.Now.Minute.ToString("00") + DateTime.Now.Second.ToString("00") +".xlsx";
string workbookPath = filename.Replace("file:", "\\");
filename1 = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\First Article Sheet Template_" + DateTime.Now.Month.ToString("00") + "_" + DateTime.Now.Day.ToString("00") + "_" + DateTime.Now.Year.ToString("00") + DateTime.Now.Hour.ToString("00") + DateTime.Now.Minute.ToString("00") + DateTime.Now.Second.ToString("00") + ".xlsx";
excelWorkbooks = excelApp1.Workbooks;
excelWorkbook = excelWorkbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
// excelWorkbook = excelApp1.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
excelWorkbook.SaveAs(filename1, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
excelSheets = excelWorkbook.Worksheets;
sheet = excelSheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
String strQuery = " SELECT GDOC_FirstArticleSchedule.RefreshDate,GDOC_FirstArticleSchedule.ProdSite, GDOC_FirstArticleSchedule.SFSRouting, GDOC_FirstArticleSchedule.PartMap, GDOC_FirstArticleSchedule.WorkInstr, GDOC_FirstArticleSchedule.QADoc, GDOC_FirstArticleSchedule.Pack, GDOC_FirstArticleSchedule.Tooling, GDOC_FirstArticleSchedule.OSPPO, GDOC_FirstArticleSchedule.Barcode, GDOC_FirstArticleSchedule.Test, GDOC_FirstArticleSchedule.WorkOrdNum, GDOC_FirstArticleSchedule.AsmGPN, GDOC_FirstArticleSchedule.description, GDOC_FirstArticleSchedule.Qty, GDOC_FirstArticleSchedule.SchedAsmDate, GDOC_FirstArticleSchedule.SchedWeek, GDOC_FirstArticleSchedule.DemandSource, GDOC_FirstArticleSchedule.CommCode, GDOC_FirstArticleSchedule.RefGPN, GDOC_FirstArticleSchedule.PlanGroup, GDOC_FirstArticleSchedule.FormFactor, GDOC_FirstArticleSchedule.Platform, GDOC_FirstArticleSchedule.BP_BuildID, GDOC_FirstArticleSchedule.BP_TLAGPN, GDOC_FirstArticleSchedule.BP_POPDate, GDOC_FirstArticleSchedule.BP_FinalDestination, GDOC_FirstArticleSchedule.BP_Cluster, GDOC_FirstArticleSchedule.BP_ProjCode, GDOC_FirstArticleSchedule.BP_Owner FROM MIMDIST.dbo.GDOC_FirstArticleSchedule GDOC_FirstArticleSchedule ORDER BY GDOC_FirstArticleSchedule.SchedAsmDate ";
DataSet dsFirstArticle = getdataSet(strQuery);
int col = 0;
for (int i = 0; i < dsFirstArticle.Tables[0].Rows.Count; i++)
{
//copyPasteRangeExcel(("C" + j).ToString(), ("AL" + j).ToString());
for (int j = 0; j < dsFirstArticle.Tables[0].Columns.Count; j++)
{
sheet.Cells[i + 3, j + 1] = dsFirstArticle.Tables[0].Rows[i][j].ToString();
}
}
excelWorkbook.Save();
// excelWorkbook.Close(true, false, Type.Missing);
createdFile = true;
p.StartInfo.FileName = filename1;
//p.StartInfo.WindowStyle = ProcessWindowStyle.Minimized
p.Start();
// System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1);
}
catch (Exception e)
{
Console.Write(e.Message.ToString());
}
finally
{
//Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal
/* excelWorkbook.Close(true, false, Type.Missing);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheets);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbooks);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1);*/
}
}
public void exportToExcel(DataTable dt, string filename)
{
try
{
Microsoft.Office.Interop.Excel.Application excelApp1 = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp1.Workbooks.Add();
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet sheet = excelSheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
int col = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
//copyPasteRangeExcel(("C" + j).ToString(), ("AL" + j).ToString());
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
excelWorkbook.Save();
excelWorkbook.SaveAs(filename);
}
catch (Exception e)
{
}
}
private void exportToExcel1(string filename)
{
System.Diagnostics.Process p = new System.Diagnostics.Process();
Microsoft.Office.Interop.Excel.Application excelApp1 = new Microsoft.Office.Interop.Excel.Application();
try
{
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp1.Workbooks.Add();
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet sheet = excelSheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
string cnt = "data source=mvint;initial catalog=proddist;user id=sa;password=mimi~100;pooling=true;max pool size=100;min pool size=1;";
Microsoft.Office.Interop.Excel.QueryTable oQryTable = sheet.QueryTables.Add("OLEDB;Provider=sqloledb;" + cnt, sheet.Range["A1"], sql);
oQryTable.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertEntireRows; // 2; //' xlInsertEntireRows = 2
oQryTable.Refresh(false);
excelApp1.DisplayAlerts = false;
excelWorkbook.Save();
excelWorkbook.SaveAs(filename);
p.StartInfo.FileName = filename;
p.Start();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1);
}
catch (Exception e)
{
string Message = e.Message;
MessageBox.Show(Message);
if (excelApp1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1);
}
}
finally
{
GC.Collect();
p.Close();
}
}
private void buttonExcel_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
BringToFront();
string workbookPath = Application.StartupPath + "\\templates/zhurnal.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Бланк";
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
int i = 1;
int i2 = 4;
foreach (ListViewItem lvi in listViewZhurnal.Items)
{
i = 1;
foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
{
excelWorksheet.Cells[i2, i] = lvs.Text;
i++;
}
i2++;
}
}
public ExcelWriter(string _fileName)
{
fileName = _fileName;
if(File.Exists(FilePath))
throw new ApplicationException("File already exists: " + FilePath);
File.Create(FilePath);
app = new Microsoft.Office.Interop.Excel.Application();
Console.Error.WriteLine("Connected to Excel");
wbs = app.Workbooks;
wb = wbs.Add(1);
wb.Activate();
wss = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1);
Console.Error.WriteLine("Excel Worksheet Initialized");
}
public void getallSheet(string FileName)
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
try
{
string workbookPath = FileName.Replace("file:\\", "");
excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
sheetnum = 0;
excelSheets = excelWorkbook.Worksheets;
foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in excelWorkbook.Worksheets)
{
if (worksheet.Name.Contains("Rack Delivery Plan"))
{
ListItem1 lst = new ListItem1(worksheet.Name, worksheet.Name);
lstWorksheet.Items.Add(lst);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
}
finally
{
//Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal
excelWorkbook.Close(true, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
}
//get a list of Row objects for a sheet
private List <Row> GetRows(Sheet sheet)
{
List <Row> rows = new List <Row>();
Book book = _books.Where(b => b.Id == sheet.BookId).FirstOrDefault();
Microsoft.Office.Interop.Excel._Workbook xlBook = _xlBooks.Open(book.Path);
Microsoft.Office.Interop.Excel.Sheets xlSheets = xlBook.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet xlSheet = (Microsoft.Office.Interop.Excel._Worksheet)xlSheets[sheet.Index];
int id = _rows.Count();
int row = 2;
while (xlSheet.get_Range(CellName(row, 1)).Value2 != null)
{
Row r = new Row()
{
Id = id++,
SheetId = sheet.Id,
Index = row,
Data = GetRowData(GetRowVals(xlSheet, 1), GetRowVals(xlSheet, row++))
};
rows.Add(r);
}
_xlBooks.Close();
return(rows);
}
private void button1_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
if (openFileDialog1.FileName.Length > 0)
{
string sFileName = openFileDialog1.FileName;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
Microsoft.Office.Interop.Excel.Workbooks wbks = app.Workbooks;
Microsoft.Office.Interop.Excel._Workbook _wbk = wbks.Open(openFileDialog1.FileName);
Microsoft.Office.Interop.Excel.Sheets shs = _wbk.Sheets;
Microsoft.Office.Interop.Excel._Worksheet _wsh = (Microsoft.Office.Interop.Excel._Worksheet)shs[1];
foreach (DataRowView row in m_viewTagValue)
{
if (row["DataSourcesNo"].ToString() == "1")
{
_wsh.Cells[int.Parse(row["id"].ToString()), 6] = row["TagValue"].ToString();
}
}
string sExt = sFileName.Substring(sFileName.LastIndexOf('.'));
sFileName = sFileName.Substring(0, sFileName.LastIndexOf('.'));
sFileName = sFileName + "_2" + sExt;
_wbk.SaveAs(sFileName);
MessageBox.Show("完成！");
}
}
private void browse_Click(object sender, EventArgs e)
{
using (OpenFileDialog openFileDialog = new OpenFileDialog()
{
Filter = "Excel Workbook|*.xlsx|Excel 97-2003 Workbook|*.xls"
})
{
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
this._open.Enabled = true;
fileName.Text = openFileDialog.FileName;
theWorkbook = ExcelObj.Workbooks.Open(fileName.Text);
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
cmbSheet.Items.Clear();
var numSheet = sheets.Count;
for (int i = 1; i <= numSheet; i++)
{
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
cmbSheet.Items.Add(worksheet.Name);
cmbSheet.SelectedIndex = 0;
}
}
}
}
private void simpleButton5_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application oExcel_12 = null;
Microsoft.Office.Interop.Excel.Workbook oBook = null;
Microsoft.Office.Interop.Excel.Sheets oSheetsColl = null;
Microsoft.Office.Interop.Excel.Worksheet oSheet = null;
Microsoft.Office.Interop.Excel.Range oRange = null;
Object oMissing = System.Reflection.Missing.Value;
oExcel_12 = new Microsoft.Office.Interop.Excel.Application();
oExcel_12.Visible = true;
oExcel_12.UserControl = true;
oBook = oExcel_12.Workbooks.Add(oMissing);
oSheetsColl = oExcel_12.Worksheets;
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheetsColl.get_Item(1);
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j + 1];
oRange.Value2 = dataGridView1.Columns[j].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i + 2, j + 1];
oRange.Value2 = dataGridView1[j, i].Value;
}
}
oBook = null;
oExcel_12 = null;
GC.Collect();
}
private void btnExcelConvert_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelApp.Sheets;
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
// System.Data.DataTable dat = (System.Data.DataTable)(dgvSaleBook.DataSource);
DataTable dat = new DataTable();
foreach (DataGridViewColumn col in dgvSaleBook.Columns)
{
dat.Columns.Add(col.HeaderText);
}
foreach (DataGridViewRow row in dgvSaleBook.Rows)
{
DataRow dRow = dat.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
dRow[cell.ColumnIndex] = cell.Value;
}
dat.Rows.Add(dRow);
}
dat.TableName = "Sale Book 2";
xlWorksheet.Name = dat.TableName;
for (int j = 1; j < dat.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = dat.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < dat.Rows.Count; k++)
{
for (int l = 0; l < dat.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
dat.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private Microsoft.Office.Interop.Excel.Worksheet GetWorksheet(string WorksheetName)
{
Microsoft.Office.Interop.Excel.Sheets sheets = xlWB.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[WorksheetName];
ReleaseObject(sheets);
return(sheet);
}
public static bool writeDatatableToElt(DataTable dt)
{
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks appworkbook = (Microsoft.Office.Interop.Excel.Workbooks)appexcel.Workbooks;
if (File.Exists(Application.StartupPath + "\\bk\\检测信息.xls"))
{
File.Copy(Application.StartupPath + "\\bk\\检测信息.xls", Application.StartupPath + "\\检测信息.xls", true);
appworkbook.Open(Application.StartupPath + "\\检测信息.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Workbook objbook = appworkbook.get_Item(1);
Microsoft.Office.Interop.Excel.Sheets msheets = (Microsoft.Office.Interop.Excel.Sheets)objbook.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet objsheet = (Microsoft.Office.Interop.Excel._Worksheet)msheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range m_objRange = (Microsoft.Office.Interop.Excel.Range)objsheet.get_Range("A1", "D5");
for (int i = 0; i < dt.Rows.Count; i++)
{
objsheet.Cells[i + 4, 1] = (i + 1).ToString();
objsheet.Cells[i + 4, 2] = "";
objsheet.Cells[i + 4, 3] = mainPanel.stationinfmodel.STATIONNAME;
objsheet.Cells[i + 4, 4] = dt.Rows[i]["LINEID"].ToString();
objsheet.Cells[i + 4, 5] = DateTime.Parse(dt.Rows[i]["JCSJ"].ToString()).ToString("yyyy-MM-dd HH:mm");
objsheet.Cells[i + 4, 6] = dt.Rows[i]["CLHP"].ToString();
objsheet.Cells[i + 4, 7] = dt.Rows[i]["PP"].ToString() + dt.Rows[i]["XH"].ToString();
objsheet.Cells[i + 4, 8] = dt.Rows[i]["SYXZ"].ToString();
objsheet.Cells[i + 4, 9] = dt.Rows[i]["CLLX"].ToString();
objsheet.Cells[i + 4, 10] = "'" + dt.Rows[i]["CLSBM"].ToString();
objsheet.Cells[i + 4, 11] = DateTime.Parse(dt.Rows[i]["ZCRQ"].ToString()).ToString("yyyy/MM/dd");
objsheet.Cells[i + 4, 12] = dt.Rows[i]["RLZL"].ToString();
objsheet.Cells[i + 4, 13] = "'" + dt.Rows[i]["LSH"].ToString();
switch (dt.Rows[i]["JCFF"].ToString())
{
case "ASM": objsheet.Cells[i + 4, 14] = "稳态工况法"; break;
case "SDS": objsheet.Cells[i + 4, 14] = "双怠速法"; break;
case "JZJS": objsheet.Cells[i + 4, 14] = "加载减速法"; break;
case "ZYJS": objsheet.Cells[i + 4, 14] = "自由加速法"; break;
case "VMAS": objsheet.Cells[i + 4, 14] = "简易瞬态法"; break;
case "LZ": objsheet.Cells[i + 4, 14] = "滤纸法"; break;
default: break;
}
}
//保存工作表
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
m_objRange = null;
//调用方法关闭excel进程
appexcel.Visible = true;
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// activate the excel application
/// </summary>
///
protected virtual void ActivateExcel()
{
_excelApplication = new Microsoft.Office.Interop.Excel.Application();
_workBooks = (Microsoft.Office.Interop.Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Microsoft.Office.Interop.Excel._Workbook)(_workBooks.Add(_value));
_excelSheets = (Microsoft.Office.Interop.Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Microsoft.Office.Interop.Excel._Worksheet)(_excelSheets.get_Item(1));
}
/// <summary>
/// Create Excel application parameters instances
/// </summary>
private void CreateExcelRef()
{
_excelApp = new Microsoft.Office.Interop.Excel.Application();
_books = (Microsoft.Office.Interop.Excel.Workbooks)_excelApp.Workbooks;
_book = (Microsoft.Office.Interop.Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Microsoft.Office.Interop.Excel.Sheets)_book.Worksheets;
_sheet = (Microsoft.Office.Interop.Excel._Worksheet)(_sheets.get_Item(1));
}
private void btnExport_Click(object sender, EventArgs e)
{
try
{
System.Data.DataTable ds = new System.Data.DataTable();
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelApp.Sheets;
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
foreach (DataGridViewColumn col in dgvStockRegister.Columns)
{
ds.Columns.Add(col.HeaderText);
}
foreach (DataGridViewRow row in dgvStockRegister.Rows)
{
DataRow dRow = ds.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
dRow[cell.ColumnIndex] = cell.Value;
}
ds.Rows.Add(dRow);
}
ds.Columns.Remove("प्रकार");
ds.Columns.Remove("कंपनी");
ds.Columns.Remove("बॅच नंबर उत्पदन अंतिम मुदत दिनांक");
ds.TableName = "Item Ledger Report";
xlWorksheet.Name = ds.TableName;
for (int j = 1; j < ds.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = ds.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < ds.Rows.Count; k++)
{
for (int l = 0; l < ds.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
ds.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
}
catch (Exception ac)
{
MessageBox.Show(ac.Message);
}
}
public override void CreateWorksheet(string worksheetname)
{
Microsoft.Office.Interop.Excel.Sheets sheets = xlWB.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet newWS = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
newWS.Name = worksheetname;
ReleaseObject(sheets);
ReleaseObject(newWS);
}
public bool TableToExcel_AE(String strfilename)
{
try
{
dataGridView1.DataSource = null;
dataGridView1.DataSource = Global.db.NangSuatDeSo_AE(firstDateTime, lastDateTime);
Microsoft.Office.Interop.Excel.Application App = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook book = App.Workbooks.Open(strfilename, 0, true, 5, "", "", false,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets _sheet = (Microsoft.Office.Interop.Excel.Sheets)book.Sheets;
Microsoft.Office.Interop.Excel.Worksheet wrksheet =
(Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
int h = 1;
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
wrksheet.Cells[h + 2, 1] = h;
wrksheet.Cells[h + 2, 2] = dr.Cells[0].Value != null ? dr.Cells[0].Value.ToString() : "";
wrksheet.Cells[h + 2, 3] = dr.Cells[1].Value != null ? dr.Cells[1].Value.ToString() : "";
wrksheet.Cells[h + 2, 4] = dr.Cells[2].Value != null ? dr.Cells[2].Value.ToString() : "";
wrksheet.Cells[h + 2, 5] = dr.Cells[3].Value != null ? dr.Cells[3].Value.ToString() : "";
wrksheet.Cells[h + 2, 6] = dr.Cells[4].Value != null ? dr.Cells[4].Value.ToString() : "";
wrksheet.Cells[h + 2, 7] = dr.Cells[5].Value != null ? dr.Cells[5].Value.ToString() : "";
wrksheet.Cells[h + 2, 8] = dr.Cells[6].Value != null ? dr.Cells[6].Value.ToString() : "";
h++;
}
string savePath = "";
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Title = "Save Excel Files";
saveFileDialog1.Filter = "Excel files (*.xls)|*.xls";
saveFileDialog1.FileName = "NangSuat_AE_" + dtp_FirstDay.Value.Day + "-" + dtp_EndDay.Value.Day;
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
book.SaveCopyAs(saveFileDialog1.FileName);
book.Saved = true;
savePath = Path.GetDirectoryName(saveFileDialog1.FileName);
App.Quit();
}
else
{
MessageBox.Show("Lỗi khi xuất excel!");
return(false);
}
Process.Start(savePath);
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
public void ReadExistingExcel(DataTable dt, string templatePath, string Exportpath, string late1, string late2, string late3)
{
try
{
//string path = @"C:\Users\zkritsawan.p\Desktop\Project ใช้จริง\testreportMo\TestReport\report\encotemplate.xls";
string path = templatePath;
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbooks workbooks = oXL.Workbooks;
mWorkBook = workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = oXL.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
int colCount = range.Columns.Count;
//int rowCount = range.Rows.Count;
int rowCount = 5;
mWSheet1.Cells[rowCount, 7] = late1;
mWSheet1.Cells[rowCount, 8] = late2;
mWSheet1.Cells[rowCount, 9] = late3;
for (int r = 0; r < dt.Rows.Count; r++)
{
mWSheet1.Cells[rowCount + r + 1, 2] = dt.Rows[r]["NAME"].ToString();
mWSheet1.Cells[rowCount + r + 1, 7] = dt.Rows[r]["Case1"].ToString();
mWSheet1.Cells[rowCount + r + 1, 8] = dt.Rows[r]["Case2"].ToString();
mWSheet1.Cells[rowCount + r + 1, 9] = dt.Rows[r]["Case3"].ToString();
mWSheet1.Cells[rowCount + r + 1, 6] = string.Format("=E{0}/30", rowCount + r + 1);
mWSheet1.Cells[rowCount + r + 1, 11] = string.Format("=E{0}-J{0}", rowCount + r + 1);
mWSheet1.Cells[rowCount + r + 1, 10] = string.Format("=((G{0}*F{0})+(H{0}*F{0})+(I{0}*F{0}))", rowCount + r + 1);
}
// string pathSave = @"C:\Users\zkritsawan.p\Desktop\Project ใช้จริง\testreportMo\TestReport\report\EncoReport(1).xls";
string pathSave = Exportpath;
mWorkBook.SaveAs(pathSave, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
catch (Exception e)
{
}
}
private void ExportSSummarizeList(object[] parameter)
{
string exepath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
string file = exepath + "MyData\\导出表.xlsx";
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks wbks = app.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wbk = wbks.Add(file);
Microsoft.Office.Interop.Excel.Sheets sheets = wbk.Sheets;
Microsoft.Office.Interop.Excel.Worksheet wsh = sheets["Sheet1"];
int i = 0;
if (KPISSummarizelist.Count > 0)
{
foreach (KPIIndicators item in KPISSummarizelist)
{
wsh.Cells[i + 2, 1].value = item.Name;
wsh.Cells[i + 2, 2].value = item.Period;
for (int j = 0; j < 10; j++)
{
wsh.Cells[i + 2, j * 2 + 3].value = item.IndicatorNames[j];
wsh.Cells[i + 2, j * 2 + 4].value = item.Indicators[j];
}
wsh.Cells[i + 2, 23].value = item.Score;
wsh.Cells[i + 2, 24].value = item.Grade;
i++;
}
if (!Directory.Exists(FilePathS + "\\汇总"))
{
Directory.CreateDirectory(FilePathS + "\\汇总");
}
string fileSummarize = FilePathS + "\\汇总\\干部汇总表.xlsx";
if (File.Exists(fileSummarize))
{
try
{
File.Delete(fileSummarize);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
throw;
}
}
wsh.SaveAs(fileSummarize);
wbks.Close();
MessageBox.Show("生成完毕！", "", MessageBoxButton.OK, MessageBoxImage.Information);
Process.Start("explorer.exe ", FilePathS + "\\汇总");
}
else
{
MessageBox.Show("没有数据", "出错了", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void ReadPlanFile(object[] parameter)
{
if (Directory.Exists(FilePath))
{
FileInfo fileInfo = new FileInfo(FilePath);
DirectoryInfo folder = new DirectoryInfo(FilePath);
KPISummarizelist = new ObservableCollection <KPIIndicators>();
ErrorInfos = new ObservableCollection <ErrorInfo>();
//try
//{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
foreach (FileInfo file in folder.GetFiles("*.xls"))
{
if (!file.Name.Contains("~$"))
{
Microsoft.Office.Interop.Excel.Workbooks wbks = app.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wbk = wbks.Add(file.FullName);
Microsoft.Office.Interop.Excel.Sheets sheets = wbk.Sheets;
for (int i = 1; i <= sheets.Count; i++)
{
Microsoft.Office.Interop.Excel.Worksheet wsh = sheets.Item[i];
string sheetname = wsh.Name;
if (wsh.Cells[17, 2].value == "被考核者姓名" && wsh.Cells[16, 2].value == "绩效考核指标总得分" && wsh.Cells[5, 12].value == "考核得分")
{
KPISummarizelist.Add(GetKPIIndicators(file, wsh));
}
else
{
ErrorInfo error = new ErrorInfo();
error.FileName = file.Name;
error.SheetName = sheetname;
error.Information = "未使用标准表格";
ErrorInfos.Add(error);
}
}
wbks.Close();
}
}
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.Message);
// throw;
//}
}
else
{
MessageBox.Show("当前选择的路径不存在", "出错了", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public bool create_file()
{
System.Diagnostics.Process p = new System.Diagnostics.Process();
bool createdFile = false;
try
{
excelApp1 = new Microsoft.Office.Interop.Excel.Application();
string filename = foldername + "BOLReportData.xlsx";
// string filename = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\MIM_Menlo Demand TrackerTemplate.xlsx";
//filename = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\Demand Tracker1_"+DateTime.Now.Month.ToString("00") + "_" + DateTime.Now.Day.ToString("00") + "_" + DateTime.Now.Year.ToString("00") + DateTime.Now.Hour.ToString("00") + DateTime.Now.Minute.ToString("00") + DateTime.Now.Second.ToString("00") +".xlsx";
string workbookPath = filename.Replace("file:", "\\");
filename1 = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\BOL Data _" + DateTime.Now.Month.ToString("00") + "_" + DateTime.Now.Day.ToString("00") + "_" + DateTime.Now.Year.ToString("00") + DateTime.Now.Hour.ToString("00") + DateTime.Now.Minute.ToString("00") + DateTime.Now.Second.ToString("00") + ".xlsx";
excelWorkbooks = excelApp1.Workbooks;
excelWorkbook = excelWorkbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
// excelWorkbook = excelApp1.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
excelWorkbook.SaveAs(filename1, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
excelSheets = excelWorkbook.Worksheets;
sheet = excelSheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
String strQuery = " SELECT [ShipDate] ,[BOL#] ,[TruckId] ,[Seal#] ,[PalletId] ,[Container#] ,[Order#] ,[GPN] ,[Qty] ,[OwnerCode] ,[ProjectCode] FROM [PRODDIST].[dbo].[XPO_BOL] where [ShipDate] >getdate()-2 order by [ShipDate] ";
DataSet dsFirstArticle = getdataSet(strQuery);
int col = 0;
for (int i = 0; i < dsFirstArticle.Tables[0].Rows.Count; i++)
{
//copyPasteRangeExcel(("C" + j).ToString(), ("AL" + j).ToString());
for (int j = 0; j < dsFirstArticle.Tables[0].Columns.Count; j++)
{
sheet.Cells[i + 2, j + 1] = dsFirstArticle.Tables[0].Rows[i][j].ToString();
}
}
excelWorkbook.Save();
excelWorkbook.Close(true, false, Type.Missing);
}
catch (Exception e)
{
Console.Write(e.Message.ToString());
}
finally
{
//Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal
/* excelWorkbook.Close(true, false, Type.Missing);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheets);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbooks);
* System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1);*/
}
return(true);
}
private void button1_Click(object sender, EventArgs e)
{
string path = @"D:\NYT_Lasttry\MyConfigScapy.xls";
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
mWSheet1.Cells[3, 2] = this.textBox1.Text;
mWSheet1.Cells[4, 2] = this.textBox2.Text;
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value);
mWorkBook.Close(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
this.mainForm.label1.Text = "IP = " + this.textBox1.Text;;
this.mainForm.label2.Text = "Payload = " + this.textBox2.Text;;
int ExitCode;
Process p = new Process();
p.StartInfo.UseShellExecute = true;
p.StartInfo.FileName = "D:\\NYT_Lasttry\\batch.bat";
p.Start();
p.WaitForExit();
ExitCode = p.ExitCode;
p.Close();
this.Close();
}
public void ExcelOut(string id)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Sheets sh = null;
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "箱码";
excel.Cells[1, 2] = "物流码";
excel.Cells[1, 3] = "积分码";;
string content = " 箱码 物流码 积分码\r\n";
BLL.BarCode bllBarCode = new BLL.BarCode();
System.Data.DataTable dt = bllBarCode.GetBarCodeCreateDetailByBarCodeCreateRecordID(id);
BLL.BarCodeCreateRecord bllBarCodeCreateRecord = new BLL.BarCodeCreateRecord();
int num = 2;
string FileName;
FileName = Guid.NewGuid().ToString();
object OsPath, FilePathxls, FilePathtxt; //表格文件的保存地址
OsPath = Server.MapPath("/DownLoad/"); //这里我定义了桌面的地址
FilePathxls = string.Format("{0}{1}", (string)OsPath, FileName + ".xls"); //为了自定义地址，加了一个地址拼接
FilePathtxt = string.Format("{0}{1}", (string)OsPath, FileName + ".txt"); //为了自定义地址，加了一个地址拼接
FileStream fs = File.Create(FilePathtxt.ToString());
foreach (DataRow dr in dt.Rows)
{
excel.Cells[num, 1] = dr["BoxCode"];
excel.Cells[num, 2] = dr["LogisticsCode"];
excel.Cells[num, 3] = dr["IntegralCode"];
content += dr["BoxCode"].ToString() + " " + dr["LogisticsCode"] + " " + dr["IntegralCode"] + "\r\n";
num++;
}
byte[] buffer = Encoding.UTF8.GetBytes(content);
fs.Write(buffer, 0, buffer.Length);
//bllBarCodeCreateRecord.Update(id, "//DownLoad//" + FileName);
bllBarCodeCreateRecord.Update(id, FileName);
excel.Visible = false;
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
excel.ActiveWorkbook.SaveAs(FilePathxls);
excel.Quit();
//Response.Write("/DownLoad/" + FileName + ".txt");
}
/// <summary>
/// Save the string To the excel File.
/// </summary>
/// <param name="ToFile">String Of TOLEDO Format</param>
/// <param name="SavedFlag">Boolean Flag that Shows this string Saved or not</param>
public void ReadExistingExcel(String ToFile, Boolean SavedFlag)
{
try
{
string path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\Resources\\iTOLEDO_Loggs.xls";
oXL = new Microsoft.Office.Interop.Excel.Application();
// oXL.Visible = true;
oXL.DisplayAlerts = false;
try
{
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
}
catch (Exception)
{
// mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
}
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
int colCount = range.Columns.Count;
int rowCount = range.Rows.Count;
///Add Columns To the Excel file.
for (int index = 1; index < 2; index++)
{
mWSheet1.Cells[rowCount + index, 1] = DateTime.Now.ToString("MMM dd, yyyy hh:mm:ss tt");
mWSheet1.Cells[rowCount + index, 2] = ToFile;
mWSheet1.Cells[rowCount + index, 3] = SavedFlag.ToString();
}
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
catch (Exception)
{ }
}
//取得sheet by name
public static Microsoft.Office.Interop.Excel.Worksheet GetSheetByName(Microsoft.Office.Interop.Excel.Application myExcel, string name)
{
Microsoft.Office.Interop.Excel.Worksheet sheet = null;
Microsoft.Office.Interop.Excel.Sheets sheets = myExcel.Sheets;
foreach (Microsoft.Office.Interop.Excel.Worksheet s in sheets)
{
if (s.Name == name)
{
sheet = s;
break;
}
}
return(sheet);
}
private void ExportDoneTable(object[] parameter)
{
string exepath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
string file = exepath + "MyData\\绩效汇总表-模板.xls";
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks wbks = app.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wbk = wbks.Add(file);
Microsoft.Office.Interop.Excel.Sheets sheets = wbk.Sheets;
Microsoft.Office.Interop.Excel.Worksheet wsh = sheets["sheet1"];
int i = 0;
foreach (KpiInfoViewModel kpi in SelectedKpiInfos)
{
wsh.Cells[i + 2, 1].value = kpi.KPIInfo.Period;
wsh.Cells[i + 2, 2].value = kpi.KPIInfo.Zone;
wsh.Cells[i + 2, 3].value = kpi.KPIInfo.Name;
wsh.Cells[i + 2, 4].value = kpi.KPIInfo.DonePoint_Target;
wsh.Cells[i + 2, 5].value = kpi.KPIInfo.FirstVirsionPoint_Target;
wsh.Cells[i + 2, 6].value = kpi.KPIInfo.PatentDegree_Target;
wsh.Cells[i + 2, 7].value = kpi.KPIInfo.InTimePortion_Target;
wsh.Cells[i + 2, 8].value = kpi.KPIInfo.Score_Cowork;
wsh.Cells[i + 2, 9].value = kpi.KPIInfo.Score_Passion;
wsh.Cells[i + 2, 10].value = kpi.KPIInfo.Score_Selfdrive;
wsh.Cells[i + 2, 11].value = kpi.KPIInfo.DonePoint;
wsh.Cells[i + 2, 12].value = kpi.KPIInfo.PatentDegree;
wsh.Cells[i + 2, 13].value = kpi.KPIInfo.InTimePortion;
wsh.Cells[i + 2, 14].value = kpi.KPIInfo.FirstVirsionPoint;
wsh.Cells[i + 2, 15].value = kpi.KPIInfo.Position;
wsh.Cells[i + 2, 16].value = kpi.KPIInfo.Examiner;
wsh.Cells[i + 2, 17].value = kpi.KPIInfo.Examiner_Positon;
wsh.Cells[i + 2, 18].value = kpi.KPIInfo.Score;
wsh.Cells[i + 2, 20].value = kpi.KPIInfo.Comment;
i++;
}
string fileSummarize = "C:\\WORK\\绩效考核\\绩效考核汇总表.xlsx";
if (File.Exists(fileSummarize))
{
File.Delete(fileSummarize);
}
wsh.SaveAs(fileSummarize);
wbks.Close();
MessageBox.Show("生成完毕！", "", MessageBoxButton.OKCancel, MessageBoxImage.Information);
}
//populate a list of Sheet objects for a workbook
private void LoadSheets(Book b, Microsoft.Office.Interop.Excel._Workbook xlBook)
{
Microsoft.Office.Interop.Excel.Sheets xlSheets = xlBook.Worksheets;
foreach (Microsoft.Office.Interop.Excel._Worksheet w in xlSheets)
{
Sheet s = new Sheet()
{
Id = _sheets.Count(),
BookId = b.Id,
Name = w.Name
};
LoadRows(s, w);
_sheets.Add(s);
}
}
public void create_file()
{
System.Diagnostics.Process p = new System.Diagnostics.Process();
try
{
String strQuery = " select Top 1 [SharedDocFolderName],TLA_PartNum,TLADesc FROM [MIMDIST].[dbo].[BaconBOM_for_SharedDoc] where [SharedDocFolderName] is not null SELECT [AssemblyType] ,[AssyGPN],[CommodityCode] ,[SupplyType],[CompGPN] ,[LeadTime] ,[BOMQty] ,[UnitCost],[ExtCost],[CompDesc] FROM [MIMDIST].[dbo].[BaconBOM_for_SharedDoc] order by sortorder,COMPGPN ";
DataSet ds = getDataSet(strQuery);
DataTable dt = dtPopulate.Clone();
dt = ds.Tables[1];// ultraGridBOM.DataSource as DataTable;
Collection = ds.Tables[0].Rows[0][0].ToString();
TLA_PartNum = ds.Tables[0].Rows[0][1].ToString();
TLA_PartDesc = ds.Tables[0].Rows[0][2].ToString();
excelApp1 = new Microsoft.Office.Interop.Excel.Application();
// string workbookPath = "E:\\Ruchi\\MaintainGoogleDoc\\MaintainGoogleDoc\\DemandTrackerSheet.xls";// Microsoft.Office.Interop.Excel.XlPlatform.xlWindows
filename = "\\\\mvfile\\MV REPORTS\\Master Data & Forms\\PlanningExecutionForms\\Templates\\shared Doc Costed BOM Template.xlsx";
excelApp1 = new Microsoft.Office.Interop.Excel.Application();
filename1 = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\" + TLA_PartNum + ".xlsx";//Menlo_Metrics_Kit_SnapshotTemplate.xlsx";
string workbookPath = filename.Replace("file:\\", "");
excelWorkbook = excelApp1.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
excelWorkbook.SaveAs(filename1, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
excelSheets = excelWorkbook.Worksheets;
sheet = excelSheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
sheet.Cells[1, 2] = TLA_PartNum;
sheet.Cells[2, 2] = TLA_PartDesc;
updateSheet(dt);
excelWorkbook.Save();
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
}
finally
{
//Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal
excelWorkbook.Close(true, false, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1);
}
}
private void CleanUp()
{
lock(sync) {
if(wb != null) {
wb.Close(Type.Missing, Type.Missing, Type.Missing);
Release(wb);
wb = null;
}
if(wbs != null) { Release(wbs); wbs = null; }
if(ws != null) { Release(ws); ws = null; }
if(wss != null) { Release(wss); wss = null; }
if(app != null) {
app.Quit();
Release(app);
app = null;
}
}
}