//FIX Excel duplication
public static void Print(string PathToFile)
{
if (!TimerSettings.PrintingEnabled)
{
return;
}
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks wbs = Excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(
PathToFile,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
if (TimerSettings.PrintMode == PrintModes.Both)
{
wb.PrintOutEx(1, 2);
}
else
{
wb.PrintOutEx(1, 1);
}
GC.WaitForPendingFinalizers();
wb.Close(0);
Marshal.FinalReleaseComObject(wb);
wbs.Close();
Marshal.FinalReleaseComObject(wbs);
Excel.Quit();
Marshal.FinalReleaseComObject(Excel);
GC.Collect();
}
protected virtual void Dispose(bool disposing)
{
if (!base.disposed)
{
if (disposing)
{
workbook.Close(false, Type.Missing, Type.Missing);
workbooks.Close();
//关闭退出
excel.Quit();
//释放 COM 对象
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excel);
worksheet = null;
workbook = null;
workbooks = null;
excel = null;
}
base.disposed = true;
}
}
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);*/
}
}
private void Close()
{
#region 关闭Excel进程
if (wb != null)
{
wb.Close(false, _savepath, Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wb = null;
}
if (wbs != null)
{
wbs.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
wbs = null;
}
if (xlApp != null)
{
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
#endregion
}
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 ExcelListReaderBlock(String filePath)
{
excelApp = new Microsoft.Office.Interop.Excel.Application();
workBooks = excelApp.Workbooks;
workBook = workBooks.Open(filePath, @ReadOnly: true, Editable: false);
workSheet = workBook.Sheets.Item[1];
}
void Upid()
{
excelappworkbooks = new Microsoft.Office.Interop.Excel.Application().Workbooks;
excelworksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelappworkbooks.Application.Workbooks.Open(fns,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing).Worksheets.get_Item(1);
for (int i = 2; i <= maxS; i++)
{
Thread.Sleep(1);
try
{
Dispatcher.Invoke(() => Win.settings.listExcel.Add(new ExcelTaga((DateTime.FromOADate(int.Parse(Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 11]).Value2)))).ToString("dd.MM.yyyy"),
Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 3]).Value2),
Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 4]).Value2),
Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 5]).Value2),
Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 10]).Value2),
Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 13]).Value2))));
}
catch
{
Dispatcher.Invoke(() => Win.settings.listExcel.Add(new ExcelTaga(Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 11]).Value2),
Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 3]).Value2),
Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 4]).Value2),
Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 5]).Value2),
Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 10]).Value2),
Convert.ToString(((Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[i, 13]).Value2))));
}
Dispatcher.Invoke(() => Kol.Content = i + " из " + maxS);
}
Dispatcher.Invoke(() => Title = "Готово!");
Dispatcher.Invoke(() => Oke.Visibility = Visibility.Visible);
}
public void ExportExcelFile(DataTable dt, string filrname)
{
if (dt != null)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = filrname;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
int rowCount = dt.Rows.Count; //行数
int columnCount = dt.Columns.Count; //列数
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
Application.DoEvents();
}
worksheet.Columns.AutoFit();
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件： " + filrname + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
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("完成！");
}
}
//打开一个Microsoft.Office.Interop.Excel文件
public void Open(string FileName)
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
mFilename = FileName;
}
/// <summary>
/// //创建一个Excel对象
/// </summary>
public void CreateNewExcel()
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
}
static public void Save2excel(DataGrid dataGrid)
{
int colcount = 0;
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel 文件|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
System.Windows.MessageBox.Show("无法创建Excel对象，您可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
//统计可见列的数目
for (int i = 0; i < dataGrid.Columns.Count; i++)
{
if (dataGrid.Columns[i].Visibility == Visibility.Visible)
{
colcount++;
}
}
//写入列头
int k = 0;
for (int i = 0; i < dataGrid.Columns.Count; i++)
{
if (dataGrid.Columns[i].Visibility == Visibility.Visible)
{
worksheet.Cells[1, k + 1] = dataGrid.Columns[i].Header;
k++;
}
}
k = 0;
for (int r = 0; r < dataGrid.Items.Count; r++)
{
for (int i = 0; i < dataGrid.Columns.Count - 1; i++) //暂时不处理新样选择标样列，所以标样最后一列也不保存
{
if (dataGrid.Columns[i].Visibility == Visibility.Hidden ||
dataGrid.Columns[i].Visibility == Visibility.Collapsed)
{
continue;
}
if ((dataGrid.Columns[i].GetCellContent(dataGrid.Items[r]) is null))
{
worksheet.Cells[r + 2, k + 1] = "NaN";
}
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);
}
}
/// <summary>
/// 获取Workbook
/// </summary>
/// <param name="table"></param>
/// <param name="filename"></param>
public Microsoft.Office.Interop.Excel.Workbook GetExcelWorkbook()
{
if (_tables.Count == 0)
{
throw new Exception("Tables集合必须大于零!");
}
if (_rowindex < 0)
{
_rowindex = 0;
}
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
wbs = xlApp.Workbooks;
wb = wbs.Add(Missing.Value); //添加一个工作簿
//添加Sheet表,新建一个Excel文件时候,一般会默认有3个Sheet表,所以用[table.Count - wb.Sheets.Count]
int tabcount = _tables.Count;
int sheets = wb.Worksheets.Count; //获取默认Sheet表个数,一般默认3个
if (tabcount > sheets)
{
wb.Worksheets.Add(Missing.Value, Missing.Value, tabcount - sheets, Missing.Value);
}
//删除多余Sheet表
//((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[index]).Delete();
//写入Excel
WriteExcelSheet(wb);
//保存工作表
//xlApp.ActiveWorkbook.SaveCopyAs(filename);
//wb.SaveCopyAs(_savepath);
return(wb);
}
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);
}
}
private void ExportExcel(string fileName, DataGridView myDGV)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得 sheet1
//写入标题
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch (Exception ex)
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
// if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
MessageBox.Show(fileName + "的简明资料保存成功", "提示", MessageBoxButtons.OK);
}
private void ExportExcel(string fileName, DataGridView customerDataGridView)
{
if (customerDataGridView.Rows.Count > 0)
{
string saveFileName = "";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.DefaultExt = "xls";
saveFileDialog.Filter = "Excel File|*.xls";
saveFileDialog.FileName = fileName;
saveFileDialog.ShowDialog();
saveFileName = saveFileDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
for (int i = 0; i < customerDataGridView.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = customerDataGridView.Columns[i].HeaderText;
}
for (int r = 0; r < customerDataGridView.Rows.Count; r++)
{
for (int i = 0; i < customerDataGridView.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = customerDataGridView.Rows[r].Cells[i].Value;
}
Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("export error, the file may be opened now\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();
MessageBox.Show(fileName + " save successful", "prompt", MessageBoxButtons.OK);
}
else
{
MessageBox.Show("Veri listesi yok", "prompt", MessageBoxButtons.OK);
}
}
/// <summary>
/// Mở file Excel có đường dẫn "path"
/// </summary>
/// <param name="path"></param>
public static void OpenFile(string path)
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook sheet = workbooks.Open(path);
}
private void allocExcel()
{
if (avaExcel)
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlBooks = xlApp.Workbooks;
}
}
/// <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));
}
/// <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));
}
//导出Execl明细
private void btn_dao_Click(object sender, EventArgs e)
{
#region 导出Execl的具体代码
string fileName = "汤臣一品小区物业员工信息表";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
//写入标题
for (int i = 0; i < staff_dgv.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = staff_dgv.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < staff_dgv.Rows.Count; r++)
{
for (int i = 0; i < staff_dgv.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = staff_dgv.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
#endregion
}
public static void exportDataTablesToSpreadSheets(Dictionary <string, DataTable> SpreadSheetNamesAndDataTables)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
int counterOfSpreadSheets = 1;
foreach (KeyValuePair <string, DataTable> kvp in SpreadSheetNamesAndDataTables)
{
DataTable spreadsheetDataSource = kvp.Value;
if (spreadsheetDataSource.Rows.Count > 0)
{
if (counterOfSpreadSheets > 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, worksheet, missing, missing);
}
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[counterOfSpreadSheets];
worksheet.Name = kvp.Key;
int NumberOfColumns = spreadsheetDataSource.Columns.Count;
int NumberOfRows = spreadsheetDataSource.Rows.Count;
String[,] dataArray = new String[NumberOfRows + 1, NumberOfColumns];
for (int hearderColumn = 0; hearderColumn < NumberOfColumns; hearderColumn++)
{
dataArray[0, hearderColumn] = spreadsheetDataSource.Columns[hearderColumn].ColumnName;
}
;
DataRow dtRow = null;
for (int rowNumber = 0; rowNumber < NumberOfRows; rowNumber++)
{
dtRow = spreadsheetDataSource.Rows[rowNumber];
for (int columnNum = 0; columnNum < NumberOfColumns; columnNum++)
{
dataArray[rowNumber + 1, columnNum] = "'" + dtRow.ItemArray[columnNum].ToString().Trim();
}
;
}
;
worksheet.Range["A1"].Resize[NumberOfRows + 1, NumberOfColumns].Value = dataArray;
worksheet.Cells.EntireColumn.AutoFit();
}
;
counterOfSpreadSheets++;
}
workbook.Worksheets[1].Activate();
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
// public void PrintExcel(string FileName, string ConnectionString, string coTitle, string sFontName, string Lang, string where,
// string where1, string where2, string where3, string where4, string where5, string where6, string where7, string where8,
// string where9, string where10, string User)
// {
// object oMissing = Missing.Value;
// Excel.Application oExcel = new Excel.ApplicationClass();
// Excel.Workbooks oBooks = oExcel.Workbooks;
// Excel._Workbook oBook = null;
//
// System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
// System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
// try
// {
// oBook = oBooks.Open(FileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
// oMissing, oMissing, oMissing, oMissing, oMissing);
// oExcel.Visible = true;
//
// //Cap nhat properties
// SetProperty(oBook, "ConnectionString", ConnectionString);
// SetProperty(oBook, "CoTitle", coTitle);
// SetProperty(oBook, "sFontName", sFontName);
// SetProperty(oBook, "Lang", Lang);
// SetProperty(oBook, "where", where);
// SetProperty(oBook, "where1", where1);
// SetProperty(oBook, "where2", where2);
// SetProperty(oBook, "where3", where3);
// SetProperty(oBook, "where4", where4);
// SetProperty(oBook, "where5", where5);
// SetProperty(oBook, "where6", where6);
// SetProperty(oBook, "where7", where7);
// SetProperty(oBook, "where8", where8);
// SetProperty(oBook, "where9", where9);
// SetProperty(oBook, "where10", where10);
// SetProperty(oBook, "User", User);
//
// try
// {
// RunMacro(oExcel, new object[]{"CreatReport"});
// }
// catch{}
// }
// catch(Exception ex)
// {
// System.Windows.Forms.MessageBox.Show(ex.Message, "Error!");
// }
// finally
// {
// System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
// System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
// GC.Collect();
// }
// }
public static void PrintExcel(string FileName, string ConnectionString, string coTitle, string sFontName, string Lang, string where,
string where1, string where2, string where3, string where4, string where5, string where6, string where7, string where8,
string where9, string where10, string where11, string where12, string where13, string User)
{
object oMissing = Missing.Value;
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks oBooks = oExcel.Workbooks;
Microsoft.Office.Interop.Excel._Workbook oBook = null;
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
try
{
oBook = oBooks.Open(FileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing);
oExcel.Visible = true;
//Cap nhat properties
SetProperty(oBook, "ConnectionString", ConnectionString);
SetProperty(oBook, "CoTitle", coTitle);
SetProperty(oBook, "sFontName", sFontName);
SetProperty(oBook, "Lang", Lang);
SetProperty(oBook, "where", where);
SetProperty(oBook, "where1", where1);
SetProperty(oBook, "where2", where2);
SetProperty(oBook, "where3", where3);
SetProperty(oBook, "where4", where4);
SetProperty(oBook, "where5", where5);
SetProperty(oBook, "where6", where6);
SetProperty(oBook, "where7", where7);
SetProperty(oBook, "where8", where8);
SetProperty(oBook, "where9", where9);
SetProperty(oBook, "where10", where10);
SetProperty(oBook, "where11", where11);
SetProperty(oBook, "where12", where12);
SetProperty(oBook, "where13", where13);
SetProperty(oBook, "User", User);
try
{
RunMacro(oExcel, new object[] { "CreatReport" });
}
catch {}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message, "Error!");
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
GC.Collect();
}
}
public Service()
{
_xlApp = new Microsoft.Office.Interop.Excel.Application();
_xlBooks = _xlApp.Workbooks;
_dirs = new List <Dir>();
_books = new List <Book>();
_sheets = new List <Sheet>();
_rows = new List <Row>();
}
public Xl(bool fetchData = false)
{
_xlApp = new Microsoft.Office.Interop.Excel.Application();
_xlBooks = _xlApp.Workbooks;
if (fetchData)
{
SetRows();
}
}
// Method for open file from save location
public void openFile(string path)
{
var excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = true;
Microsoft.Office.Interop.Excel.Workbooks books = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook sheet2 = books.Open(path);
}
/// <summary>
/// 导出到excel
/// <param name="fileName">默认文件名</param>
/// <param name="listView">数据源，一个页面上的ListView控件</param>
/// <param name="titleRowCount">标题占据的行数，为0表示无标题</param>
public static void ExportExcel()
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//string date = DateTime.Now.ToString("yyyy-MM-dd");
MergeCells(worksheet, 1, 1, 1, 7, " 生产情况");
MergeCells(worksheet, 2, 1, 22, 1, "白班");
MergeCells(worksheet, 23, 1, 42, 1, "晚班");
worksheet.Cells[2, 2] = "机器号";
worksheet.Cells[2, 3] = "开机时间";
worksheet.Cells[2, 4] = "停机时间";
worksheet.Cells[2, 5] = "效率";
worksheet.Cells[2, 6] = "产量";
for (int i = 1; i <= 20; i++)
{
worksheet.Cells[i + 2, 2] = "M" + i.ToString();
worksheet.Cells[i + 22, 2] = "M" + i.ToString();
}
HVCenterAlign(worksheet, 2, 2, 42, 6);
System.Windows.Forms.Application.DoEvents();
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
//if (Microsoft.Office.Interop.cmbxType.Text != "Notification")
//{
// Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[ds.Tables[0].Rows.Count + 1, 2]);
// rg.NumberFormat = "00000000";
//}
if (saveFileName != null)
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (System.Exception ex)
{
MessageBox.Show("导出文件可能出错" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("导出Excel成功", "提示", MessageBoxButtons.OK);
}
public void Open(string FileName)//打开一个Microsoft.Office.Interop.Excel文件
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
//wb = wbs.Open(FileName, 0, true, 5,"", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
//wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
mFilename = FileName;
}
/// <summary>
/// “历史界面”导出按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ExcelOut_Click(object sender, EventArgs e)
{
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
fileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，你设备未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
MessageBox.Show("文件已导出到" + fileName, "导出成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
xlApp.Quit();
GC.Collect();//强行销毁
}
public static void ExportExcel(string fileName, DataGridView myDGV)
//导出函数有两个参数，一个为保存文件名，另一个为当前显示数据的datagridview
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();//建立保存对话框
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp
= new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题，headertext为标题属性
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[2, i + 1] = myDGV.Columns[i].HeaderText;
}
//写入数值
worksheet.Cells[1, 1] = fileName;
//写入导出数据的表格名称
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 3, i + 1] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Cells[myDGV.Rows.Count + 4, 2] = "导出数据时间："; //显示导出时间
worksheet.Cells[myDGV.Rows.Count + 4, 3] = Convert.ToString(DateTime.Now);
worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应
if (saveFileName != "")
{
try
{ workbook.Saved = true; workbook.SaveCopyAs(saveFileName); }
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
MessageBox.Show(fileName + "的简明资料保存成功", "提示", MessageBoxButtons.OK);
}
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;
}
}
}
private void Close()
{
#region 关闭Excel进程
if (wb != null)
{
wb.Close(false, _savepath, Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wb = null;
}
if (wbs != null)
{
wbs.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
wbs = null;
}
if (xlApp != null)
{
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
#endregion
}
/// <summary>
/// 获取Workbook
/// </summary>
/// <param name="table"></param>
/// <param name="filename"></param>
public Microsoft.Office.Interop.Excel.Workbook GetExcelWorkbook()
{
if (_tables.Count == 0)
{
throw new Exception("Tables集合必须大于零!");
}
if (_rowindex < 0)
{
_rowindex = 0;
}
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
wbs = xlApp.Workbooks;
wb = wbs.Add(Missing.Value); //添加一个工作簿
//添加Sheet表,新建一个Excel文件时候,一般会默认有3个Sheet表,所以用[table.Count - wb.Sheets.Count]
int tabcount = _tables.Count;
int sheets = wb.Worksheets.Count; //获取默认Sheet表个数,一般默认3个
if (tabcount > sheets)
{
wb.Worksheets.Add(Missing.Value, Missing.Value, tabcount - sheets, Missing.Value);
}
//删除多余Sheet表
//((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[index]).Delete();
//写入Excel
WriteExcelSheet(wb);
//保存工作表
//xlApp.ActiveWorkbook.SaveCopyAs(filename);
//wb.SaveCopyAs(_savepath);
return wb;
}
