void frmdrill_FormClosed(object sender, FormClosedEventArgs e)
{
if (frmdrill.DialogResult == System.Windows.Forms.DialogResult.OK)
{
System.Data.DataTable dt = frmdrill._dataTable;
Excel.DataTable dtEx;
Excel.Workbook _wbook = (Excel.Workbook)ExcelApp.ActiveWorkbook;
_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
Excel.Worksheet _wsheet = (Excel.Worksheet)ExcelApp.ActiveSheet;
for (int i = 0; i < dt.Columns.Count; i++)
{
Excel.Range _range = (Excel.Range)_wsheet.Cells[1, i + 1];
_range.Font.Bold = true;
_range.Value = dt.Columns[i].ColumnName;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
Excel.Range _range = (Excel.Range)_wsheet.Cells[i + 2, j + 1];
_range.Value = dt.Rows[i][j];
}
}
string add = _wsheet.Name + "!R1C1:R" + (dt.Rows.Count + 1) + "C" + dt.Columns.Count;
_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
Excel.Worksheet _wpivotsheet = (Excel.Worksheet)ExcelApp.ActiveSheet;
string des = _wpivotsheet.Name + "!R3C1";
_wbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, add).CreatePivotTable(des, "PivotTable1", Type.Missing);//, Excel. Excel.XlPivotTableVersionList.xlPivotTableVersion10);
}
}
public static void ConvertExcel(string openPath, string savePath, ref string errText)
{
errText = string.Empty;
try
{
//将xml文件转换为标准的Excel格式
Object Nothing = Missing.Value; //由于yongCOM组件很多值需要用Missing.Value代替
Excel.Application ExclApp = new Excel.ApplicationClass(); // 初始化
Excel.Workbook ExclDoc = ExclApp.Workbooks.Open(openPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); //打开Excl工作薄
try
{
Object format = Excel.XlFileFormat.xlWorkbookNormal; //获取Excl 2007文件格式
ExclApp.DisplayAlerts = false;
ExclDoc.SaveAs(savePath, format, Nothing, Nothing, Nothing, Nothing, Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); //保存为Excl 2007格式
}
catch (Exception ex)
{
errText = ex.Message;
}
finally
{
ExclDoc.Close(Nothing, Nothing, Nothing);
ExclApp.Quit();
}
}
catch (Exception ex)
{
errText = ex.Message;
}
}
private void ExcelButton_Click(object sender, EventArgs e)
{
excelApp.Visible = true;
Excel.Workbook wb = excelApp.Workbooks.Add();
Excel.Worksheet sheet = wb.ActiveSheet;
for (int i = 1; i <= 10; i++)
{
for (int j = 1; j <= 10; j++)
{
sheet.Cells[i, j].Value = i * j;
if (i == j)
{
sheet.Cells[i, j].Font.Bold = true;
}
}
}
sheet.Range["A1:J1"].Font.Bold = true;
sheet.Range["A1:A10"].Font.Bold = true;
sheet.Range["A1:J1"].Interior.Color = Color.LightGray;
sheet.Range["A1:A10"].Interior.Color = Color.LightGray;
float left = (float)sheet.Range["A15"].Left;
float top = (float)sheet.Range["A15"].Top;
sheet.Shapes.AddPicture(Application.StartupPath + @"/../../logo.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, left, top, 100, 100);
}
private void button1_Click(object sender, System.EventArgs e)
{
string str_Sql, errorstring;
useExcel = true;
int i = 0, j = 0;
excelApp = new Excel.ApplicationClass();
Excel.Workbook excelBook = excelApp.Workbooks.Add(1);
Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];
if (conn.ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("无可打印内容！", "提醒！", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
try
{
//设置表头
excelSheet.Cells[1, 1] = comboBox_QuXian.Text + " 共有学生 " + label_Count.Text + " 个";
excelSheet.Cells[2, 1] = "学校类型";
excelSheet.Cells[2, 2] = "区县代码";
excelSheet.Cells[2, 3] = "区县";
excelSheet.Cells[2, 4] = "人数";
//设置表头格式
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 2]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 2]).Font.Bold = true;
str_Sql = "select * FROM View_QuXian_Student_Statistics WHERE 区县代码='" + comboBox_QuXian.SelectedValue.ToString() + "'";
errorstring = conn.Fill(str_Sql);
DataGrid1.SetDataBinding(conn.ds, "TableIn");
//填充表中各单元格
for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
{
for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
{
excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
}
}
//设置报表表格为最适应宽度
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();
excelSheet.Cells[i + 3, 1] = "完中初中部";
excelSheet.Cells[i + 3, 2] = label_Class_Type_ID_chu.Text;
excelSheet.Cells[i + 3, 3] = "人";
excelSheet.Cells[i + 4, 1] = "完中高中部";
excelSheet.Cells[i + 4, 2] = label_Class_Type_ID_gao.Text;
excelSheet.Cells[i + 4, 3] = "人";
excelApp.Visible = true;
//excelApp.Quit();
}
catch
{
throw new Exception("Excel error");
}
}
public static DataTable DataTableFromXlsx(string Path, string password = "")
{
Excel.Application objXL = null;
Excel.Workbook objWB = null;
objXL = new Excel.Application();
objWB = objXL.Workbooks.Open(Path);
Excel.Worksheet objSHT = objWB.Worksheets[1];
int rows = objSHT.UsedRange.Rows.Count;
int cols = objSHT.UsedRange.Columns.Count;
DataTable dt = new DataTable();
int noofrow = 1;
for (int c = 1; c <= cols; c++)
{
string colname = objSHT.Cells[1, c].Text;
dt.Columns.Add(colname);
noofrow = 2;
}
for (int r = noofrow; r <= rows; r++)
{
DataRow dr = dt.NewRow();
for (int c = 1; c <= cols; c++)
{
dr[c - 1] = objSHT.Cells[r, c].Text;
}
dt.Rows.Add(dr);
}
objWB.Close();
objXL.Quit();
return(dt);
}
/// <summary>
/// Export data table to Excel like DataGridTableStyle
/// </summary>
/// <param name="view"></param>
/// <param name="headers"></param>
/// <param name="indexes"></param>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
public void ExportToExcel(DataView view, string[] headers, int[] indexes, int startRow, int startCol)
{
Excel.Application excelApp = null;
Excel.Workbook excelBook = null;
Excel.Worksheet sheet = null;
try
{
excelApp = new Excel.Application();
excelBook = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
sheet = (Excel.Worksheet)excelBook.Worksheets[1];
ExportToExcel(view, headers, indexes, startRow, startCol, sheet);
excelApp.Visible = true;
}
catch (Exception ex)
{
log.Error(ex.Message, ex);
if (excelApp != null)
{
excelApp.Visible = true;
}
}
}
public void GenerateReport(IEnumerable <StudentDto> students)
{
Excel.Application app = new Excel.Application();
app.DisplayAlerts = false;
Excel.Workbook book_template = app.Workbooks.Open(@"шаблон_отчета.xlsx");
Excel._Worksheet sheet_template = book_template.Sheets["отчет"];
foreach (var ob in students)
{
//1. Создаем объкт LearnerReport из БД
LearnerReport report = new LearnerReport
{
SNS = $"{ob.surname} {ob.name} {ob.SecondName}",
SchoolName = ob.SchoolName,
ClassName = ob.ClassName,
TestResult5 = ob.TestResult5
};
//2. Экспорт объкта LearnerReport в шаблон xlsx
sheet_template.Range["C4"].Value2 = report.SNS;
sheet_template.Range["C5"].Value2 = report.SchoolName;
sheet_template.Range["C6"].Value2 = report.ClassName;
sheet_template.Range["C9"].Value2 = report.TestResult5;
//3. Сохраняем полученный файл в .pdf на рабочем столе
string file_name = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\{report.SNS}.pdf";
sheet_template.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, file_name);
}
book_template.Close(0);
book_template = null;
app.Quit();
app = null;
}
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose(bool disposing)
{
if (useExcel == true)
{
try
{
excelApp.Application.Workbooks.Close();
excelApp.Application.Quit();
excelApp.Quit();
excelBook = null;
excelSheet = null;
excelApp = null;
GC.Collect();
}
catch
{
throw new Exception("Excel 关闭错误！");
}
}
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose(disposing);
}
private void GeData()
{
string connection = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Server=192.168.0.1\SQL2005;DataBase=Test;UID=sa;PWD=pass@123";
string command = "SELECT Column1,Column2,Column3,Column4,cast((Column5*1.00)/Column4 AS DECIMAL(16,2)) as Column5 FROM PivotData";
Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)app.Workbooks.Add(Type.Missing);
Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
Excel.PivotCache pivotCache = app.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, (Excel.Range)sheet.get_Range("A1", "E10"));
pivotCache.Connection = connection;
pivotCache.MaintainConnection = true;
pivotCache.CommandText = command;
pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Type.Missing);
Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, app.ActiveCell, "PivotTable1", Type.Missing, Type.Missing);
pivotTable.SmallGrid = false;
pivotTable.ShowTableStyleRowStripes = true;
pivotTable.TableStyle2 = "PivotStyleLight1";
Excel.PivotFields rowField = (Excel.PivotFields)pivotTable.PivotFields(Type.Missing);
int fieldCount = rowField.Count;
for (int i = 1; i <= fieldCount; i++)
{
if ("Colunm" + i != "Colunm2" && "Colunm" + i != "Colunm5")
{
Excel.PivotField field = (Excel.PivotField)pivotTable.PivotFields("Column" + i);
field.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
}
}
pivotTable.AddDataField(pivotTable.PivotFields("Column4"), "Sum of Column4", Excel.XlConsolidationFunction.xlSum);
}
public void checkin(Excel.Workbook activeWorkbook)
{
try
{
if (MessageBox.Show(resources.GetString("sure_check_in"), resources.GetString("checkin"), MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.OK)
{
object saveChanges = true;
object missing = Type.Missing;
String localFileName = activeWorkbook.FullName;
activeWorkbook.Close(saveChanges, missing, missing); // Always we save document
docXML.refresh(); // Refresh document list
if (docXML.isOpenKMDocument(localFileName))
{
OKMDocument oKMDocument = docXML.getOpenKMDocument(localFileName);
docXML.remove(oKMDocument);
DocumentLogic.checkin(oKMDocument, configXML.getHost(), configXML.getUser(), configXML.getPassword());
if (File.Exists(localFileName))
{
File.Delete(localFileName);
}
}
}
}
catch (Exception e)
{
String errorMsg = "OpenKMExcelAddIn - (checkinButton_Click)\n" + e.Message + "\n\n" + e.StackTrace;
MessageBox.Show(errorMsg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
//把Excel文档转换为Pdf文档
#region
private static void ExcelToPdf(string ExcelFileName, string PdfFileName)
{
//Object missing = System.Reflection.Missing.Value;
//Object ReadOnly = (object)true;
//ET.Application app = new ET.Application();
//app.Visible = false;
//ET.workbook doc = (ET.workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//doc.ExportPdf(PdfFileName, "", "");
//doc.Close(missing, missing, missing);
//app.Quit();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
//app = null;
Object missing = System.Reflection.Missing.Value;
Object ReadOnly = (object)true;
Excel.Application app = new Excel.Application();
app.Visible = false;
Excel.Workbook doc = (Excel.Workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
doc.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, PdfFileName);
doc.Close(missing, missing, missing);
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
public void ReadSample()
{
Excel.Application excelApp = new Excel.Application();
if (excelApp != null)
{
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(@"C:\test.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];
Excel.Range excelRange = excelWorksheet.UsedRange;
int rowCount = excelRange.Rows.Count;
int colCount = excelRange.Columns.Count;
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
Excel.Range range = (excelWorksheet.Cells[i, 1] as Excel.Range);
string cellValue = range.Value.ToString();
//do anything
}
}
excelWorkbook.Close();
excelApp.Quit();
}
}
/// <summary>
/// WORKBOOK EXTENSION METHOD
/// This method return the name of the class that we
/// are embedded inside of.
/// If we are not embedded it return null.
/// If there is any exception it return null.
/// If the container cannot be accessed it returns UNKNOWN.
/// </summary>
/// <param name="PobjWb"></param>
/// <returns></returns>
public static string EmbedClassName(this Excel.Workbook PobjWb)
{
try
{
IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
IOleClientSite LobjPpClientSite;
// get the client site
LobjOleObject.GetClientSite(out LobjPpClientSite);
if (LobjPpClientSite != null)
{
IOleContainer LobjPpContainer;
LobjPpClientSite.GetContainer(out LobjPpContainer);
if (LobjPpContainer != null)
{
return(LobjPpContainer.GetType().Name);
}
else
{
// something wrong - container is not valid
return("UNKNOWN");
}
}
else
{
// not embedded
return(null);
}
}
catch (Exception ex)
{
Debug.Print(ex.ToString());
return(null); // failed
}
}
public static void PrintPreview(string fileName, PageSetup p)
{
Excel.Application excelApp = null;
Excel.Workbook workBook = null;
try
{
object objOpt = System.Reflection.Missing.Value;
excelApp = ExcelInit();
workBook = excelApp.Workbooks.Open(fileName, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt,
objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt);
if (p != null)
{
//if (p.TopMargin != 0 || p.LeftMargin != 0 || p.RightMargin != 0 || p.BottomMargin != 0)
{
int sheetCount = workBook.Sheets.Count;
Excel.Worksheet workSheet = null;
int i = 0;
while (i <= sheetCount)
{
try
{
workSheet = workBook.Sheets[i];
break;
}
catch
{
i++;
}
}
workSheet.PageSetup.LeftMargin = p.GetLeftMargin();
workSheet.PageSetup.RightMargin = p.GetRightMargin();
workSheet.PageSetup.TopMargin = p.GetTopMargin();
workSheet.PageSetup.BottomMargin = p.GetBottomMargin();
//workSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
}
}
excelApp.Visible = true;
workBook.PrintPreview(objOpt);
excelApp.Visible = false;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (workBook != null)
{
workBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
ExcelTerminal(excelApp);
}
}
private void BtnOpen_Click(object sender, EventArgs e)
{
Excel.Application excel = new Excel.Application();
string strPath = this.txtExcel.Text;
Excel.Workbook wBook = excel.Application.Workbooks.Add(strPath);
excel.Visible = true;
}
public void CreateExcelFile()
{
UserControl(false);
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));
//m_objSheets = (Excel.Worksheets)m_objBook.Sheets;
m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet;
}
private void button1_Click(object sender, System.EventArgs e)
{
string str_Sql;
useExcel = true;
int i = 0, j = 0;
excelApp = new Excel.ApplicationClass();
Excel.Workbook excelBook = excelApp.Workbooks.Add(1);
Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];
if (conn.ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("无可打印内容！", "提醒！", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
try
{
//设置表头
String str_SchoolName = conn.School_IDtoWhat(comboBox_School.SelectedValue.ToString(), "School_Name");
str_Sql = "Select * from Teacher WHERE School_ID='" + comboBox_School.SelectedValue.ToString() + "'";
string errorstring = conn.Fill(str_Sql);
excelSheet.Cells[1, 1] = str_SchoolName + " 共有教师 " + conn.ds.Tables[0].Rows.Count.ToString() + " 个";
excelSheet.Cells[2, 1] = "学校代码";
excelSheet.Cells[2, 2] = "学校名称";
excelSheet.Cells[2, 3] = "学历";
excelSheet.Cells[2, 4] = "人数";
//设置表头格式
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 4]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 4]).Font.Bold = true;
str_Sql = "Select * from View_Teacher_Statistics WHERE View_Teacher_Statistics.学校代码='" + comboBox_School.SelectedValue.ToString() + "'";
errorstring = conn.Fill(str_Sql);
DataGrid1.SetDataBinding(conn.ds, "TableIn");
//填充表中各单元格
for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
{
for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
{
excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
}
}
//设置报表表格为最适应宽度
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();
excelApp.Visible = true;
//excelApp.Quit();
}
catch
{
throw new Exception("Excel error");
}
}
private void button3_Click(object sender, System.EventArgs e)
{
useExcel = true;
excelApp = new Excel.ApplicationClass();
//Excel.Workbook
excelBook = excelApp.Workbooks.Add(1);
//Excel.Worksheet
excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];
if (conn.ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("无可打印内容！", "提醒！", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
try
{
//创建应用程序,工作表,工作簿
int i, j = 0;
excelApp.Visible = true;
//设置表头
excelSheet.Cells[2, 1] = "身份证号码";
excelSheet.Cells[2, 2] = "姓名";
excelSheet.Cells[2, 3] = "性别";
excelSheet.Cells[2, 4] = "出生日期";
excelSheet.Cells[2, 5] = "参加工作时间";
excelSheet.Cells[2, 6] = "学历";
excelSheet.Cells[2, 7] = "职称";
excelSheet.Cells[2, 8] = "职务";
excelSheet.Cells[2, 9] = "毕业院校";
excelSheet.Cells[2, 10] = "毕业日期";
excelSheet.Cells[2, 11] = "所学专业";
excelSheet.Cells[2, 12] = "是否在编";
excelSheet.Cells[2, 13] = "是否专任教师";
excelSheet.Cells[2, 14] = "担任课程";
//设置表头格式
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 14]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 14]).Font.Bold = true;
//填充表中各单元格
for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
{
for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
{
excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
}
}
//设置报表表格为最适应宽度
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();
}
catch
{
throw new Exception("Excel error");
}
}
protected override void Execute(CodeActivityContext context)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook Nuovo = xlApp.Workbooks.Add();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(PathWorkbookInput.Get(context));
var nomeFile = NomeFile.Get(context);
var nomeSheet = NomeSheet.Get(context);
}
protected override void Execute(CodeActivityContext context)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook Nuovo = xlApp.Workbooks.Add();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(PathWorkbookInput.Get(context));
var nomeFile = NomeFile.Get(context);
var nomeSheet = NomeSheet.Get(context);
Nuovo.SaveAs(System.IO.Directory.GetCurrentDirectory() + "\\" + NomeFile + NomeSheet, Excel.XlFileFormat.xlOpenXMLWorkbook);
public void DoSomething()
{
xl = new Excel.Application();
xl.Visible = true;
book = xl.Workbooks.Add(Type.Missing);
// These variables are locally scoped, so we need not worry about them.
// Notice I don't care about using two dots.
Excel.Range rng = book.Worksheets[1].UsedRange;
}
private void creat_Click(object sender, EventArgs e)
{
// textBox2.Text = UInt96.Parse(textBox1.Text).Inc().ToString();
saveFileDialog1.Title = "MAC地址生成工具----百思威科技";
saveFileDialog1.Filter = "Excel(*.xls)|*.xls";
saveFileDialog1.FileName = string.Format("MAC地址_{0}", DateTime.Now.ToString("yyyyMMdd"));
DialogResult result = saveFileDialog1.ShowDialog();
Excel._Application xlapp = new Excel.Application();
Excel.Workbook xlbook = xlapp.Workbooks.Add(true);
Excel.Worksheet xlsheet = (Excel.Worksheet)xlbook.Worksheets[1];
// MessageBox.Show(textBox2.Text);
int RowCount = Convert.ToInt32(textBox2.Text);
int RowIndex = 0;
string val = textBox1.Text;
val = val.Replace(":", "");
for (int i = 0; i < RowCount; i++)
{
RowIndex++;
if (i != 0)
{
if (radioButton1.Checked)
{
val = UInt96.Parse(val).Inc().ToString(":");
}
else
{
val = UInt96.Parse(val).Inc().ToString("");
}
xlsheet.Cells[RowIndex, 1] = val;
}
else
{
if (radioButton1.Checked)
{
string[] sArray = Regex.Split(val, @"(\w{2})");
List <string> listTemp = new List <string>();
foreach (string s in sArray)
{
if (string.IsNullOrEmpty(s))
{
continue;
}
listTemp.Add(s);
}
string[] newlist = listTemp.ToArray();
val = String.Join(":", newlist);
}
xlsheet.Cells[RowIndex, 1] = val;
}
}
xlbook.Saved = true;
xlbook.SaveCopyAs(saveFileDialog1.FileName);
xlapp.Quit();
MessageBox.Show("导出成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public static void getExcelFile()
{
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\E56626\Desktop\Teddy\VS2012\Sandbox\sandbox_test - Copy - Copy.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
//IMPORTANT SECTION
var dictionary = new Dictionary <string, List <string> >();
//iterate over the rows and columns as it appears in the file
//excel is not zero based!!
for (int i = 1; i <= rowCount; i++)
{
//it would be nice if we add some null checking to this variables. Check the article again
var col1 = xlRange.Cells[i, 1].Value2.ToString();
var col2 = xlRange.Cells[i, 2].Value2.ToString();
if (dictionary.ContainsKey(col1))
{
var existingList = dictionary[col1];
existingList.Add(col2);
}
else
{
var newList = new List <string>();
newList.Add(col2);
dictionary.Add(col1, newList);
}
}
//Do whatever you'd like with the dictionary
//END OF IMPORTANT SECTION
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
public void CleanUp()
{
book = null;
xl.Quit();
xl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
void Application_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
{
if (Application.Workbooks.Count > 1 || window.Handle == IntPtr.Zero)
{
return;
}
Cancel = true;
window.ReleaseHandle();
Dispatcher.CurrentDispatcher.BeginInvoke(new MethodInvoker(Application.Quit), null);
}
public static Excel.Workbook OpenBook(Excel.Application excelInstance, string fileName, bool readOnly, bool editable,
bool updateLinks)
{
Excel.Workbook book = excelInstance.Workbooks.Open(
fileName, updateLinks, readOnly,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
return(book);
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// new code
private void Window_Loaded(object sender, RoutedEventArgs e)
{
List <Food> listFoods = new List <Food>();
using Excel = Microsoft.Office.Interop.Excel;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"sandbox_test.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
}
/// <summary>
///
/// </summary>
private void InputExcel()
{
// if(m_dt.Rows.Count==0)
// else
// {
// m_exl=new Excel.ApplicationClass();
// m_exl.Workbooks.Add ( true );
// for(int j=0;j<m_dt.Columns.Count;j++)
// {
// for(int z=0;z<m_Title.Length;z++)
// {
// if(m_dt.Columns[j].ColumnName==m_Title[z].name)
// {
// m_exl.Cells[2,j+1]=m_Title[z].title;
// for(int i=0;i<m_dt.Rows.Count;i++)
// {
// m_exl.Cells[i+3,j+1]=m_dt.Rows[i][j];
// }
// break;
// }
//
// }
// continue;
// }
// m_exl.Visible=true;
// }
// else
// {
string str = Path.GetDirectoryName(Application.ExecutablePath) + "\\report\\GRDB.xls";
m_exl = new Excel.ApplicationClass();
// m_exl.Workbooks.Add(true);
Excel.Workbook eWork = m_exl.Workbooks.Add(str); //true)
eWork.SaveCopyAs("ll");
m_exl.Cells[2, 1] = m_time;
for (int i = 1; i < m_Title.Length; i++)
{
for (int j = 0; j < m_dt.Columns.Count; j++)
{
if (m_dt.Columns[j].ColumnName == m_Title[i].name)
{
// m_exl.Cells[2,i]=m_Title[i].title;
for (int z = 0; z < m_dt.Rows.Count; z++)
{
m_exl.Cells[z + 5, i] = m_dt.Rows[z][j];
}
}
}
}
m_exl.Visible = true;
// }
}
static void CreateInternalCOA(CSV.Common common, Excel.FinishedGoods finishedGoods, Excel.Workbook.CustomerName customerName)
{
string input;
int daysBackToInclude;
do
{
System.Console.SetCursorPosition(0, 30);
System.Console.Write(new string(' ', System.Console.WindowWidth));
System.Console.SetCursorPosition(0, 30);
System.Console.Write("Days before " + DateTime.Now.ToShortDateString() + ": ");
input = System.Console.ReadLine();
} while (int.TryParse(input, out daysBackToInclude) == false);
// A string is used as the hashset can't easily differentiate arrays with matching contents
HashSet <string> set = new HashSet <string>();
foreach (List <string> line in common.DelimitedMicroResults)
{
for (int i = 0; i < daysBackToInclude; i++)
{
if (line[5] == DateTime.Now.AddDays(i * -1).ToString("M/d/yy") || line[5] == DateTime.Now.AddDays(i * -1).ToShortDateString())
{
if (line[16] == "K1")
{
if (line[10].Contains('/') || line[10].Contains('\\'))
{
char[] delimit = { '/', '\\' };
string reformattedEntry = line[10].Split(delimit)[0];
reformattedEntry += " & ";
reformattedEntry += line[10].Split(delimit)[1];
set.Add(Convert.ToDateTime(line[9]).ToString("M-d-yy") + "," + reformattedEntry);
}
else
{
set.Add(Convert.ToDateTime(line[9]).ToString("M-d-yy") + "," + line[10]);
}
}
}
}
}
foreach (string productAndDateCombo in set)
{
Excel.Workbook workbook = new Excel.Workbook(common.DelimitedTitrationResults, common.DelimitedMicroResults, customerName, finishedGoods.Contents, common.Recipes,
Excel.Workbook.CustomerType.Internal);
workbook.InternalCOAData = productAndDateCombo.Split(new char[] { ',' });
Thread thread = new Thread(workbook.Generate);
thread.Start();
}
}
/// <summary>
/// 打开Excel文件
/// </summary>
/// <param name="filename">路径</param>
public void OpenExcelFile(string filename)
{
UserControl(false);
m_objExcel.Workbooks.Open(filename, miss, miss, miss, miss, miss, miss, miss,
miss, miss, miss, miss, miss, miss, miss);
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel.Workbook)m_objExcel.ActiveWorkbook;
//m_objSheets = (Excel.Worksheets)m_objBook.Sheets;
m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet;
}
/// <summary>
/// エクセルデータの操作を行う
/// </summary>
/// <param name="path">xlsファイルパス</param>
public ExcelController(string path)
{
exPath = path;
oXls = new Excel.Application();
//Excel画面を表示しない
oXls.Visible = false;
//Excelファイルをオープンする
oWBook = (Excel.Workbook)(oXls.Workbooks.Open(exPath));
}
//**********************************************************************
/// <summary>
/// New excel file from the giving path
/// </summary>
public void create(string strPath)
{
if(File.Exists(strPath))
{
File.Delete(strPath);
}
object Nothing = Missing.Value;
object format = Excel.XlFileFormat.xlWorkbookNormal;
xBook = excelMain.Workbooks.Add(Nothing);
xSheet = (Excel.Worksheet)xBook.Sheets[1];
//MessageBox.Show(strPath);
xBook.SaveAs(strPath,Nothing,Nothing,Nothing,Nothing,Nothing,
Excel.XlSaveAsAccessMode.xlExclusive,Nothing,Nothing,Nothing,Nothing);
}
// 打开文件
public bool Open(string file_name)
{
if (!file_name.Contains(".xlsx") && !file_name.Contains(".xls"))
return false;
if (ExcelApp == null)
return false;
try
{
Book = ExcelApp.Workbooks.Open(file_name);
return true;
}
catch (System.Exception)
{
return false;
}
}
// 创建空白工作薄
public bool Creat(string file_name)
{
if (!file_name.Contains(".xlsx") && !file_name.Contains(".xls"))
return false;
if (ExcelApp == null)
return false;
try
{
Book = ExcelApp.Workbooks.Add(Type.Missing);
Excel.XlFileFormat file_format = file_name.Contains(".xlsx") ? Excel.XlFileFormat.xlOpenXMLWorkbook : Excel.XlFileFormat.xlXMLSpreadsheet;
Book.SaveAs(file_name, FileFormat: file_format, AccessMode: Excel.XlSaveAsAccessMode.xlNoChange);
return true;
}
catch (System.Exception)
{
return false;
}
}
/// <summary>
/// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径
/// </summary>
/// <param name="templetFilePath">Excel模板文件路径</param>
/// <param name="outputFilePath">输出Excel文件路径</param>
public ExcelHelper(string templetFilePath, string outputFilePath)
{
if (templetFilePath == null)
throw new Exception("Excel模板文件路径不能为空！");
if (outputFilePath == null)
throw new Exception("输出Excel文件路径不能为空！");
if (!File.Exists(templetFilePath))
throw new Exception("指定路径的Excel模板文件不存在！");
this.templetFile = templetFilePath;
this.outputFile = outputFilePath;
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打开模板文件，得到WorkBook对象
workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
private void Dispose()
{
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (range1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
range1 = null;
}
if (range2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
range2 = null;
}
if (textBox != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(textBox);
textBox = null;
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();
this.KillExcelProcess();
}//end Dispose
/// <summary>
/// 构造函数，新建一个工作簿
/// </summary>
public ExcelHelper()
{
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
//app.Visible = true; 不自动打开
afterTime = DateTime.Now;
//新建一个WorkBook
workBook = app.Workbooks.Add(Type.Missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
/// <summary>
/// 构造函数，打开一个已有的工作簿
/// </summary>
/// <param name="fileName">Excel文件名</param>
public ExcelHelper(string fileName)
{
if (!File.Exists(fileName))
throw new Exception("指定路径的Excel文件不存在！");
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打开一个WorkBook
workBook = app.Workbooks.Open(fileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
private void WebBrowser_DocumentCompleted(object sender, System.Windows.Forms.WebBrowserDocumentCompletedEventArgs e)
{
object[] args = new object[4];
args[0] = SHDocVw.OLECMDID.OLECMDID_HIDETOOLBARS;
args[1] = SHDocVw.OLECMDEXECOPT.OLECMDEXECOPT_DONTPROMPTUSER;
args[2] = missing;
args[3] = missing;
object axWebBrowser = this.WebBrowser.ActiveXInstance;
axWebBrowser.GetType().InvokeMember("ExecWB", System.Reflection.BindingFlags.InvokeMethod, null, axWebBrowser, args);
object oApplication = axWebBrowser.GetType().InvokeMember("Document", System.Reflection.BindingFlags.GetProperty, null, axWebBrowser, null);
wbb = (Excel.Workbook)oApplication;
}
// 关闭当前工作薄
public bool Close()
{
Sheet = null;
if (Book != null)
{
Book.Close();
Book = null;
}
return true;
}
public void ExcelOpen(string EXPath, bool ReadOnly)
{
CurrentWorkBook = ExcelApp.Workbooks.Open(@EXPath,
Type.Missing, ReadOnly, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing
//,Type.Missing, Type.Missing//for officeXP
);
CurrentWorkSheet = (Excel.Worksheet)CurrentWorkBook.Worksheets.get_Item(1);
}
public void Dispose()
{
if (mUseTemporaryInstance)
{
CurrentWorkBook = null;
CurrentWorkSheet = null;
mCurrentCells = null;
ExcelApp.Quit();
GC.Collect();
}
if (oldCI != null)
Thread.CurrentThread.CurrentCulture = oldCI;
}
public void SelectWorkBook(int workBookIndex)
{
CurrentWorkBook = ExcelApp.Workbooks[workBookIndex];
CurrentWorkSheet = (Excel.Worksheet)CurrentWorkBook.Worksheets.get_Item(1);
}
//===========================================
// WORKBOOKS WORK
//===========================================
/// <summary>
/// Adds needed new WorkBook with several sheets,
/// and selected this book as Active.
/// </summary>
/// <param name="sheetsCount"></param>
public void AddWorkBook(int sheetsCount)
{
if (sheetsCount < 1) sheetsCount = 1;
ExcelApp.SheetsInNewWorkbook = sheetsCount;
ExcelApp.Workbooks.Add(Type.Missing);
CurrentWorkBook = ExcelApp.ActiveWorkbook;
CurrentWorkSheet = (Excel.Worksheet)CurrentWorkBook.Worksheets.get_Item(1);
}
//**********************************************************************
/// <summary>
/// Open excel file from the giving path
/// </summary>
public void open(string strPath)
{
xBook = excelMain.Workbooks.Open(strPath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
xSheet = (Excel.Worksheet)xBook.Sheets[1];
}
