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 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; } } } }
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"); }
/// <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)); }
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 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(); }
/// <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) { } }
private void createExcelFile(List <SqlClient.LicenseUse> accumulatedList) { string filename = Path.GetTempFileName(); filename = Path.ChangeExtension(filename, ".xlsx"); File.WriteAllBytes(filename, Properties.Resources.ExcelTemplate); Microsoft.Office.Interop.Excel.Application excel; Microsoft.Office.Interop.Excel.Workbook workbook; Microsoft.Office.Interop.Excel.Worksheet worksheet; excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; workbook = excel.Workbooks.Open(Path.GetFullPath(filename)); try { Microsoft.Office.Interop.Excel.Sheets excelSheets = workbook.Worksheets; worksheet = excelSheets.get_Item(1) as Microsoft.Office.Interop.Excel.Worksheet; int row = 1; foreach (SqlClient.LicenseUse lu in accumulatedList.OrderBy(n => n.Profile).ThenBy(n => n.License)) { row++; worksheet.Cells[row, 1] = lu.Profile; worksheet.Cells[row, 2] = lu.License; worksheet.Cells[row, 3] = lu.Count; } workbook.Save(); } catch (Exception e) { try { File.Delete(filename); } catch { } throw e; } finally { workbook.Close(); excel.Quit(); } System.Diagnostics.Process.Start(filename); }
public static void ReadExistingExcel() { string path = @"C:\Tool\Reports1.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; int colCount = range.Columns.Count; int rowCount = range.Rows.Count; for (int index = 1; index < 15; index++) { mWSheet1.Cells[rowCount + index, 1] = rowCount + index; mWSheet1.Cells[rowCount + index, 2] = "New Item" + index; } 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(); }
/// <summary> /// 打印交易票 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void printBtn_Click(object sender, EventArgs e) { try { vehicle = VehicleDao.GetBySerial(this.serial.Text); vehicle.Isprinted = true; vehicle.Isgrant = false; VehicleDao.UpdateVehicle(vehicle); } catch (Exception ex) { MessageBox.Show(ex.Message); return; } Object oMissing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application m_objExcel = null; Microsoft.Office.Interop.Excel._Workbook m_objBook = null; Microsoft.Office.Interop.Excel.Sheets m_objSheets = null; Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null; Microsoft.Office.Interop.Excel.Range m_objRange = null; try { m_objExcel = new Microsoft.Office.Interop.Excel.Application(); DirectoryInfo Dir = new DirectoryInfo("."); m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Input.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1)); // 开票日期 m_objRange = m_objSheet.get_Range("B1", oMissing); m_objRange.Value = DateTime.ParseExact(VehicleDao.GetCurrentDate(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture).ToString("yyyy/MM/dd"); // 开票人 m_objRange = m_objSheet.get_Range("K19", oMissing); m_objRange.Value = LoginForm.user.UsersName; // 买 方 单 位 /个人 m_objRange = m_objSheet.get_Range("C5", oMissing); m_objRange.Value = this.currentName.Text; // 单位代码/身份证号码 m_objRange = m_objSheet.get_Range("K5", oMissing); m_objRange.Value = this.currentId.Text; // 买方单位/个人住址 m_objRange = m_objSheet.get_Range("C6", oMissing); m_objRange.Value = this.currentAddress.Text; // 电话 m_objRange = m_objSheet.get_Range("L6", oMissing); m_objRange.Value = this.currentPhone.Text; // 卖 方 单 位/ 个人 m_objRange = m_objSheet.get_Range("C7", oMissing); m_objRange.Value = this.originName.Text; // 单位代码/身份证号码 m_objRange = m_objSheet.get_Range("K7", oMissing); m_objRange.Value = this.originId.Text; // 卖方单位/个人住址 m_objRange = m_objSheet.get_Range("C8", oMissing); m_objRange.Value = this.originAddress.Text; // 电话 m_objRange = m_objSheet.get_Range("L8", oMissing); m_objRange.Value = this.originPhone.Text; // 车 牌 照 号 m_objRange = m_objSheet.get_Range("C9", oMissing); m_objRange.Value = "辽B." + this.license.Text; // 登记证号 m_objRange = m_objSheet.get_Range("E9", oMissing); m_objRange.Value = this.certificate.Text; // 车 辆 类 型 m_objRange = m_objSheet.get_Range("L9", oMissing); m_objRange.Value = this.vehicleType.Text; // 车架号/车辆识别代码 m_objRange = m_objSheet.get_Range("C10", oMissing); m_objRange.Value = this.vin.Text; // 厂牌型号 m_objRange = m_objSheet.get_Range("E10", oMissing); m_objRange.Value = this.brand.Text; // 转入地车辆管理所名称 m_objRange = m_objSheet.get_Range("L10", oMissing); m_objRange.Value = this.department.Text; // 车价 合 计(大写) m_objRange = m_objSheet.get_Range("C11", oMissing); m_objRange.Value = this.transactions.Text; // 车价 合 计(小写) m_objRange = m_objSheet.get_Range("L11", oMissing); m_objRange.Value = this.transactions.Text; m_objExcel.DisplayAlerts = false; m_objSheet.PrintOut(); } catch (Exception ex) { MessageBox.Show("打印失败,请检查打印机设置。错误信息:" + ex.Message); } finally { if (m_objBook != null) { m_objBook.Close(oMissing, oMissing, oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); } if (m_objExcel != null) { m_objExcel.Workbooks.Close(); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); } m_objBook = null; m_objExcel = null; GC.Collect(); } this.resetControlContent(); this.setControlReadOnly(true); this.serial.Focus(); this.serial.Select(this.serial.Text.Length, 0); }
/// <summary> /// 创建新的EXCEL并写入数据 /// </summary> /// <param name="dataSet"></param> /// <param name="savePath"></param> public void ToExcel(DataSet dataSet, string savePath) { DataTable ds = dataSet.Tables[0]; int rowNumber = ds.Rows.Count; int columnNumber = ds.Columns.Count; if (rowNumber == 0) { MessageBox.Show("没有任何数据可以导入到Excel文件!"); } //建立Excel对象 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks wbks = excel.Workbooks; Microsoft.Office.Interop.Excel._Workbook _wbk = wbks.Add(true); Microsoft.Office.Interop.Excel.Sheets shs = _wbk.Sheets; Microsoft.Office.Interop.Excel._Worksheet _wsh = (Microsoft.Office.Interop.Excel._Worksheet)shs.get_Item(1); excel.Application.Workbooks.Add(true); excel.Visible = false;//是否打开该Excel文件 List <uint> fieldPlaceList; List <Dictionary <uint, string[]> > combineRowList = GetCombineList(dataSet, out fieldPlaceList); //数据合并 List <List <StringBuilder> > combineList = new List <List <StringBuilder> >(); for (int i = 0; i < combineRowList.Count; i++) { // combineList.Add(DataCombine(dataSet, combineRowList[i])); } //填充数据 for (int c = 0; c < rowNumber; c++) { for (int j = 0; j < ds.Columns.Count + combineList.Count; j++)//一行中的每列 { if (j >= ds.Columns.Count) { int index = j - ds.Columns.Count; switch (c) { case 0: _wsh.Cells[c + 1, j + 1] = "合并字段#客户端"; break; case 1: _wsh.Cells[c + 1, j + 1] = "@" + combineList[index][2].ToString().Replace('&', '#') + " " + combineList[index][3].ToString().Replace('_', ' '); break; case 2: _wsh.Cells[c + 1, j + 1] = "uint#" + combineList[index][3].ToString(); break; case 3: _wsh.Cells[c + 1, j + 1] = combineList[index][3].ToString(); break; default: _wsh.Cells[c + 1, j + 1] = combineList[index][c].ToString(); break; } } else { //每个单元格内容,加到StringBuilder中 if (fieldPlaceList.Contains((uint)j) && c == 0) { _wsh.Cells[c + 1, j + 1] = ds.Rows[c].ItemArray[j] + "#合并"; } else { _wsh.Cells[c + 1, j + 1] = ds.Rows[c].ItemArray[j]; } } } } _wbk.SaveCopyAs(savePath); }
/// <summary> /// 打印 /// </summary> private void printInfo() { Object oMissing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application m_objExcel = null; Microsoft.Office.Interop.Excel._Workbook m_objBook = null; Microsoft.Office.Interop.Excel.Sheets m_objSheets = null; Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null; Microsoft.Office.Interop.Excel.Range m_objRange = null; try { m_objExcel = new Microsoft.Office.Interop.Excel.Application(); DirectoryInfo Dir = new DirectoryInfo("."); m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Alter.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1)); // 号牌种类 m_objRange = m_objSheet.get_Range("D2", oMissing); m_objRange.Value = this.category.Text; // 号牌号码 m_objRange = m_objSheet.get_Range("H2", oMissing); m_objRange.Value = "辽B" + this.license.Text; // 变更机动车所有人姓名/名称 m_objRange = m_objSheet.get_Range("D4", oMissing); m_objRange.Value = this.ownerName.Text; //共同所有的机动车变更所有人 m_objRange = m_objSheet.get_Range("D5", oMissing); m_objRange.Value = this.owner.Text; // 住所在车辆管理所辖区内迁移 m_objRange = m_objSheet.get_Range("D6", oMissing); m_objRange.Value = this.newAddress.Text; // 邮寄地址 m_objRange = m_objSheet.get_Range("D7", oMissing); m_objRange.Value = " " + this.postAddress.Text; // 邮政编码 m_objRange = m_objSheet.get_Range("D8", oMissing); m_objRange.Value = " " + this.postcode.Text; // 电子信箱 m_objRange = m_objSheet.get_Range("D9", oMissing); m_objRange.Value = " " + this.email.Text; // 固定电话 m_objRange = m_objSheet.get_Range("H8", oMissing); m_objRange.Value = " " + this.phone.Text; // 移动电话 m_objRange = m_objSheet.get_Range("H9", oMissing); m_objRange.Value = " " + this.mobile.Text; // 省(自治县、直辖市) m_objRange = m_objSheet.get_Range("D10", oMissing); m_objRange.Value = " " + this.province.Text; // 车辆管理所 m_objRange = m_objSheet.get_Range("H10", oMissing); m_objRange.Value = this.department.Text; // 变更后的信息 m_objRange = m_objSheet.get_Range("D15", oMissing); m_objRange.Value = this.information.Text; // 代理人姓名、名称 m_objRange = m_objSheet.get_Range("C21", oMissing); m_objRange.Value = this.agentName.Text; // 代理人邮寄地址 m_objRange = m_objSheet.get_Range("C22", oMissing); m_objRange.Value = this.agentAddress.Text; // 代理人邮政编码 m_objRange = m_objSheet.get_Range("C23", oMissing); m_objRange.Value = this.agentPostcode.Text; // 代理人联系电话 m_objRange = m_objSheet.get_Range("F23", oMissing); m_objRange.Value = this.agentPhone.Text; // 代理人电子信箱 m_objRange = m_objSheet.get_Range("C24", oMissing); m_objRange.Value = this.agentEmail.Text; // 经办人电话 m_objRange = m_objSheet.get_Range("C25", oMissing); m_objRange.Value = this.handlerName.Text; // 经办人联系电话 m_objRange = m_objSheet.get_Range("F25", oMissing); m_objRange.Value = this.handlerPhone.Text; // 办理日期 m_objRange = m_objSheet.get_Range("H20", oMissing); m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day + " "; m_objRange = m_objSheet.get_Range("H25", oMissing); m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day + " ";; m_objExcel.DisplayAlerts = false; m_objBook.Save(); m_objSheet.PrintOut(); } catch (Exception ex) { // 输出异常信息 MessageBox.Show("打印失败,异常信息为:" + ex.Message); } finally { if (m_objBook != null) { m_objBook.Close(oMissing, oMissing, oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); } if (m_objExcel != null) { m_objExcel.Workbooks.Close(); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); } m_objBook = null; m_objExcel = null; GC.Collect(); } }
private void Run_Report(object Rpt_Code_Rpt_Name_Rpt_Year_Rpt_Prd) { System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); try { string[] Shet = Rpt_Code_Rpt_Name_Rpt_Year_Rpt_Prd.ToString().Split((new string[] { "##" }), StringSplitOptions.None); string Rpt_Code = Shet[0]; string Rpt_Name = Shet[1]; int Rpt_Year = int.Parse(Shet[2]); int Rpt_Prd = int.Parse(Shet[3]); DataTable dtCols = DataClass.RetrieveData("SELECT GRCT.Col_No, GRCT.Col_Type, GRCT.Col_Source, GRCT.Col_Range, GRCT.Col_Period, GRCT.Col_Header, GRCT.Col_Calc_Formula, GRCT.Col_Prec_Source, GRCT.Col_Width, GRCT.Col_Sup, GRCT.Col_Calc_Divide, GR.Daily_Rev FROM dbo.G_Report AS GR LEFT OUTER JOIN dbo.G_Report_Col_Det AS GRCT ON GR.Col_Code = GRCT.Col_Code WHERE (GR.Rpt_Code = N'" + Rpt_Code + "')"); DataTable dtRows = DataClass.RetrieveData("SELECT GRCT.Row_No, GRCT.Row_Type, GRCT.Row_Accounts, GRCT.Row_Divide_By_Acc, GRCT.Row_Desc, GRCT.Row_TOT_Formula, GRCT.Row_Prec_Row, GRCT.Row_Rev, GRCT.Row_Sup, GRCT.Row_Format, GRCT.Row_Divide_By_Amt, GR.Daily_Rev FROM dbo.G_Report_Row_Det AS GRCT RIGHT OUTER JOIN dbo.G_Report AS GR ON GRCT.Row_Code = GR.Row_Code WHERE (GR.Rpt_Code = N'" + Rpt_Code + "')"); Microsoft.Office.Interop.Excel.ApplicationClass myexl = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook mywb = myexl.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Sheets mysh = mywb.Sheets; string currentSheet = "Sheet1"; Microsoft.Office.Interop.Excel.Worksheet mywsh = (Microsoft.Office.Interop.Excel.Worksheet)mysh.get_Item(currentSheet); mywsh.Name = Rpt_Name; string letter = GetLetter(dtCols.Rows.Count); Microsoft.Office.Interop.Excel.Range rr = mywsh.get_Range("A1", "A1"); ////for (int c = 0; c < DT.Columns.Count; c++) ////{ //// rr[1, c + 1] = DT.Columns[c].ColumnName; ////} //rr.Font.Bold = true; for (int row = 0; row < dtRows.Rows.Count; row++) { string Accounts = dtRows.Rows[row]["Row_Accounts"].ToString(); Accounts = Accounts.Replace("-", ""); Accounts = Accounts.Replace("(", ""); Accounts = Accounts.Replace(")", ""); if (Accounts.Contains("TO")) { Accounts = Accounts.Replace("TO", "AND"); } else { Accounts = Accounts + " AND " + Accounts; } string Divide_Acc = dtRows.Rows[row]["Row_Divide_By_Acc"].ToString(); if (Divide_Acc.Trim() != "") { Divide_Acc = Divide_Acc.Replace("-", ""); Divide_Acc = Divide_Acc.Replace("(", ""); Divide_Acc = Divide_Acc.Replace(")", ""); if (Divide_Acc.Contains("TO")) { Divide_Acc = Divide_Acc.Replace("TO", "AND"); } else { Divide_Acc = Divide_Acc + " AND " + Divide_Acc; } } if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "DTL" || dtRows.Rows[row]["Row_Type"].ToString().Trim() == "TOT") { for (int col = 0; col < dtCols.Rows.Count; col++) { rr = mywsh.get_Range(dtCols.Rows[col]["Col_No"].ToString() + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), dtCols.Rows[col]["Col_No"].ToString() + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString()); if (dtCols.Rows[col]["Col_Type"].ToString() == "GL") { if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "DTL") { DateTime Start_Date = DateTime.Now.Date; DateTime End_Date = DateTime.Now.Date; string str = "GLTotals"; string strPrd = "PeriodBalance"; if (dtCols.Rows[col]["Col_Period"].ToString().Substring(0, 3) == "PRD") { Rpt_Prd = int.Parse(dtCols.Rows[col]["Col_Period"].ToString().Remove(0, 3)); } switch (dtCols.Rows[col]["Col_Source"].ToString()) { case "LST": Rpt_Year--; break; case "BGT": str = "GLBudget"; strPrd = "Period"; break; } if (!DataClass.isExsist("*", "FiscalYear = " + Rpt_Year, "GLFiscalPeriodSetup")) { rr[1, 1] = 0; } else { switch (dtCols.Rows[col]["Col_Range"].ToString()) { case "MTD": if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Source"].ToString() != "BGT") { string PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID" + Rpt_Prd + " From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year); Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodStartDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID)); End_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodEndDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID)); } else { strPrd += Rpt_Prd.ToString(); } break; case "YTD": if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Source"].ToString() != "BGT") { string PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID1 From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year); Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodStartDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID)); PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID" + Rpt_Prd + " From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year); End_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodEndDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID)); } else { string PeriodID = DataClass.ReturnRecordNameByID("Select MIN(PeriodID) AS PeriodID From GLFiscalPeriod WHERE Status='OPEN'"); string PeriodNumber = DataClass.ReturnRecordNameByID("Select PeriodNumber From GLFiscalPeriod WHERE PeriodID = " + PeriodID); string FiscalYear = DataClass.ReturnRecordNameByID("Select FiscalYear From GLFiscalPeriodSetup WHERE PeriodID" + PeriodNumber + " = " + PeriodID); if (int.Parse(FiscalYear) > Rpt_Year) { strPrd += Rpt_Prd.ToString(); } else if (int.Parse(FiscalYear) < Rpt_Year) { string s = ""; for (int i = 1; i < Rpt_Prd; i++) { s += strPrd + i.ToString() + "+"; } s += strPrd + Rpt_Prd.ToString(); strPrd = s; } else if (int.Parse(FiscalYear) == Rpt_Year) { if (int.Parse(PeriodNumber) >= Rpt_Prd - 1) { strPrd += Rpt_Prd.ToString(); } else { string s = ""; for (int i = int.Parse(PeriodNumber); i < Rpt_Prd; i++) { s += strPrd + i.ToString() + "+"; } s += strPrd + Rpt_Prd.ToString(); strPrd = s; } } } break; case "DAY": if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Source"].ToString() != "BGT") { string PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID" + Rpt_Prd + " From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year); Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodEndDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID)); End_Date = Start_Date; } break; case "BEG": if (strPrd == "Period") { strPrd = "0"; } else { strPrd = "BeginningBalance"; } break; } DataTable DT = new DataTable(); if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Range"].ToString() != "BEG" && dtCols.Rows[col]["Col_Source"].ToString() != "BGT") { System.Collections.ArrayList ItemsNames = new System.Collections.ArrayList(); System.Collections.ArrayList ItemsValues = new System.Collections.ArrayList(); ItemsNames.Add("Rpt_Fdate"); ItemsValues.Add(Start_Date.Date); ItemsNames.Add("Rpt_Tdate"); ItemsValues.Add(End_Date.Date); DT = DataClass.SelectRecord("SELECT { fn IFNULL(SUM(GLT.AmountLC), 0) } AS Amt FROM dbo.Batch AS B INNER JOIN dbo.GLTransactions AS GLT ON B.BatchNo = GLT.BatchNo WHERE (B.BatchStat = 'P') AND (Convert(int,replace(GLT.GLAccount,'-','')) Between " + Accounts + ") AND (B.BatchDate Between @Rpt_Fdate AND @Rpt_Tdate ) AND (B.BatchJNL IN (Select JournalCodeID From G_Report_Journal Where Rpt_Code = '" + Rpt_Code + "'))", ItemsNames, ItemsValues); } else { DT = DataClass.RetrieveData("SELECT { fn IFNULL(Sum(" + strPrd.Trim() + "), 0) } AS Amt FROM dbo." + str.Trim() + " WHERE (Convert(int,replace(AccountNumber,'-','')) Between " + Accounts + ") AND ({ fn IFNULL(FiscalYear, 0) } = " + Rpt_Year.ToString() + ") "); } if (DT.Rows.Count > 0) { double DAccAmt = 0; if (Divide_Acc.Trim() != "") { DataTable DT2 = new DataTable(); if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Range"].ToString() != "BEG" && dtCols.Rows[col]["Col_Source"].ToString() != "BGT") { System.Collections.ArrayList ItemsNames = new System.Collections.ArrayList(); System.Collections.ArrayList ItemsValues = new System.Collections.ArrayList(); ItemsNames.Add("Rpt_Fdate"); ItemsValues.Add(Start_Date.Date); ItemsNames.Add("Rpt_Tdate"); ItemsValues.Add(End_Date.Date); DT2 = DataClass.SelectRecord("SELECT { fn IFNULL(SUM(GLT.AmountLC), 0) } AS Amt FROM dbo.Batch AS B INNER JOIN dbo.GLTransactions AS GLT ON B.BatchNo = GLT.BatchNo WHERE (B.BatchStat = 'P') AND (Convert(int,replace(GLT.GLAccount,'-','')) Between " + Divide_Acc + ") AND (B.BatchDate Between @Rpt_Fdate AND @Rpt_Tdate ) AND (B.BatchJNL IN (Select JournalCodeID From G_Report_Journal Where Rpt_Code = '" + Rpt_Code + "'))", ItemsNames, ItemsValues); } else { DT2 = DataClass.RetrieveData("SELECT { fn IFNULL(Sum(" + strPrd.Trim() + "), 0) } AS Amt FROM dbo." + str.Trim() + " WHERE (Convert(int,replace(AccountNumber,'-','')) Between " + Divide_Acc + ") AND ({ fn IFNULL(FiscalYear, 0) } = " + Rpt_Year.ToString() + ") "); } if (DT2.Rows.Count > 0) { double.TryParse(DT2.Rows[0]["Amt"].ToString(), out DAccAmt); } } int Div_Day = 0; switch (dtRows.Rows[row]["Row_Divide_By_Amt"].ToString().Trim()) { case "Day": string PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID" + Rpt_Prd + " From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year); int.TryParse(DataClass.ReturnRecordNameByID("Select datediff (d, PeriodStartDate, PeriodEndDate) + 1 From GLFiscalPeriod WHERE PeriodID = " + PeriodID), out Div_Day); break; default: int.TryParse(dtRows.Rows[row]["Row_Divide_By_Amt"].ToString().Trim(), out Div_Day); break; } string sval = ""; if (DAccAmt != 0) { sval = "=" + DT.Rows[0]["Amt"].ToString() + "/" + DAccAmt.ToString(); } if (Div_Day != 0) { if (sval.Contains("=")) { sval += "/" + Div_Day.ToString(); } else { sval = "=" + DT.Rows[0]["Amt"].ToString() + "/" + Div_Day.ToString(); } } if (sval.Trim() != "") { rr[1, 1] = sval.Trim(); } else { rr[1, 1] = double.Parse(DT.Rows[0]["Amt"].ToString()); } } } } else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "TOT") { string RTF = dtRows.Rows[row]["Row_TOT_Formula"].ToString(); RTF = RTF.Replace("00", ""); RTF = RTF.Replace("A", dtCols.Rows[col]["Col_No"].ToString()); rr[1, 1] = "=" + RTF.Trim(); } } else if (dtCols.Rows[col]["Col_Type"].ToString() == "DB" || dtCols.Rows[col]["Col_Type"].ToString() == "CR") { if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "DTL") { string BEG = ""; if (dtCols.Rows[col]["Col_Period"].ToString().Substring(0, 3) == "PRD") { Rpt_Prd = int.Parse(dtCols.Rows[col]["Col_Period"].ToString().Remove(0, 3)); } if (dtCols.Rows[col]["Col_Source"].ToString() == "LST") { Rpt_Year--; } if (!DataClass.isExsist("*", "FiscalYear = " + Rpt_Year, "GLFiscalPeriodSetup")) { rr[1, 1] = 0; } else { string PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID" + Rpt_Prd + " From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year); DateTime Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodStartDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID)); DateTime End_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodEndDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID)); switch (dtCols.Rows[col]["Col_Range"].ToString()) { case "YTD": PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID1 From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year); Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodStartDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID)); break; case "DAY": Start_Date = Start_Date.AddDays(Start_Date.Day * -1).AddDays(DateTime.Now.Day); End_Date = Start_Date; break; case "BEG": End_Date = Start_Date.AddDays(-1); PeriodID = DataClass.ReturnRecordNameByID("Select PeriodID1 From GLFiscalPeriodSetup WHERE FiscalYear = " + Rpt_Year); Start_Date = DateTime.Parse(DataClass.ReturnRecordNameByID("Select PeriodStartDate From GLFiscalPeriod WHERE PeriodID = " + PeriodID)); BEG = " + " + DataClass.ReturnRecordNameByID("SELECT { fn IFNULL(Sum(BeginningBalance), 0) } AS Amt FROM dbo.GLTotals WHERE (Convert(int,replace(AccountNumber,'-','')) Between " + Accounts + ") AND ({ fn IFNULL(FiscalYear, 0) } = " + Rpt_Year.ToString() + ") "); break; } System.Collections.ArrayList ItemsNames = new System.Collections.ArrayList(); System.Collections.ArrayList ItemsValues = new System.Collections.ArrayList(); ItemsNames.Add("Rpt_Fdate"); ItemsValues.Add(Start_Date.Date); ItemsNames.Add("Rpt_Tdate"); ItemsValues.Add(End_Date.Date); string type = ">="; if (dtCols.Rows[col]["Col_Type"].ToString() == "CR") { type = "<"; } DataTable DT = new DataTable(); if ((bool)dtCols.Rows[col]["Daily_Rev"] && dtCols.Rows[col]["Col_Range"].ToString() != "BEG" && dtCols.Rows[col]["Col_Source"].ToString() != "BGT") { DT = DataClass.SelectRecord("SELECT { fn IFNULL(SUM(GLT.AmountLC), 0) } " + BEG + " AS Amt FROM dbo.Batch AS B INNER JOIN dbo.GLTransactions AS GLT ON B.BatchNo = GLT.BatchNo WHERE (B.BatchStat = 'P') AND (GLT.AmountLC " + type + " 0) AND (Convert(int,replace(GLT.GLAccount,'-','')) Between " + Accounts + ") AND (B.BatchDate Between @Rpt_Fdate AND @Rpt_Tdate ) AND (B.BatchJNL IN (Select JournalCodeID From G_Report_Journal Where Rpt_Code = '" + Rpt_Code + "'))", ItemsNames, ItemsValues); } else { DT = DataClass.SelectRecord("SELECT { fn IFNULL(SUM(GLT.AmountLC), 0) } " + BEG + " AS Amt FROM dbo.Batch AS B INNER JOIN dbo.GLTransactions AS GLT ON B.BatchNo = GLT.BatchNo WHERE (B.BatchStat = 'P') AND (GLT.AmountLC " + type + " 0) AND (Convert(int,replace(GLT.GLAccount,'-','')) Between " + Accounts + ") AND (B.BatchDate Between @Rpt_Fdate AND @Rpt_Tdate )", ItemsNames, ItemsValues); } if (DT.Rows.Count > 0) { rr[1, 1] = double.Parse(DT.Rows[0]["Amt"].ToString()); } } } else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "TOT") { string RTF = dtRows.Rows[row]["Row_TOT_Formula"].ToString(); RTF = RTF.Replace("00", ""); RTF = RTF.Replace("A", dtCols.Rows[col]["Col_No"].ToString()); rr[1, 1] = "=" + RTF.Trim(); } } else if (dtCols.Rows[col]["Col_Type"].ToString() == "PCT") { if (dtCols.Rows[col]["Col_Prec_Source"].ToString().Trim() != "" && dtRows.Rows[row]["Row_Prec_Row"].ToString().Trim() != "") { string CPS = dtCols.Rows[col]["Col_Prec_Source"].ToString(); string RPR = (int.Parse(dtRows.Rows[row]["Row_Prec_Row"].ToString()) / 100).ToString(); string RN = (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(); rr[1, 1] = "=IF(" + CPS + "" + RPR + "=0, 0, " + CPS + "" + RN + "/" + CPS + "" + RPR + "*100)"; } } else if (dtCols.Rows[col]["Col_Type"].ToString() == "CALC") { if (dtCols.Rows[col]["Col_Calc_Formula"].ToString().Trim() != "") { string CCF = dtCols.Rows[col]["Col_Calc_Formula"].ToString(); string RN = (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(); CCF = CCF.Replace("0", RN); if (dtCols.Rows[col]["Col_Calc_Divide"].ToString().Trim() != "") { string CCD = dtCols.Rows[col]["Col_Calc_Divide"].ToString(); CCD = CCD.Replace("0", RN); CCF = "IF((" + CCD + ")=0,0,(" + CCF + "/ (" + CCD + ")))"; } rr[1, 1] = "=" + CCF.Trim(); } } else if (dtCols.Rows[col]["Col_Type"].ToString() == "DESC") { rr[1, 1] = dtRows.Rows[row]["Row_Desc"].ToString(); rr.HorizontalAlignment = 3; rr.VerticalAlignment = 3; } } } else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "DESC") { rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString()); rr.MergeCells = true; rr[1, 1] = dtRows.Rows[row]["Row_Desc"].ToString(); rr.HorizontalAlignment = 3; rr.VerticalAlignment = 3; } else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "HDL") { rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString()); rr.MergeCells = true; rr[1, 1] = dtRows.Rows[row]["Row_Desc"].ToString(); rr.HorizontalAlignment = 1; rr.VerticalAlignment = 3; } else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "HDC") { rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString()); rr.MergeCells = true; rr[1, 1] = dtRows.Rows[row]["Row_Desc"].ToString(); rr.HorizontalAlignment = 3; rr.VerticalAlignment = 3; } else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "HDR") { rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString()); rr.MergeCells = true; rr[1, 1] = dtRows.Rows[row]["Row_Desc"].ToString(); rr.HorizontalAlignment = 4; rr.VerticalAlignment = 3; } else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "---") { rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString()); rr.MergeCells = true; rr.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; rr.RowHeight = 2; } else if (dtRows.Rows[row]["Row_Type"].ToString().Trim() == "===") { rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), letter + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString()); rr.MergeCells = true; rr.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDouble; rr.RowHeight = 2; } } for (int col = 0; col < dtCols.Rows.Count; col++) { rr = mywsh.get_Range(dtCols.Rows[col]["Col_No"].ToString() + "1", dtCols.Rows[col]["Col_No"].ToString() + dtRows.Rows.Count.ToString()); double i = double.Parse(rr.ColumnWidth.ToString()); double.TryParse(dtCols.Rows[col]["Col_Width"].ToString(), out i); if ((bool)dtCols.Rows[col]["Col_Sup"]) { rr.ColumnWidth = 0; } else { rr.ColumnWidth = i; } } for (int row = 0; row < dtRows.Rows.Count; row++) { rr = mywsh.get_Range("A" + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString(), dtCols.Rows[dtCols.Rows.Count - 1]["Col_No"].ToString() + (int.Parse(dtRows.Rows[row]["Row_No"].ToString()) / 100).ToString()); rr.NumberFormat = dtRows.Rows[row]["Row_Format"].ToString().Trim(); if ((bool)dtRows.Rows[row]["Row_Sup"]) { rr.RowHeight = 0; } } rr = mywsh.get_Range("A1", letter + "1"); rr.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, null); rr.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, null); rr.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, null); for (int col = 0; col < dtCols.Rows.Count; col++) { rr = mywsh.get_Range(dtCols.Rows[col]["Col_No"].ToString() + "1", dtCols.Rows[col]["Col_No"].ToString() + "2"); switch (dtCols.Rows[col]["Col_Type"].ToString()) { case "PCT": rr[1, 1] = ""; rr[2, 1] = "%"; break; case "CALC": rr[1, 1] = ""; rr[2, 1] = dtCols.Rows[col]["Col_Header"].ToString(); break; case "DB": rr[1, 1] = dtCols.Rows[col]["Col_Range"].ToString() + "(" + "Debit)"; rr[2, 1] = dtCols.Rows[col]["Col_Header"].ToString(); break; case "CR": rr[1, 1] = dtCols.Rows[col]["Col_Range"].ToString() + "(" + "Credits)"; rr[2, 1] = dtCols.Rows[col]["Col_Header"].ToString(); break; case "GL": rr[1, 1] = dtCols.Rows[col]["Col_Range"].ToString(); rr[2, 1] = dtCols.Rows[col]["Col_Header"].ToString(); break; } rr.HorizontalAlignment = 3; rr.VerticalAlignment = 3; } rr = mywsh.get_Range("A3", letter + "3"); rr.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; rr.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; rr.MergeCells = true; rr[1, 1] = "Period " + Rpt_Prd.ToString() + "/" + Rpt_Year.ToString(); rr.HorizontalAlignment = 3; rr.VerticalAlignment = 3; rr.Font.Bold = true; //rr.Columns.AutoFit(); myexl.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; Function.StateThrid = ""; Thread.CurrentThread.Abort(); } }
/// <summary> /// 打印 /// </summary> private void printInfo() { Object oMissing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application m_objExcel = null; Microsoft.Office.Interop.Excel._Workbook m_objBook = null; Microsoft.Office.Interop.Excel.Sheets m_objSheets = null; Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null; Microsoft.Office.Interop.Excel.Range m_objRange = null; try { m_objExcel = new Microsoft.Office.Interop.Excel.Application(); DirectoryInfo Dir = new DirectoryInfo("."); m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Register.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1)); // 号牌种类 m_objRange = m_objSheet.get_Range("D11", oMissing); m_objRange.Value = this.category.Text; // 号牌号码 m_objRange = m_objSheet.get_Range("H11", oMissing); m_objRange.Value = "辽B" + this.license.Text; // 品牌型号 m_objRange = m_objSheet.get_Range("D12", oMissing); m_objRange.Value = this.brand.Text; //车辆识别代号 m_objRange = m_objSheet.get_Range("H12", oMissing); m_objRange.Value = this.vin.Text; // 机动车所有人姓名/名称 m_objRange = m_objSheet.get_Range("E3", oMissing); m_objRange.Value = this.ownerName.Text; // 机动车所有人邮寄地址 m_objRange = m_objSheet.get_Range("E4", oMissing); m_objRange.Value = this.ownerAddress.Text; // 机动车所有人邮政编码 m_objRange = m_objSheet.get_Range("I3", oMissing); m_objRange.Value = this.ownerPostcode.Text; // 机动车所有人固定电话 m_objRange = m_objSheet.get_Range("I5", oMissing); m_objRange.Value = this.ownerPhone.Text; // 机动车所有人移动电话 m_objRange = m_objSheet.get_Range("E5", oMissing); m_objRange.Value = this.ownerMobile.Text; // 省(自治县、直辖市) m_objRange = m_objSheet.get_Range("D10", oMissing); m_objRange.Value = " " + this.province.Text + " " + this.department.Text; // 代理人姓名、名称 m_objRange = m_objSheet.get_Range("E6", oMissing); m_objRange.Value = this.agentName.Text; // 代理人联系电话 m_objRange = m_objSheet.get_Range("H6", oMissing); m_objRange.Value = this.agentPhone.Text; // 办理日期 m_objRange = m_objSheet.get_Range("F18", oMissing); m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day; m_objExcel.DisplayAlerts = false; m_objBook.Save(); m_objSheet.PrintOut(); } catch (Exception ex) { // 输出异常信息 MessageBox.Show("打印失败,异常信息为:" + ex.Message); } finally { if (m_objBook != null) { m_objBook.Close(oMissing, oMissing, oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); } if (m_objExcel != null) { m_objExcel.Workbooks.Close(); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); } m_objBook = null; m_objExcel = null; GC.Collect(); } }
private void buttonExcel_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); // Creates a new Excel Application excelApp.Visible = true; // Makes Excel visible to the user. // The following line adds a new workbook Microsoft.Office.Interop.Excel.Workbook newWorkbook = excelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); // The following code opens an existing workbook string workbookPath = Application.StartupPath + "\\template.xls"; // Add your own path here Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); // The following gets the Worksheets collection Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets; // The following gets Sheet1 for editing string currentSheet = "Бланк"; Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet); // The following gets cell A1 for editing //Microsoft.Office.Interop.Excel.Range excelCell = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A1", "A1"); // The following sets cell A1's value to "Hi There" //excelCell.Value2 = "Hi There"; _Ekskursovody a; int n = queueAccounts.Count; for (int i = 0; i < n; i++) { a = (_Ekskursovody)queueAccounts.Dequeue(); queueAccounts.Enqueue(a); if (a.id == m_journal.account_id.ToString()) { Microsoft.Office.Interop.Excel.Range excelCellAccount = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("C8", "C8"); excelCellAccount.Value2 = a.name; break; } } Microsoft.Office.Interop.Excel.Range excelCellID = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("D11", "D11"); excelCellID.Value2 = m_journal.id.ToString(); Microsoft.Office.Interop.Excel.Range excelCellStudent = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("E14", "F14"); excelCellStudent.Value2 = m_journal.student; _PaymentNames pn; n = queuePaymentNames.Count; for (int i = 0; i < n; i++) { pn = (_PaymentNames)queuePaymentNames.Dequeue(); queuePaymentNames.Enqueue(pn); if (pn.id == m_journal.payment_name_id) { Microsoft.Office.Interop.Excel.Range excelCellPaymentName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B19", "C19"); excelCellPaymentName.Value2 = pn.name; break; } } _Semester s; n = queueSemesters.Count; for (int i = 0; i < n; i++) { s = (_Semester)queueSemesters.Dequeue(); queueSemesters.Enqueue(s); if (s.id == m_journal.semester) { Microsoft.Office.Interop.Excel.Range excelCellSemester = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B20", "C20"); excelCellSemester.Value2 = s.name; break; } ; } _Pdv p; n = queuePdv.Count; for (int i = 0; i < n; i++) { p = (_Pdv)queuePdv.Dequeue(); queuePdv.Enqueue(p); if (p.id == m_journal.pdv) { Microsoft.Office.Interop.Excel.Range excelCellPdv = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("E22", "E22"); excelCellPdv.Value2 = p.name; break; } ; } _EduYears ey; n = queueEduYears.Count; for (int i = 0; i < n; i++) { ey = (_EduYears)queueEduYears.Dequeue(); queueEduYears.Enqueue(ey); if (ey.id.ToString() == m_journal.edu_year_id.ToString()) { Microsoft.Office.Interop.Excel.Range excelCellSemester = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("B21", "C21"); excelCellSemester.Value2 = ey.name; break; } ; } Microsoft.Office.Interop.Excel.Range excelCellSum = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("F21", "F21"); excelCellSum.Value2 = m_journal.sum; String date = ""; if (DateTime.Parse(m_journal.date).Day < 10) { date += "0"; } date += DateTime.Parse(m_journal.date).Day; date += "/"; if (DateTime.Parse(m_journal.date).Month < 10) { date += "0"; } date += DateTime.Parse(m_journal.date).Month; date += "/"; date += DateTime.Parse(m_journal.date).Year; Microsoft.Office.Interop.Excel.Range excelCellDate = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("F11", "F11"); excelCellDate.Value2 = date; }
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++; } }
// 完全读取 excel /// <summary> /// 解析Excel,返回DataTable /// </summary> /// <param name="fileName"></param> /// <returns></returns> public System.Data.DataTable ImpExcel(string fileName) { System.Data.DataTable dt = new System.Data.DataTable(); dt.TableName = excelsheetname.ToString(); try { //Microsoft.Office.Interop.Excel.Application app; //Microsoft.Office.Interop.Excel.Workbook wbs; // Microsoft.Office.Interop.Excel.Worksheet ws; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); //新建文件 // 多数使用缺省值 (除了 read-only我们设置它为 true) Microsoft.Office.Interop.Excel.Workbook wbs = app.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null); // 取得工作簿(workbook)中表单的集合(sheets) Microsoft.Office.Interop.Excel.Sheets wss = wbs.Worksheets; // 取得表单集合中唯一的一个表(worksheet) Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1); //app = new Microsoft.Office.Interop.Excel.Application(); //wbs = app.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null); //ws = (Microsoft.Office.Interop.Excel.Worksheet)app.Worksheets.get_Item(1); //int a = ws.Rows.Count; int a = ws.UsedRange.Cells.Rows.Count + 2; //int b = ws.Columns.Count; int b = 20; string name = ws.Name; for (int i = 1; i < a; i++) { if (i == 1) { for (int j = 1; j <= b; j++) { Microsoft.Office.Interop.Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]); range.Select(); string columnnames = app.ActiveCell.Text.ToString(); dt.Columns.Add(columnnames, typeof(string)); } } else { DataRow dr = dt.NewRow(); for (int j = 1; j <= b; j++) { Microsoft.Office.Interop.Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]); range.Select(); int rowco = j - 1; string cellc = app.ActiveCell.Text.ToString();; dr[rowco] = cellc; } dt.Rows.Add(dr); } } System.Runtime.InteropServices.Marshal.ReleaseComObject(ws); ws = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(wss); wss = null; wbs.Close(true, Type.Missing, Type.Missing); //(Microsoft.Office.Interop.Excel.Worksheet)wbs.Close(true, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs); wbs = null; app.Application.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); int generation = System.GC.GetGeneration(app); Kill(app); app = null; System.GC.Collect(generation); return(dt); } catch (Exception ex) { // MessageBox.Show("数据绑定Excel失败! 失败原因:Excel格式不正确!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return(dt); } finally { GC.Collect(); //垃圾回收 GC.WaitForPendingFinalizers(); } }
private static void exportexcel(System.Data.DataTable dt, string filename, string charttitle, bool tableflag, bool bl) { //System.Data.DataTable dt = new System.Data.DataTable(); if (dt == null) { return; } Microsoft.Office.Interop.Excel._Workbook oWB; Microsoft.Office.Interop.Excel.Series oSeries; //Microsoft.Office.Interop.Excel.Range oResizeRange; Microsoft.Office.Interop.Excel._Chart oChart; //String sMsg; //int iNumQtrs; GC.Collect();//系统的垃圾回收 //string filename = @"C:\Documents and Settings\tongxl\桌面\nnn.xls"; //Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(filename); Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(true); if (ep == null) { MsgBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } ep.Visible = true; Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets; Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);// [System.Type.Missing];//.get.get_Item("xx"); ws.UsedRange.Select(); ws.UsedRange.Copy(System.Type.Missing); // wb.Charts.Add(System.Type.Missing, System.Type.Missing, 1, System.Type.Missing); int rowIndex = 1; int colIndex = 1; foreach (DataColumn col in dt.Columns) { ws.Cells[rowIndex, colIndex++] = col.ColumnName; } for (int drvIndex = 0; drvIndex < dt.Rows.Count; drvIndex++) { DataRow row = dt.Rows[drvIndex]; colIndex = 1; foreach (DataColumn col in dt.Columns) { ws.Cells[drvIndex + 2, colIndex] = row[col.ColumnName].ToString(); colIndex++; } } oWB = (Microsoft.Office.Interop.Excel._Workbook)ws.Parent; oChart = (Microsoft.Office.Interop.Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); int rcount = dt.Rows.Count; int ccount = dt.Columns.Count; oChart.ChartWizard(ws.get_Range(ws.Cells[1, 1], ws.Cells[rcount + 2, ccount + 2]), Microsoft.Office.Interop.Excel.XlChartType.xlLine, Missing.Value, Microsoft.Office.Interop.Excel.XlRowCol.xlRows, 1, true, true, charttitle, Missing.Value, Missing.Value, Missing.Value); // oSeries = (Microsoft.Office.Interop.Excel.Series)oChart.SeriesCollection(1); //string str = String.Empty; //for (int I = 1; I < 15; I++) //{ // str += I.ToString() + "\t"; //} //try { oSeries.XValues = str; } //catch { } // oSeries.HasDataLabels = true; // string charttitle ,bool tableflag ,bool bl) if (tableflag == true) { oChart.HasDataTable = true; } else { oChart.HasDataTable = false; } oChart.PlotVisibleOnly = false; // Microsoft.Office.Interop.Excel.Axis axis = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes( Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary); //axis.HasTitle = true; //axis.AxisTitle.Text = "Sales Figures"; // axis.HasMinorGridlines=true; Microsoft.Office.Interop.Excel.Axis ax = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes( Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary); //ax.HasTitle = true; //ax.AxisTitle.Text = "Sales Figures"; ax.HasMajorGridlines = true; // string filename = @"C:\Documents and Settings\tongxl\桌面\ccsb.xls"; // ws.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); try { wb.Saved = true; wb.SaveCopyAs(filename); } catch (Exception ex) { MsgBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); } ep.Quit(); GC.Collect();//强行销毁 }
public void Export(string fileName) { if (values.Count == 0) { throw new Exception("Данные не заданы"); } var excel = new Microsoft.Office.Interop.Excel.Application(); try { if (File.Exists(fileName)) { excel.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, Type.Missing, Type.Missing); } else { excel.SheetsInNewWorkbook = 2; excel.Workbooks.Add(Type.Missing); excel.Workbooks[1].SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } var workbook = excel.Workbooks[1]; Microsoft.Office.Interop.Excel.Sheets excelsheets = workbook.Worksheets; var excelworksheetData = (Microsoft.Office.Interop.Excel.Worksheet)excelsheets.get_Item(1); excelworksheetData.Cells.Clear(); excelworksheetData.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape; excelworksheetData.PageSetup.CenterHorizontally = true; excelworksheetData.PageSetup.CenterVertically = true; for (var i = 1; i <= values.Count; i++) { if (i <= captions.Count) { excelworksheetData.Range["A" + i].Value = captions[i - 1]; } excelworksheetData.Range["B" + i].Value = values[i - 1]; } excelworksheetData.Name = "Data"; var excelworksheetDia = (Microsoft.Office.Interop.Excel.Worksheet)excelsheets.get_Item(2); excelworksheetDia.Name = "Diagram"; var charts = excelworksheetDia.ChartObjects() as Microsoft.Office.Interop.Excel.ChartObjects; var chartObject = charts.Add(60, 10, 300, 300) as Microsoft.Office.Interop.Excel.ChartObject; var chart = chartObject.Chart; chart.ChartWizard(excelworksheetData.Range["A1", $"B{values.Count}"], Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn, "New Chart"); excel.Workbooks[1].Save(); } catch (Exception e) { throw e; } finally { excel.Quit(); } releaseObject(excel); }
/// <summary> /// Extension method to write list data to Microsoft.Office.Interop.Excel File. /// </summary> /// <typeparam name="T">Ganeric list</typeparam> /// <param name="list"></param> /// <param name="PathToSave">Path to save file.</param> public static void ToExcel <T>(this List <T> list, string PathToSave) { #region Declarations if (string.IsNullOrEmpty(PathToSave)) { throw new Exception("Invalid file path."); } else if (PathToSave.ToLower().Contains("") == false) { throw new Exception("Invalid file path."); } if (list == null) { throw new Exception("No data to export."); } Microsoft.Office.Interop.Excel.Application excelApp = null; Microsoft.Office.Interop.Excel.Workbooks books = null; Microsoft.Office.Interop.Excel._Workbook book = null; Microsoft.Office.Interop.Excel.Sheets sheets = null; Microsoft.Office.Interop.Excel._Worksheet sheet = null; Microsoft.Office.Interop.Excel.Range range = null; Microsoft.Office.Interop.Excel.Font font = null; // Optional argument variable object optionalValue = Missing.Value; string strHeaderStart = "A2"; string strDataStart = "A3"; #endregion #region Processing try { #region Init Microsoft.Office.Interop.Excel app. 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)); #endregion #region Creating Header Dictionary <string, string> objHeaders = new Dictionary <string, string>(); PropertyInfo[] headerInfo = typeof(T).GetProperties(); foreach (var property in headerInfo) { var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false) .Cast <DisplayNameAttribute>().FirstOrDefault(); objHeaders.Add(property.Name, attribute == null ? property.Name : attribute.DisplayName); } range = sheet.get_Range(strHeaderStart, optionalValue); range = range.get_Resize(1, objHeaders.Count); range.set_Value(optionalValue, objHeaders.Values.ToArray()); range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing); font = range.Font; font.Bold = true; range.Interior.Color = Color.LightGray.ToArgb(); #endregion #region Writing data to cell int count = list.Count; object[,] objData = new object[count, objHeaders.Count]; for (int j = 0; j < count; j++) { var item = list[j]; int i = 0; foreach (KeyValuePair <string, string> entry in objHeaders) { var y = typeof(T).InvokeMember(entry.Key.ToString(), BindingFlags.GetProperty, null, item, null); objData[j, i++] = (y == null) ? "" : y.ToString(); } } range = sheet.get_Range(strDataStart, optionalValue); range = range.get_Resize(count, objHeaders.Count); range.set_Value(optionalValue, objData); range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing); range = sheet.get_Range(strHeaderStart, optionalValue); range = range.get_Resize(count + 1, objHeaders.Count); range.Columns.AutoFit(); #endregion #region Saving data and Opening Microsoft.Office.Interop.Excel file. if (string.IsNullOrEmpty(PathToSave) == false) { book.SaveCopyAs(PathToSave); } excelApp.Visible = true; #endregion #region Release objects try { if (sheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); } sheet = null; if (sheets != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); } sheets = null; if (book != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(book); } book = null; if (books != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(books); } books = null; if (excelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); } excelApp = null; } catch (Exception ex) { sheet = null; sheets = null; book = null; books = null; excelApp = null; } finally { GC.Collect(); } #endregion } catch (Exception ex) { throw ex; } #endregion }
private void buttonOst_Click(object sender, EventArgs e) { //массив0 выдано refreshOst(); int[,] arr = new int[listViewBilety2.Items.Count, listViewBilety2.Columns.Count]; for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++) { for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++) { arr[x, y] = int.Parse(listViewBilety2.Items[x].SubItems[y].Text.Trim()); } } //массив1 сдано refreshOst1(); int[,] arr1 = new int[listViewBilety2.Items.Count, listViewBilety2.Columns.Count]; for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++) { for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++) { arr1[x, y] = int.Parse(listViewBilety2.Items[x].SubItems[y].Text.Trim()); } } //массив2 названия refreshOst2(); string[,] b = new string[listViewBilety2.Items.Count, listViewBilety2.Columns.Count]; for (int x = 0, maxX = listViewBilety2.Items.Count; x < maxX; x++) { for (int y = 0, maxY = listViewBilety2.Columns.Count; y < maxY; y++) { b[x, y] = listViewBilety2.Items[x].SubItems[y].Text; } } //refreshBilety2(); listViewBilety2.Items.Clear(); listViewBilety2.Columns.Clear(); List <int> ARR = new List <int>(); List <int> ARR1 = new List <int>(); List <int> ARR2 = new List <int>(); List <int> ARR3 = new List <int>(); List <int> unique = new List <int>(); List <object> unique2 = new List <object>(); //диапазоны из нач/кон значений выдано for (int x = 0; x <= ((arr.Length / 2) - 1); x++) { for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++) { ARR.Add(i); } } //диапазоны из нач/кон значений сдано for (int x = 0; x <= ((arr1.Length / 2) - 1); x++) { for (int i = arr1[x, 0], maxi = arr1[x, 1]; i <= maxi; i++) { ARR1.Add(i); } } //удаление того, что есть в журнале, но нет в билетах for (int x = 0; x < ARR.Count; x++) { for (int y = 0; y < ARR1.Count; y++) { if (ARR[x].Equals(ARR1[y])) { ARR3.Add(ARR[x]); } } } //объединение списков ARR2.AddRange(ARR.ToArray()); ARR2.AddRange(ARR3.ToArray()); ARR2.Sort(); //удаление двойных for (int m = 0; m < ARR2.Count; m++) { for (int j = m + 1; j < ARR2.Count; j++) { if (ARR2[j].Equals(ARR2[m])) { ARR2.RemoveAt(j--); ARR2.RemoveAt(m); } } } //приведение к начальным for (int x = 0; x <= ((arr.Length / 2) - 1); x++) { for (int i = arr[x, 0], maxi = arr[x, 1]; i <= maxi; i++) { if (ARR2.Contains(i)) { unique.Add(x + 1); unique.Add(i); } } } //собственно диапазоны остатка for (int x = 0; x < unique.Count; x = x + 2) { int m; if (x == 0) { m = x + 1; } else { m = x - 2; } if (!unique[x].Equals(unique[m])) { unique2.Add(unique[x]); unique2.Add(unique[x + 1]); } int f; if (x < unique.Count - 2) { f = x + 2; } else { f = x - 1; } if (!unique[x].Equals(unique[f])) { unique2.Add(unique[x + 1]); for (int y = 0; y < b.Length / 3; y++) { if (unique[x + 1].Equals(int.Parse(b[y, 0]))) { unique2.Add(b[y, 1]); unique2.Add(b[y, 2]); } } } } //массив для екселя object[,] c = new object[unique2.Count, unique2.Count / 3]; for (int i = 0, k = 0; i < unique2.Count; i = i + 5, k++) { c[k, 0] = unique2[i]; } for (int i = 1, k = 0; i < unique2.Count; i = i + 5, k++) { c[k, 1] = unique2[i]; } for (int i = 2, k = 0; i < unique2.Count; i = i + 5, k++) { c[k, 2] = unique2[i]; } for (int i = 3, k = 0; i < unique2.Count; i = i + 5, k++) { c[k, 3] = unique2[i]; } for (int i = 4, k = 0; i < unique2.Count; i = i + 5, k++) { c[k, 4] = unique2[i]; } object[] a = unique2.ToArray(); Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.Visible = true; BringToFront(); string workbookPath = Application.StartupPath + "\\templates/bilety.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); Microsoft.Office.Interop.Excel.Range excelCellName = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.get_Range("A4", "E" + unique2.Count); excelCellName.Value2 = c; }
public static string multiSheetExcel(DataSet ds) { DataTable table100 = ds.Tables[0]; DataTable table50first = ds.Tables[1]; DataTable table50second = ds.Tables[2]; DataTable table50full = ds.Tables[3]; DataTable table5050 = ds.Tables[4]; DataTable tableCn = ds.Tables[5]; DataTable tableRmb = ds.Tables[6]; string filename = ""; Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets; for (int i = sheets.Count; i > 1; i--) { ((Microsoft.Office.Interop.Excel.Worksheet)sheets[i]).Delete(); } for (int i = 1; i <= 6; i++) { workbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i); } Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[1]; sheet1.Name = "100% Normal"; for (int i = 0; i < table100.Columns.Count; i++) { sheet1.Cells[1, i + 1] = table100.Columns[i].ColumnName; } for (int i = 0; i < table100.Rows.Count; i++) { for (int j = 0; j < table100.Columns.Count; j++) { sheet1.Cells[i + 2, j + 1] = table100.Rows[i][j].ToString(); } } Microsoft.Office.Interop.Excel.Worksheet sheet2 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[2]; sheet2.Name = "50% MPA 1st 50%"; for (int i = 0; i < table50first.Columns.Count; i++) { sheet2.Cells[1, i + 1] = table50first.Columns[i].ColumnName; } for (int i = 0; i < table50first.Rows.Count; i++) { for (int j = 0; j < table50first.Columns.Count; j++) { sheet2.Cells[i + 2, j + 1] = table50first.Rows[i][j].ToString(); } } Microsoft.Office.Interop.Excel.Worksheet sheet3 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[3]; sheet3.Name = "50% MPA 2nd 50%"; for (int i = 0; i < table50second.Columns.Count; i++) { sheet3.Cells[1, i + 1] = table50second.Columns[i].ColumnName; } for (int i = 0; i < table50second.Rows.Count; i++) { for (int j = 0; j < table50second.Columns.Count; j++) { sheet3.Cells[i + 2, j + 1] = table50second.Rows[i][j].ToString(); } } Microsoft.Office.Interop.Excel.Worksheet sheet4 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[4]; //Microsoft.Office.Interop.Excel.Worksheet sheet4 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets.Add(After: excelApp.Sheets[excelApp.Sheets.Count]); sheet4.Name = "50% MPA 100% 2pcs"; for (int i = 0; i < table50full.Columns.Count; i++) { sheet4.Cells[1, i + 1] = table50full.Columns[i].ColumnName; } for (int i = 0; i < table50full.Rows.Count; i++) { for (int j = 0; j < table50full.Columns.Count; j++) { sheet4.Cells[i + 2, j + 1] = table50full.Rows[i][j].ToString(); } } Microsoft.Office.Interop.Excel.Worksheet sheet5 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[5]; sheet5.Name = "50% MPA 100% 1+1 pcs"; for (int i = 0; i < table5050.Columns.Count; i++) { sheet5.Cells[1, i + 1] = table5050.Columns[i].ColumnName; } for (int i = 0; i < table5050.Rows.Count; i++) { for (int j = 0; j < table5050.Columns.Count; j++) { sheet5.Cells[i + 2, j + 1] = table5050.Rows[i][j].ToString(); } } Microsoft.Office.Interop.Excel.Worksheet sheet6 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[6]; sheet6.Name = "OEM=CN"; for (int i = 0; i < tableCn.Columns.Count; i++) { sheet6.Cells[1, i + 1] = tableCn.Columns[i].ColumnName; } for (int i = 0; i < tableCn.Rows.Count; i++) { for (int j = 0; j < tableCn.Columns.Count; j++) { sheet6.Cells[i + 2, j + 1] = tableCn.Rows[i][j].ToString(); } } Microsoft.Office.Interop.Excel.Worksheet sheet7 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[7]; sheet7.Name = "RMB"; for (int i = 0; i < tableRmb.Columns.Count; i++) { sheet7.Cells[1, i + 1] = tableRmb.Columns[i].ColumnName; } for (int i = 0; i < tableRmb.Rows.Count; i++) { for (int j = 0; j < tableRmb.Columns.Count; j++) { sheet7.Cells[i + 2, j + 1] = tableRmb.Rows[i][j].ToString(); } } SaveFileDialog sfd = new SaveFileDialog() { DefaultExt = "xls", Filter = "EXCEL Files (*.xls)|*.xls", FilterIndex = 1, FileName = DateTime.Today.ToString("yyyyMMdd") + "_ReceivedPO"// + year + month + day, }; if (sfd.ShowDialog() == DialogResult.OK) { try { filename = sfd.FileName; excelApp.ActiveWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); //excelApp.ActiveWorkbook.Saved = true; //excelApp.AlertBeforeOverwriting = false; excelApp.Quit(); MessageBox.Show("Record saved to " + filename); } catch (COMException ex) { MessageBox.Show(ex.Message + ex.StackTrace); Debug.WriteLine(ex.Message + ex.StackTrace); } } return(filename); }
static void ExportToExcel(DataTable dataAdded, DataTable dataModified) { Console.WriteLine("Writing to Excel file"); string path = GetOutputFile(); oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = false; 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; //Write to New Contribution mWSheet = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("GitHub New Contribution"); Microsoft.Office.Interop.Excel.Range range = mWSheet.UsedRange; int rowCount = range.Rows.Count; int index = 1; foreach (DataRow item in dataAdded.Rows) { mWSheet.Cells[rowCount + index, 1] = item["StartDate"]; mWSheet.Cells[rowCount + index, 2] = item["EndDate"]; mWSheet.Cells[rowCount + index, 3] = item["GitUser"]; mWSheet.Cells[rowCount + index, 4] = item["AccountType"]; mWSheet.Cells[rowCount + index, 5] = item["TotalContribution"]; mWSheet.Cells[rowCount + index, 6] = item["DataConnectors"]; mWSheet.Cells[rowCount + index, 7] = item["Workbooks"]; mWSheet.Cells[rowCount + index, 8] = item["Playbooks"]; mWSheet.Cells[rowCount + index, 9] = item["Exploration Queries"]; mWSheet.Cells[rowCount + index, 10] = item["Hunting Queries"]; mWSheet.Cells[rowCount + index, 11] = item["Sample Data"]; mWSheet.Cells[rowCount + index, 12] = item["Tools"]; mWSheet.Cells[rowCount + index, 13] = item["Detections"]; mWSheet.Cells[rowCount + index, 14] = item["Notebooks @ efbace2"]; index++; } //Write to Update Contribution mWSheet = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("GitHub Update Contribution"); range = mWSheet.UsedRange; rowCount = range.Rows.Count; index = 1; foreach (DataRow item in dataModified.Rows) { mWSheet.Cells[rowCount + index, 1] = item["StartDate"]; mWSheet.Cells[rowCount + index, 2] = item["EndDate"]; mWSheet.Cells[rowCount + index, 3] = item["GitUser"]; mWSheet.Cells[rowCount + index, 4] = item["AccountType"]; mWSheet.Cells[rowCount + index, 5] = item["TotalContribution"]; mWSheet.Cells[rowCount + index, 6] = item["DataConnectors"]; mWSheet.Cells[rowCount + index, 7] = item["Workbooks"]; mWSheet.Cells[rowCount + index, 8] = item["Playbooks"]; mWSheet.Cells[rowCount + index, 9] = item["Exploration Queries"]; mWSheet.Cells[rowCount + index, 10] = item["Hunting Queries"]; mWSheet.Cells[rowCount + index, 11] = item["Sample Data"]; mWSheet.Cells[rowCount + index, 12] = item["Tools"]; mWSheet.Cells[rowCount + index, 13] = item["Detections"]; mWSheet.Cells[rowCount + index, 14] = item["Notebooks @ efbace2"]; index++; } mWorkBook.Save(); mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value); mWSheet = null; mWorkBook = null; oXL.Quit(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); Console.WriteLine("Excel file has been save at location: " + Environment.NewLine + path); }
//写EXCEL public void saveExcel(string savepath, DataSet dstemp) { string filepath = Server.MapPath("Template/Template.xls"); Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application(); //新建文件 // 多数使用缺省值 (除了 read-only我们设置它为 true) Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(filepath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null); // 取得工作簿(workbook)中表单的集合(sheets) Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets; // 取得表单集合中唯一的一个表(worksheet) if (ExcelObj == null) { //MessageBox.Show("无法启动Excel,可能您的电脑未安装Excel"); return; } try { for (int i = 0; i < dstemp.Tables.Count; i++) { string dtempname = dstemp.Tables[i].TableName; int rowIndex = 1; int colIndex = 0; int sizeofsheet = sheets.Count; Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(dtempname); string a = worksheet.Name; int countrow = dstemp.Tables[dtempname].Rows.Count; int countcolumn = 0; if (dtempname == "主管碰头会议决议事项督办清单") { countcolumn = 9; } else { countcolumn = 8; } var RowAll = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[countrow + 1, countcolumn]); RowAll.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; System.Data.DataTable table = dstemp.Tables[dtempname]; foreach (DataColumn col in table.Columns) { colIndex++; worksheet.Cells[2, colIndex] = col.ColumnName; } foreach (DataRow row in table.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in table.Columns) { colIndex++; worksheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } } rowIndex = 1; colIndex = 0; } ExcelObj.DisplayAlerts = false; ExcelObj.ActiveWorkbook.SaveAs(savepath); //MessageBox.Show("数据导出成功!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); ExcelObj.Visible = false; System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); sheets = null; theWorkbook.Close(true, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(theWorkbook); theWorkbook = null; ExcelObj.Application.Workbooks.Close(); ExcelObj.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelObj); Kill(ExcelObj); int generation = System.GC.GetGeneration(ExcelObj); ExcelObj = null; System.GC.Collect(generation); } catch (Exception ex) { //MessageBox.Show(ex.ToString()); } finally { GC.Collect();//垃圾回收 GC.WaitForPendingFinalizers(); } }
/// <summary> /// 打印交易票 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void printBtn_Click(object sender, EventArgs e) { if (!this.ValidateChildren(ValidationConstraints.Enabled & ValidationConstraints.TabStop & ValidationConstraints.Selectable)) { MessageBox.Show("控件内容不合法"); return; } Object oMissing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application m_objExcel = null; Microsoft.Office.Interop.Excel._Workbook m_objBook = null; Microsoft.Office.Interop.Excel.Sheets m_objSheets = null; Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null; Microsoft.Office.Interop.Excel.Range m_objRange = null; try { m_objExcel = new Microsoft.Office.Interop.Excel.Application(); DirectoryInfo Dir = new DirectoryInfo("."); m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "\\input.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1)); // 开票日期 m_objRange = m_objSheet.get_Range("B1", oMissing); m_objRange.Value = VehicleDao.GetCurrentDate(); // 买 方 单 位 /个人 m_objRange = m_objSheet.get_Range("C5", oMissing); m_objRange.Value = this.currentName.Text; // 单位代码/身份证号码 m_objRange = m_objSheet.get_Range("K5", oMissing); m_objRange.Value = this.currentId.Text; // 买方单位/个人住址 m_objRange = m_objSheet.get_Range("C6", oMissing); m_objRange.Value = this.currentAddress.Text; // 电话 m_objRange = m_objSheet.get_Range("L6", oMissing); m_objRange.Value = this.currentPhone.Text; // 卖 方 单 位/ 个人 m_objRange = m_objSheet.get_Range("C7", oMissing); m_objRange.Value = this.originName.Text; // 单位代码/身份证号码 m_objRange = m_objSheet.get_Range("K7", oMissing); m_objRange.Value = this.originId.Text; // 卖方单位/个人住址 m_objRange = m_objSheet.get_Range("C8", oMissing); m_objRange.Value = this.originAddress.Text; // 电话 m_objRange = m_objSheet.get_Range("L8", oMissing); m_objRange.Value = this.originPhone.Text; // 车 牌 照 号 m_objRange = m_objSheet.get_Range("C9", oMissing); m_objRange.Value = "辽B." + this.license.Text; // 登记证号 m_objRange = m_objSheet.get_Range("E9", oMissing); m_objRange.Value = this.certificate.Text; // 车 辆 类 型 m_objRange = m_objSheet.get_Range("L9", oMissing); m_objRange.Value = this.vehicleType.Text; // 车架号/车辆识别代码 m_objRange = m_objSheet.get_Range("C10", oMissing); m_objRange.Value = this.vin.Text; // 厂牌型号 m_objRange = m_objSheet.get_Range("E10", oMissing); m_objRange.Value = this.brand.Text; // 转入地车辆管理所名称 m_objRange = m_objSheet.get_Range("L10", oMissing); m_objRange.Value = this.department.Text; // 车价 合 计(大写) m_objRange = m_objSheet.get_Range("C11", oMissing); m_objRange.Value = this.transactions.Text; // 车价 合 计(小写) m_objRange = m_objSheet.get_Range("L11", oMissing); m_objRange.Value = this.transactions.Text; m_objExcel.DisplayAlerts = false; m_objSheet.PrintOut(); } catch (Exception ex) { MessageBox.Show("打印失败,请检查打印机设置。错误代码:" + ex.Message); } finally { if (m_objBook != null) { m_objBook.Close(oMissing, oMissing, oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); } if (m_objExcel != null) { m_objExcel.Workbooks.Close(); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); } m_objBook = null; m_objExcel = null; GC.Collect(); } }
public DataSet ImportExcel(string filePath) { DataSet ds = null; DataTable dt = null; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workbook = null; Microsoft.Office.Interop.Excel.Worksheet worksheet = null; Microsoft.Office.Interop.Excel.Sheets sheets = null; Microsoft.Office.Interop.Excel.Range range = null; object missing = System.Reflection.Missing.Value; try { if (excel == null) { return(null); } //打开 Excel 文件 workbook = excel.Workbooks.Open(filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //获取所有的 sheet 表 sheets = workbook.Worksheets; ds = new DataSet(); for (int i = 1; i <= sheets.Count; i++) { //获取第一个表 worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i); int rowCount = worksheet.UsedRange.Rows.Count; int colCount = worksheet.UsedRange.Columns.Count; int rowIndex = 1; //起始行为 1 int colIndex = 1; //起始列为 1 DataColumn dc; dt = new DataTable(); dt.TableName = "table" + i.ToString(); //读取列名 for (int j = 0; j < colCount; j++) { range = worksheet.Cells[rowIndex, colIndex + j]; dc = new DataColumn(); dc.DataType = Type.GetType("System.String"); dc.ColumnName = range.Text.ToString().Trim(); //添加列 dt.Columns.Add(dc); } //读取行数据 for (int k = 1; k < rowCount; k++) { DataRow dr = dt.NewRow(); for (int l = 0; l < colCount; l++) { range = worksheet.Cells[rowIndex + k, colIndex + l]; //使用 range.Value.ToString(); 或 range.Value2.ToString(); 或 range.Text.ToString(); 都可以获取单元格的值 dr[l] = range.Text.ToString(); } dt.Rows.Add(dr.ItemArray); } ds.Tables.Add(dt); } } catch (Exception ex) { throw; } finally { workbook.Close(); //关闭退出 excel.Quit(); //释放 COM 对象 Marshal.ReleaseComObject(worksheet); Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(excel); worksheet = null; workbook = null; excel = null; GC.Collect(); } return(ds); }
public void Export(DataTable dt, string sheetName, string title) { //Tạo mới một Excel WorkBook oExcel.Visible = true; oExcel.DisplayAlerts = false; oExcel.Application.SheetsInNewWorkbook = 1; oBooks = oExcel.Workbooks; oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing)); oSheets = oBook.Worksheets; oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1); oSheet.Name = sheetName; // Tạo phần đầu nếu muốn Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "D1"); head.MergeCells = true; head.Value2 = title; head.Font.Bold = true; head.Font.Name = "Times New Roman"; head.Font.Size = "16"; head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // Tạo tiêu đề cột Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3"); cl1.Value2 = "Mã Học Sinh"; cl1.ColumnWidth = 15; Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3"); cl2.Value2 = "Mã Lớp"; cl2.ColumnWidth = 15.0; Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3"); cl3.Value2 = "Tên Lớp"; cl3.ColumnWidth = 17.0; Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3"); cl4.Value2 = "Điểm Tổng Kết"; cl4.ColumnWidth = 15.0; Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "D3"); rowHead.Font.Bold = true; // Kẻ viền rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; // Thiết lập màu nền rowHead.Interior.ColorIndex = 15; rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable, // vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần. object[,] arr = new object[dt.Rows.Count, dt.Columns.Count]; //Chuyển dữ liệu từ DataTable vào mảng đối tượng for (int r = 0; r < dt.Rows.Count; r++) { DataRow dr = dt.Rows[r]; for (int c = 0; c < dt.Columns.Count; c++) { arr[r, c] = dr[c]; } } //Thiết lập vùng điền dữ liệu int rowStart = 4; int columnStart = 1; int rowEnd = rowStart + dt.Rows.Count - 1; int columnEnd = dt.Columns.Count; // Ô bắt đầu điền dữ liệu Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart]; // Ô kết thúc điền dữ liệu Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd]; // Lấy về vùng điền dữ liệu Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2); //Điền dữ liệu vào vùng đã thiết lập range.Value2 = arr; // Kẻ viền range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid; // Căn giữa cột STT Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart]; Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3); oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; oSheet.Cells[rowEnd + 2, 1] = "Kết quả thống kê: "; oSheet.Cells[rowEnd + 2, 2] = "Loại Giỏi: " + gioil; oSheet.Cells[rowEnd + 3, 2] = "Loại Khá: " + khal; oSheet.Cells[rowEnd + 4, 2] = "Loại TB: " + tbl; oSheet.Cells[rowEnd + 5, 2] = "Loại Yếu: " + yeul; oSheet.Cells[rowStart - 2, 2] = " Lớp: " + lop + " Năm học: " + namlop; }
/// <summary> /// 输出 /// </summary> /// <param name="title"></param> /// <param name="coumnTitles"></param> /// <param name="textList"></param> public static void OutExcel(string title, ArrayList coumnTitles, ArrayList textList) { //外部Excel对象 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = true; Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks; Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets; Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1); Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1", System.Reflection.Missing.Value); //标题 worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 1]).Value2 = title; worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 9]).Merge(0); worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 9]).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 9]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 9]).Font.Size = 16; worksheet.get_Range(app.Cells[1, 1], app.Cells[1, 9]).Font.Bold = true; //列标题 for (int i = 1; i <= coumnTitles.Count; i++) { worksheet.get_Range(app.Cells[3, i], app.Cells[3, i]).Value2 = coumnTitles[i - 1].ToString(); worksheet.get_Range(app.Cells[3, i], app.Cells[3, i]).Font.Size = 10; worksheet.get_Range(app.Cells[3, i], app.Cells[3, i]).Font.Bold = true; } //内容 for (int i = 0; i < textList.Count; i++) { string[] text = (string[])textList[i]; for (int j = 1; j <= text.Length; j++) { worksheet.get_Range(app.Cells[4 + i, j], app.Cells[4 + i, j]).Value2 = text[j - 1].ToString(); worksheet.get_Range(app.Cells[4 + i, j], app.Cells[4 + i, j]).Font.Size = 9; } } }
public string ReadExistingExcel(List <ExcellData> data, string currency, string day, string month, string year, ExcelType excelType) { string excelName = "İşçiTemp"; if (excelType == ExcelType.Official) { excelName = "MemurTemp"; } var path = GetFilePath(excelName); excel = new Microsoft.Office.Interop.Excel.Application { Visible = true, DisplayAlerts = false }; try { workBook = excel.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); } catch (Exception e) { throw new Exception(e.Message); } excel.Visible = false; workSheets = workBook.Worksheets; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workSheets.get_Item("kurummaas"); Microsoft.Office.Interop.Excel.Range range = worksheet.UsedRange; int colCount = range.Columns.Count; int rowCount = range.Rows.Count; worksheet.Cells[7, 3] = month; worksheet.Cells[8, 4] = currency; worksheet.Cells[4, 3] = string.Concat(day, "/", month, "/", year); //worksheet.Cells[5, 4] = totalAmount; //worksheet.Cells[6, 4] = data.Count; int rowBeginning = 10; foreach (var item in data) { rowBeginning++; for (int index = 1; index < 2; index++) { worksheet.Cells[rowBeginning, 1] = item.FullName; worksheet.Cells[rowBeginning, 2] = item.AccountNumber; worksheet.Cells[rowBeginning, 3] = item.RegisterNumber; worksheet.Cells[rowBeginning, 4] = item.Amount; worksheet.Cells[rowBeginning, 5] = item.Iban; } } year = year.Substring(2, 2); string name = string.Concat("\\Sources\\İSO-DER-SM-", excelName.Replace("Temp", "").ToUpper(), " (mdm) ", day, ".", month, ".", year, ".xls"); string destinatonPath = string.Concat(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), name); workBook.SaveCopyAs(destinatonPath); workBook.Close(Missing.Value, Missing.Value, Missing.Value); worksheet = null; workBook = null; excel.Quit(); GC.WaitForPendingFinalizers(); GC.Collect(); return(destinatonPath); }
private void button2_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); ds.ReadXml(Application.StartupPath + "\\ExcelBindingXml.xml"); Microsoft.Office.Interop.Excel.Application m_objExcel = null; Microsoft.Office.Interop.Excel._Workbook m_objBook = null; Microsoft.Office.Interop.Excel.Sheets m_objSheets = null; Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null; Microsoft.Office.Interop.Excel.Range m_objRange = null; object m_objOpt = System.Reflection.Missing.Value; try { m_objExcel = new Microsoft.Office.Interop.Excel.Application(); m_objBook = m_objExcel.Workbooks.Open(Application.StartupPath + "\\ExcelTemplate.xlsx", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1)); int maxRow = m_objSheet.UsedRange.Rows.Count; int maxCol = m_objSheet.UsedRange.Columns.Count; int TitleRow = 0; for (int excelrow = 1; excelrow <= maxRow; excelrow++) { for (int col = 0; col < maxCol; col++) { string excelColName = ExcelColNumberToColText(col); m_objRange = m_objSheet.get_Range(excelColName + excelrow.ToString(), m_objOpt); m_objRange.Text.ToString().Contains("$"); TitleRow = excelrow; break; } } for (int excelrow = 1; excelrow <= ds.Tables[0].Rows.Count; excelrow++) { DataRow dr = ds.Tables[0].Rows[excelrow - 1]; for (int col = 0; col < maxCol; col++) { string excelColName = ExcelColNumberToColText(col); m_objRange = m_objSheet.get_Range(excelColName + TitleRow.ToString(), m_objOpt); //Microsoft.Office.Interop.Excel.Range item_objRange = m_objSheet.get_Range(excelColName + (maxRow + excelrow).ToString(), m_objOpt); if (m_objRange.Text.ToString().Replace("$", "") == ds.Tables[0].Columns[col].ColumnName) { m_objSheet.Cells[maxRow + excelrow, col + 1].value = dr[col].ToString(); m_objSheet.Cells[maxRow + excelrow, col + 1].Style = m_objRange.Style; Console.WriteLine(m_objSheet.Cells[maxRow + excelrow, col + 1].value + ":" + dr[col].ToString()); //item_objRange.Value2 = dr[col].ToString(); } } } Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)m_objSheet.Rows[TitleRow, m_objOpt]; range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); m_objExcel.DisplayAlerts = false; m_objBook.SaveAs(Application.StartupPath + "\\ExcelBindingXml.xlsx", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { m_objBook.Close(m_objOpt, m_objOpt, m_objOpt); m_objExcel.Workbooks.Close(); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); m_objBook = null; m_objExcel = null; GC.Collect(); } }