//把Excel文档转换为Pdf文档
#region
private static void ExcelToPdf(string ExcelFileName, string PdfFileName)
{
//Object missing = System.Reflection.Missing.Value;
//Object ReadOnly = (object)true;
//ET.Application app = new ET.Application();
//app.Visible = false;
//ET.workbook doc = (ET.workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//doc.ExportPdf(PdfFileName, "", "");
//doc.Close(missing, missing, missing);
//app.Quit();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
//app = null;
Object missing = System.Reflection.Missing.Value;
Object ReadOnly = (object)true;
Excel.Application app = new Excel.Application();
app.Visible = false;
Excel.Workbook doc = (Excel.Workbook)app.Workbooks.Open(ExcelFileName, missing, ReadOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
doc.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, PdfFileName);
doc.Close(missing, missing, missing);
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
// 启动Excel 程序
public bool StartupApp(bool visible)
{
// 检查是否有Excel正在运行
bool flag = false;
foreach (var item in Process.GetProcesses())
{
if (item.ProcessName == "EXCEL")
{
flag = true;
break;
}
}
if (!flag)
{
ExcelApp = new Excel.Application();
}
else
{
object obj = Marshal.GetActiveObject("Excel.Application"); // 引用已在执行的Excel
ExcelApp = obj as Excel.Application;
}
ExcelApp.Visible = visible; // Excel程序不可见
return true;
}
public void ReadSample()
{
Excel.Application excelApp = new Excel.Application();
if (excelApp != null)
{
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(@"C:\test.xls", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];
Excel.Range excelRange = excelWorksheet.UsedRange;
int rowCount = excelRange.Rows.Count;
int colCount = excelRange.Columns.Count;
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
Excel.Range range = (excelWorksheet.Cells[i, 1] as Excel.Range);
string cellValue = range.Value.ToString();
//do anything
}
}
excelWorkbook.Close();
excelApp.Quit();
}
}
public static DataTable DataTableFromXlsx(string Path, string password = "")
{
Excel.Application objXL = null;
Excel.Workbook objWB = null;
objXL = new Excel.Application();
objWB = objXL.Workbooks.Open(Path);
Excel.Worksheet objSHT = objWB.Worksheets[1];
int rows = objSHT.UsedRange.Rows.Count;
int cols = objSHT.UsedRange.Columns.Count;
DataTable dt = new DataTable();
int noofrow = 1;
for (int c = 1; c <= cols; c++)
{
string colname = objSHT.Cells[1, c].Text;
dt.Columns.Add(colname);
noofrow = 2;
}
for (int r = noofrow; r <= rows; r++)
{
DataRow dr = dt.NewRow();
for (int c = 1; c <= cols; c++)
{
dr[c - 1] = objSHT.Cells[r, c].Text;
}
dt.Rows.Add(dr);
}
objWB.Close();
objXL.Quit();
return(dt);
}
/// <summary>获取Excel实例</summary>
static void GetExcelInstance()
{
if (ExApp == null)
{
ExApp = new Excel.Application();
}
}
// <summary>
/// 退出报表时关闭Excel和清理垃圾Excel进程
/// </summary>
private void EndReport()
{
object missing = System.Reflection.Missing.Value;
try
{
m_xlApp.Workbooks.Close();
m_xlApp.Workbooks.Application.Quit();
m_xlApp.Application.Quit();
m_xlApp.Quit();
}
catch { }
finally
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
m_xlApp = null;
}
catch { }
try
{
//清理垃圾进程
this.killProcessThread();
}
catch { }
GC.Collect();
}
}
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="pPath"></param>
/// <returns></returns>
private string GetExcelSheetName(string pPath)
{
//打开一个Excel应用
_excelApp = new Excel.Application();
if (_excelApp == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return(sheetName);
}
public void XtraGridTo(GridView grv, int DongBatDau, int CotBatDau)
{
try
{
oXL = new Excel.Application();
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//oSheet.UsedRange.Cells.Borders.Value = 1;
int SoCotGroup = 0;
foreach (GridColumn grc in grv.Columns)
{
if (grc.GroupIndex >= 0)
{
SoCotGroup++;
}
}
// Mở file Excel
oXL.Visible = true;
}
catch (Exception ex)
{
oXL.Quit();
throw ex;
}
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Excel.Application();
double loanpayment;
double dblInterest;
double dblMonths;
double dblprincipal;
dblprincipal = Convert.ToDouble(txtPrincipal.Text);
dblMonths = Convert.ToDouble(txtMonths.Text);
dblInterest = Convert.ToDouble(txtInterest.Text);
loanpayment = xlApp.WorksheetFunction.Pmt(dblInterest / 12, dblMonths, dblprincipal, 0, 0);
MessageBox.Show("the monthly payment is: " + Convert.ToString(Math.Abs(loanpayment)), "Mortgage");
/* begin test of PMT formula: */
double myPayment;
myPayment = Convert.ToDouble(dblprincipal) * -((dblInterest / 12) / (1 - Math.Pow((1 + (dblInterest / 12)), (-Convert.ToDouble(dblMonths)))));
if (Math.Round(loanpayment, 5) == Math.Round(myPayment, 5))
{
Debug.WriteLine("Test passed: formula correct");
MessageBox.Show("Test passed: formula correct");
}
else
{
Debug.WriteLine("Test failed: incorrect formula result");
MessageBox.Show("Test failed: incorrect formula result");
}
}
public void GenerateReport(IEnumerable <StudentDto> students)
{
Excel.Application app = new Excel.Application();
app.DisplayAlerts = false;
Excel.Workbook book_template = app.Workbooks.Open(@"шаблон_отчета.xlsx");
Excel._Worksheet sheet_template = book_template.Sheets["отчет"];
foreach (var ob in students)
{
//1. Создаем объкт LearnerReport из БД
LearnerReport report = new LearnerReport
{
SNS = $"{ob.surname} {ob.name} {ob.SecondName}",
SchoolName = ob.SchoolName,
ClassName = ob.ClassName,
TestResult5 = ob.TestResult5
};
//2. Экспорт объкта LearnerReport в шаблон xlsx
sheet_template.Range["C4"].Value2 = report.SNS;
sheet_template.Range["C5"].Value2 = report.SchoolName;
sheet_template.Range["C6"].Value2 = report.ClassName;
sheet_template.Range["C9"].Value2 = report.TestResult5;
//3. Сохраняем полученный файл в .pdf на рабочем столе
string file_name = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\{report.SNS}.pdf";
sheet_template.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, file_name);
}
book_template.Close(0);
book_template = null;
app.Quit();
app = null;
}
static void DisplayInExcel(IEnumerable <dynamic> entities)
{
// Apparently, this namespace no longer exsists.
var excelApp = new Excel.Application();
excelApp.Visible = true;
excelApp.Workbooks.Add();
dynamic workSheet = excelApp.ActiveSheet;
workSheet.Cells[1, "A"] = "Header A";
workSheet.Cells[2, "B"] = "Header B";
var row = 1;
foreach (var entity in entities)
{
row++;
workSheet.Cells[row, "A"] = entity.ColumnA;
workSheet.Cells[row, "B"] = entity.ColumnB;
}
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
}
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="pPath"></param>
/// <returns></returns>
private string GetExcelSheetName(string pPath)
{
//打开一个Excel应用
_excelApp = new Excel.Application();
if (_excelApp == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return sheetName;
}
private void btnFill_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt = 0;
int cCnt = 0;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
MessageBox.Show(str);
}
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}//AddSparePartsFromExcelFile
//Сохранение в Excel файл.
public static void SaveToExcelFile(IList <SparePart> spareParts)
{
Excel.Application ExcelApp = new Excel.Application();
Excel.Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorkSheet;
//Книга.
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
//Таблица.
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
for (int i = 0; i < spareParts.Count; ++i)
{
int j = 0;
ExcelApp.Cells[i + 1, ++j] = spareParts[i].Photo;
ExcelApp.Cells[i + 1, ++j] = spareParts[i].Articul;
ExcelApp.Cells[i + 1, ++j] = spareParts[i].Title;
ExcelApp.Cells[i + 1, ++j] = spareParts[i].Manufacturer;
ExcelApp.Cells[i + 1, ++j] = spareParts[i].Unit;
ExcelApp.Cells[i + 1, ++j] = spareParts[i].Count;
ExcelApp.Cells[i + 1, ++j] = spareParts[i].Price;
ExcelApp.Cells[i + 1, ++j] = spareParts[i].Markup;
}//for
//Вызываем нашу созданную эксельку.
ExcelApp.Visible = true;
ExcelApp.UserControl = true;
}//SaveToExcelFile
public void CreateStudentBaseSimpleTable()
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件
excelPath+@"\report\ImportBaseTableForStudent(simple).xls",Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing));
m_objExcel.Visible = true;
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
}
}
/// <summary>
/// 将datagridview中的数据导出到excel中
/// </summary>
/// <param name="view">datagridview</param>
public static void ToExel(DataGridView view)
{
if (view.Rows.Count == 0)
{
untCommon.InfoMsg("表格中没有数据，不能导出空表");
return;
}
//建立Excel对象
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = true;
//生成字段名称
for (int i = 0; i < view.ColumnCount; i++)
{
excel.Cells[1, i + 1] = view.Columns[i].HeaderText;
}
//填充数据
for (int row = 0; row <= view.RowCount - 1; row++)
{
for (int column = 0; column < view.ColumnCount; column++)
{
if (view[column, row].ValueType == typeof(string))
{
excel.Cells[row + 2, column + 1] = "'" + view[column, row].Value.ToString();
}
else
{
excel.Cells[row + 2, column + 1] = view[column, row].Value.ToString();
}
}
}
}
public void CreateStudentBaseSimpleTable()
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件
excelPath + @"\report\ImportBaseTableForStudent(simple).xls", Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing));
m_objExcel.Visible = true;
}
catch (Exception e)
{
Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
}
}
///// <summary>
///// 允许供应商编辑内容对策 2014-9-14 by hyf
///// </summary>
//public void allowedit()
//{
// Excel.AllowEditRanges ranges = m_objSheet.Protection.AllowEditRanges;
// ranges.Add("供应商可编辑对策区", m_objExcel.Application.get_Range("B29", "R46"), Type.Missing);
//}
/////////////////////////////////
public bool KillAllExcelApp()
{
try
{
if (m_objExcel != null) // isRunning是判断xlApp是怎么启动的flag.
{
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
//释放COM组件，其实就是将其引用计数减1
//System.Diagnostics.Process theProc;
foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
{
//先关闭图形窗口。如果关闭失败...有的时候在状态里看不到图形窗口的excel了，
//但是在进程里仍然有EXCEL.EXE的进程存在，那么就需要杀掉它:p
if (theProc.CloseMainWindow() == false)
{
theProc.Kill();
}
}
m_objExcel = null;
return(true);
}
}
catch
{
return(false);
}
return(true);
}
private bool UnloadSingleValue(Excel.Application ExcelApp, int SheetIndex)
{
object missingValue = Type.Missing;
string TempMessage = "(Заполнение единичных тегов) ";
for (int i = 0; i < dtSingleValue.Rows.Count; i++)
{
try
{
if (((Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[SheetIndex]).Cells.Find(dtSingleValue.Rows[i]["TagName"].ToString(), missingValue, Excel.XlFindLookIn.xlValues, missingValue, missingValue, Excel.XlSearchDirection.xlNext, true, missingValue, missingValue) == null)
{
WriteError(-1, TempMessage + "В листе - '" + ((Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[SheetIndex]).Name + "' отсутствует информация о '" + dtSingleValue.Rows[i]["TagName"].ToString() + "'");
return(false);
}
((Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[SheetIndex]).Cells.Replace(dtSingleValue.Rows[i]["TagName"].ToString(), dtSingleValue.Rows[i]["TagValue"].ToString(), Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, false, false, false);
}
catch (Exception exc)
{
WriteError(-2, TempMessage + exc.Message);
return(false);
}
}
return(true);
}
/// <summary>
/// 导出数据表到Excel
/// </summary>
/// <param name="table">数据表</param>
/// <param name="title">标题</param>
/// <param name="info">相关信息</param>
private void ExportToExcel(DataTable table, string title, string info)
{
if (table == null)
{
return;
}
Excel.Application myExcel = new Excel.Application();
myExcel.Application.Workbooks.Add(true);
//写入行头
int sumRowCount = table.Rows.Count;
int sumColCount = 0;
for (int j = 0; j < table.Columns.Count; j++)
{
sumColCount += 1;
myExcel.Cells[5, sumColCount] = table.Columns[j].ColumnName;
}
myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, sumColCount]).Font.Bold = true;
myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, sumColCount]).Font.Size = 10;
//逐行写入数据
for (int i = 0; i < table.Rows.Count; i++)
{
int ncol = 0;
for (int j = 0; j < table.Columns.Count; j++)
{
ncol = ncol + 1;
myExcel.Cells[6 + i, ncol] = "'" + table.Rows[i][j].ToString().Trim();
}
}
//设置报表表格为最适应宽度
myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + sumRowCount, sumColCount]).Select();
myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + sumRowCount, sumColCount]).Columns.AutoFit();
//加边框
myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + sumRowCount, sumColCount]).Borders.LineStyle = 1;
//报表名称
myExcel.Cells[1, 1] = title;
myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, sumColCount]).Font.Bold = true;
myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, sumColCount]).Font.Size = 16;
//报表名称跨行居中
myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, sumColCount]).Select();
myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, sumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//报表条件
myExcel.Cells[3, 1] = info.Trim();
myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, sumColCount]).Font.Size = 10;
myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, sumColCount]).Select();
myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, sumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//最后一行为黄色
myExcel.get_Range(myExcel.Cells[5 + sumRowCount, 1], myExcel.Cells[5 + sumRowCount, sumColCount]).Interior.ColorIndex = 19;
//让Excel文件可见
myExcel.Visible = true;
}
Process GetId(Excel.Application excelApp)
{
int id;
GetWindowThreadProcessId(excelApp.Hwnd, out id);
return(Process.GetProcessById(id));
}
public void ExportTeacherAllReports(string path, DateTime startDate, DateTime endDate)
{
string excelTempFilePath = AppDomain.CurrentDomain.BaseDirectory;
Excel.Application m_objExcel = null;
Excel.Workbooks m_objBooks = null;
Excel._Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel._Worksheet m_objSheet = null;
Excel.Range m_objRange = null;
Excel.Font m_objFont = null;
System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value;
try
{
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath + @"report\TeacherDutyAll.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
ExportAllNormal(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
ExportStat(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
ExportSingleStat(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
ExportSingle(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
m_objBook.SaveAs(path, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objFont);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objFont = null;
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
static void Main(string[] args)
{
var excel = new Excel.Application();
ICar c = new ICar();
c.Drive();
}
/// <summary>
/// Export data table to Excel like DataGridTableStyle
/// </summary>
/// <param name="view"></param>
/// <param name="headers"></param>
/// <param name="indexes"></param>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
public void ExportToExcel(DataView view, string[] headers, int[] indexes, int startRow, int startCol)
{
Excel.Application excelApp = null;
Excel.Workbook excelBook = null;
Excel.Worksheet sheet = null;
try
{
excelApp = new Excel.Application();
excelBook = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
sheet = (Excel.Worksheet)excelBook.Worksheets[1];
ExportToExcel(view, headers, indexes, startRow, startCol, sheet);
excelApp.Visible = true;
}
catch (Exception ex)
{
log.Error(ex.Message, ex);
if (excelApp != null)
{
excelApp.Visible = true;
}
}
}
public static void PrintPreview(string fileName, PageSetup p)
{
Excel.Application excelApp = null;
Excel.Workbook workBook = null;
try
{
object objOpt = System.Reflection.Missing.Value;
excelApp = ExcelInit();
workBook = excelApp.Workbooks.Open(fileName, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt,
objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt);
if (p != null)
{
//if (p.TopMargin != 0 || p.LeftMargin != 0 || p.RightMargin != 0 || p.BottomMargin != 0)
{
int sheetCount = workBook.Sheets.Count;
Excel.Worksheet workSheet = null;
int i = 0;
while (i <= sheetCount)
{
try
{
workSheet = workBook.Sheets[i];
break;
}
catch
{
i++;
}
}
workSheet.PageSetup.LeftMargin = p.GetLeftMargin();
workSheet.PageSetup.RightMargin = p.GetRightMargin();
workSheet.PageSetup.TopMargin = p.GetTopMargin();
workSheet.PageSetup.BottomMargin = p.GetBottomMargin();
//workSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
}
}
excelApp.Visible = true;
workBook.PrintPreview(objOpt);
excelApp.Visible = false;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (workBook != null)
{
workBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
ExcelTerminal(excelApp);
}
}
private void axWebBrowser1_NavigateComplete2(object sender, AxSHDocVw.DWebBrowserEvents2_NavigateComplete2Event e)
{
Object o = e.pDisp;
Object oDocument = o.GetType().InvokeMember("Document", System.Reflection.BindingFlags.GetProperty, null, o, null);
Object oApplication = o.GetType().InvokeMember("Application", System.Reflection.BindingFlags.GetProperty, null, oDocument, null);
excelApp = (Excel.Application)oApplication;
}
protected virtual void ActivateExcel()
{
_excelApplication = new Excel.Application();
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Excel._Workbook)(_workBooks.Add(_value));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1));
}
/// <summary>
/// Create Excel application parameters instances
/// </summary>
private void CreateExcelRef()
{
_excelApp = new Excel.Application();
_books = (Excel.Workbooks)_excelApp.Workbooks;
_book = (Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Excel.Sheets)_book.Worksheets;
_sheet = (Excel._Worksheet)(_sheets.get_Item(1));
}
/// <summary>退出Excel</summary>
public static void CloseExcel()
{
if (ExApp != null)
{
ExApp.Quit();
ExApp = null;
}
}
private void BtnOpen_Click(object sender, EventArgs e)
{
Excel.Application excel = new Excel.Application();
string strPath = this.txtExcel.Text;
Excel.Workbook wBook = excel.Application.Workbooks.Add(strPath);
excel.Visible = true;
}
/// <summary>退出Excel</summary>
public static void CloseExcel()
{
if (ExApp != null)
{
ExApp.Quit();
ExApp = null;
}
}
public void AllStuInfoPrint(DataSet dsExportData, string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\AllStuInfo.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
dv = dsExportData.Tables[0].DefaultView;
for (int row = 1; row < dsExportData.Tables[1].Rows.Count; row++)
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Copy(m_objOpt, m_objSheet);
}
for (int row = 1; row <= dsExportData.Tables[1].Rows.Count; row++)
{
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(row);
m_objSheet.Name = Convert.ToString(dsExportData.Tables[1].Rows[row - 1][0]);
BindingData(m_objSheet.Name);
}
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch (Exception e)
{
Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
// Export DataTable into an excel file with field names in the header line
// - Save excel file without ever making it visible if filepath is given
// - Don't save excel file, just make it visible if no filepath is given
public static void ExportToExcel(this DataTable Tbl, string ExcelFilePath = null)
{
try
{
if (Tbl == null || Tbl.Columns.Count == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
// load excel, and create a new workbook
Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Add();
// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;
// column headings
for (int i = 0; i < Tbl.Columns.Count; i++)
{
workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < Tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < Tbl.Columns.Count; j++)
{
workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
}
}
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
workSheet.SaveAs(ExcelFilePath);
excelApp.Quit();
MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
private void creat_Click(object sender, EventArgs e)
{
// textBox2.Text = UInt96.Parse(textBox1.Text).Inc().ToString();
saveFileDialog1.Title = "MAC地址生成工具----百思威科技";
saveFileDialog1.Filter = "Excel(*.xls)|*.xls";
saveFileDialog1.FileName = string.Format("MAC地址_{0}", DateTime.Now.ToString("yyyyMMdd"));
DialogResult result = saveFileDialog1.ShowDialog();
Excel._Application xlapp = new Excel.Application();
Excel.Workbook xlbook = xlapp.Workbooks.Add(true);
Excel.Worksheet xlsheet = (Excel.Worksheet)xlbook.Worksheets[1];
// MessageBox.Show(textBox2.Text);
int RowCount = Convert.ToInt32(textBox2.Text);
int RowIndex = 0;
string val = textBox1.Text;
val = val.Replace(":", "");
for (int i = 0; i < RowCount; i++)
{
RowIndex++;
if (i != 0)
{
if (radioButton1.Checked)
{
val = UInt96.Parse(val).Inc().ToString(":");
}
else
{
val = UInt96.Parse(val).Inc().ToString("");
}
xlsheet.Cells[RowIndex, 1] = val;
}
else
{
if (radioButton1.Checked)
{
string[] sArray = Regex.Split(val, @"(\w{2})");
List <string> listTemp = new List <string>();
foreach (string s in sArray)
{
if (string.IsNullOrEmpty(s))
{
continue;
}
listTemp.Add(s);
}
string[] newlist = listTemp.ToArray();
val = String.Join(":", newlist);
}
xlsheet.Cells[RowIndex, 1] = val;
}
}
xlbook.Saved = true;
xlbook.SaveCopyAs(saveFileDialog1.FileName);
xlapp.Quit();
MessageBox.Show("导出成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void Button1_Click(object sender, EventArgs e)
{
Excel.Application xl = new Excel.Application();
xl.Visible = true;
xl.Workbooks.Add("c:\\Pasta1.xlt");
xl.Cells[3, 4] = TextBox1.Text;
}
public void ExportExcel()
{
try
{
//if (e.KeyCode != Keys.F12) return;
if (_autoExcel == false) return;
BindingSource bsTemp = this.PrimaryGrid.DataSource as BindingSource;
if (bsTemp == null) return;
DataTable tempDATA = bsTemp.DataSource as DataTable;
if (tempDATA == null) return;
DataTable DATA = tempDATA;
DATA.RejectChanges();
Excel.Application objExcel;
try
{
//Tìm instance Excel đang chạy.
objExcel = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
}
catch
{
//Không có instance nào của Excel đang chạy.
objExcel = new Excel.Application();
}
try
{
objExcel.Interactive = false;
objExcel.Interactive = true;
}
catch
{
//CrossShow2 msg = new CrossShow2(clsMx.Show);
//this.Invoke(msg, Qk.Settings.ctrmsgPrintError, eAlertType.CanhBao);
return;
}
objExcel.Visible = false;
string tmpFileXSL = Path.Combine(st.TEMP_DIR, Guid.NewGuid().ToString() + ".xls");
clsAll.ExtractXLS("AutoExcel.xls", tmpFileXSL);
objExcel.Workbooks.Open(tmpFileXSL);
object[,] objData = clsAll.DataTable2ArrayObjects(DATA);
string strRange = string.Format("A{0}:{1}{2}", 1, clsAll.GetExcelColumnLabel(DATA.Columns.Count), DATA.Rows.Count);
objExcel.Range[strRange].Value = objData;
objExcel.Visible = true;
objExcel.ActiveWorkbook.Save();
//objExcel.Worksheets.PrintPreview();
}
catch (Exception ex)
{
clsMx.Show(ex, this.Name);
}
}
/// <summary>
/// エクセルデータの操作を行う
/// </summary>
/// <param name="path">xlsファイルパス</param>
public ExcelController(string path)
{
exPath = path;
oXls = new Excel.Application();
//Excel画面を表示しない
oXls.Visible = false;
//Excelファイルをオープンする
oWBook = (Excel.Workbook)(oXls.Workbooks.Open(exPath));
}
private void LoadDataExcel()
{
try
{
if (tbUploadFile.Text == "")
{
MessageBox.Show("Vui lòng load file để cập nhật!");
}
string status_name = "", status_code = "", result = "";
string resultT = "" , MSG = "";
conTTTSOA SOA = new conTTTSOA();
status_name = cbTrangthai.Text.ToString();
status_code = cbTrangthai.SelectedValue.ToString();
// string file = @"C:\Users\thongnt.NAMABANK0\Pictures\CẬP NHẬT TRẠNG THÁI ĐỒNG LOẠT FILE MẪU.xls";
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(tbUploadFile.Text);
//Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file);
Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
string contract_number = "";
int n_true = 0;
for (int i = 1; i <= xlRange.Count; i++)
{
if ((string)(xlRange.Cells[i, 1] as Excel.Range).Value2.ToString() == "")
{
return;
}
contract_number = (string)(xlRange.Cells[i, 1] as Excel.Range).Value2.ToString();
result = SOA.SOA_Change_Contr_Status("Web", contract_number, status_code, status_name, "82");
resultT = result.Substring(result.IndexOf("<result>") + "<result>".Length, result.IndexOf("</result>") - (result.IndexOf("<result>") + "<result>".Length));
MSG = result.Substring(result.IndexOf("<msg>") + "<msg>".Length, result.IndexOf("</msg>") - (result.IndexOf("<msg>") + "<msg>".Length));
if(resultT == "0")
{
n_true = n_true + 1;
lbKetqua.Text = MSG + " " + n_true;
}
if (i == xlRange.Count)
{
MessageBox.Show("Đã hoàn thành cập nhật!!!!");
return;
}
}
}
catch (Exception error)
{
MessageBox.Show("File excel không đúng định dạng!",error.Message);
}
}
public void NutritionPrint(string savePath)
{
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\nutrition.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
int number = m_objSheets.Count;
writeCover();
writeStuAmount();
writeStuConvert();
writeACC1();
writeACC2();
writeElement();
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch(Exception ex)
{
Util.WriteLog(ex.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public static void NativeExcel_CreateExcel(
DataTable Dt
, ClsExcel_Columns Columns
, string SaveFileName = ""
, Excel.XlFileFormat FileFormat = Excel.XlFileFormat.xlExcel5
, string Title = "")
{
Excel.Application Obj_Excel = new Excel.Application();
Excel.Workbook owbook = Obj_Excel.Workbooks.Add();
Excel.Worksheet owsheet = owbook.Worksheets.Add();
Int32 RowCt = 2;
Int32 ColCt = 1;
foreach (ClsExcel_Columns.Str_Columns? Obj in Columns.pObj)
{
owsheet.Cells[RowCt, ColCt].Value = Obj.Value.FieldDesc;
owsheet.Cells[RowCt, ColCt].Font.Bold = true;
Excel.Range Inner_ExRange =
owsheet.Range[
Do_Methods.GenerateChr(ColCt)
+ RowCt.ToString()
+ ":"
+ Do_Methods.GenerateChr(ColCt)
+ (RowCt + Dt.Rows.Count).ToString()];
Inner_ExRange.NumberFormat = Obj.Value.NumberFormat;
ColCt++;
}
RowCt++;
ColCt = 1;
Excel.Range ExRange =
owsheet.Range[
Do_Methods.GenerateChr(ColCt)
+ RowCt.ToString()
+ ":"
+ Do_Methods.GenerateChr(ColCt + Columns.pObj.Count)
+ (RowCt + Dt.Rows.Count - 1).ToString()];
ExRange.Value = Do_Methods.ConvertDataTo2DimArray(Dt, Columns.pFieldName);
owsheet.Range["A1;IV65536"].AutoFit();
if (SaveFileName == "")
{ SaveFileName = "Excel_File"; }
owsheet.Range["A1:A1"].Select();
owbook.SaveAs(SaveFileName, FileFormat);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xl = new Excel.Application();
xl.Visible = true;
xl.Workbooks.Add("c:\\Pasta1.xlt");
xl.Cells[3, 4] = "oi";
object x = new object();
xl.Workbooks[0].Close(false, "", x);
xl.Quit();
}
public ExcelControl()
{
// This call is required by the Windows.Forms Form Designer.
InitializeComponent();
// TODO: Add any initialization after the InitComponent call
try
{
excelApp = new Excel.ApplicationClass();
excelApp.Visible = true;
Excel.Workbook book = excelApp.Workbooks.Open(@"C:\Documents and Settings\raghunandanr\Desktop\Benefit Design Modeler V3.28_ Blank.xls",Type.Missing,false,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
this.CreateGraphics().DrawString("ok ok",new System.Drawing.Font("Arial",25),Brushes.GreenYellow,10,10);
book.Close(false,Type.Missing,Type.Missing);
}
catch(Exception ex)
{
this.CreateGraphics().DrawString(ex.Message,new System.Drawing.Font("Arial",16),Brushes.Chocolate,10,10);
}
}
/// <summary>退出Excel</summary>
public static void CloseExcel()
{
try
{
if (ExApp != null)
{
ExApp.Quit();
}
}
catch (Exception ex1)
{
//强制把excel进程退出，关闭时，会出异常
}
finally
{
ExApp = null;
}
}
public static void ExportToExcel(DataTable dt, string type, string title, string thoiGian)
{
//Export to excel
try
{
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
System.Data.DataTable table = dt;
excel.Cells[1, 1] = "Print Date :" + DateTime.Now.ToLongDateString();
excel.Cells[3, 1] = title;
excel.Cells[4, 1] = thoiGian;
for (int k = 0; k < table.Columns.Count; k++)
{
excel.Cells[6, k + 1] = table.Columns[k].ColumnName.ToString();
}
int ColumnIndex = 0;
int rowIndex = 5;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
((Excel.Range)excel.Cells[rowIndex + 1, ColumnIndex]).NumberFormat = "@"; //Format dạng text
//excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName]; //OLD 2016-01-04 (3)
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName].ToString(); //2016-01-04 (3)
//((Excel.Range)excel.Cells[rowIndex + 1, ColumnIndex]).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);
}
}
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
catch (Exception exml)
{
MessageBox.Show(exml.Message);
}
}
public void Upload()
{
string strPathnew = Server.MapPath("~/") + "UploadFile\\AcrTestfinalresult.xls";
try
{
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlWorkBook = new Excel.Application().Workbooks.Add(Missing.Value);
xlWorkBook.Application.Visible = true;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
string strPath = Server.MapPath("~/") + "UploadFile\\AcrTestfinal.xls";
StreamReader sr = new StreamReader(strPath); //Read the Excel Stream
string strTest = "";
int i = 1;
while (!sr.EndOfStream)
{
strTest = sr.ReadLine();
string[] strData = strTest.Split('\t');
int count = strData.Length;
for (int k = 1; k <= count; k++)
{
string str = strData[k - 1].Replace("\"", "");
if (k == 4) //1 based index of Column required to be changed
{
str = str.Insert(0, "'");
}
xlWorkSheet.Cells[i, k] = str;
}
i++;
}
sr.Close();
sr.Dispose();
xlWorkSheet.Columns.AutoFit();
xlWorkBook.SaveAs(strPathnew , Excel.XlFileFormat.xlExcel4, Missing.Value, Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlWorkBook.Close(Missing.Value, strPathnew, Missing.Value);
}
catch
{ }
}
/// <summary>
/// Export DataList To Excel File
/// </summary>
/// <param name="dataList">List of Data need export</param>
/// <returns></returns>
public bool ToExcel(List<ArrayList> dataList)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(_temp_excel_file_path);
Excel.Worksheet xlWorkSheet = xlWorkBook.Worksheets.get_Item(_sheet_number);
//SetValue and Fortmat base on template file
SetCells(xlWorkSheet, dataList);
//Save as new file
xlWorkSheet.SaveAs(_save_as_export_file_path);
//Dispose obj
xlWorkBook.Close();
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlWorkSheet = null;
xlWorkBook = null;
xlApp = null;
return true;
}
public void RealtimeMorningPrint(DataSet dsRealtimeMorning,string savePath)
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\RealtimeMorningInfo.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
//获取第一个打印页
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
for ( int row = 0; row < dsRealtimeMorning.Tables[0].Rows.Count; row ++ )
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 7, 1], (Excel.Range)m_objSheet.Cells[row + 7, 1]);
m_objRange.Value = dsRealtimeMorning.Tables[0].Rows[row][1].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
for ( int column = 3;column < dsRealtimeMorning.Tables[0].Columns.Count; column ++)
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 7, column - 1], (Excel.Range)m_objSheet.Cells[row + 7, column - 1]);
m_objRange.set_Item(1,1,dsRealtimeMorning.Tables[0].Rows[row][column].ToString());
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
}
if ( row == dsRealtimeMorning.Tables[0].Rows.Count - 1 )
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 1], (Excel.Range)m_objSheet.Cells[row + 9, 1]);
m_objRange.Value = "园所信息：";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 2], (Excel.Range)m_objSheet.Cells[row + 9, 2]);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 1], (Excel.Range)m_objSheet.Cells[row + 10, 1]);
m_objRange.Value = "统计日期：";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 2], (Excel.Range)m_objSheet.Cells[row + 10, 2]);
m_objRange.Value = DateTime.Now.ToString("yyyy-MM-dd");
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
}
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
private void ExportExcel(string type)
{
Excel._Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "Group";// [Dòng, cột]
excel.Cells[1, 2] = "Account ";
excel.Cells[1, 3] = "Ccy_cd";
excel.Cells[1, 4] = "Amount ";
excel.Cells[1, 5] = "Branch_code";
excel.Cells[1, 6] = "Account_Branch ";
excel.Cells[1, 7] = "Dr_Cr";
excel.Cells[1, 8] = "Ex_rate ";
excel.Cells[1, 9] = "Trn_dt";
excel.Cells[1, 10] = "Description ";
//excel.Cells[1, 1] = "Tại thời điểm " + dtTo.Text;
//((Excel.Range)excel.Cells[1, 1]).Font.Bold = true;
string sql = "", err = "";
if (Frm_main.admin_flag)
{
sql = "select a.group_id,''''||a.account_number as account_number,a.ccy_cd,a.amount_accounting,''''||a.branch_code as branch_code" +
" ,''''||a.account_branch as account_branch,a.dr_cr,a.ex_rate,to_char(a.trn_dt,'mm/dd/yyyy') as trn_dt,a.description,a.user_create,a.accounting_type " +
" from nab_accounting a " +
" where trunc(a.date_proccessing)= trunc(to_date('" + dp_Date_Process.Text + "','dd/mm/yyyy'))";
}
else
{
sql = "select a.group_id,''''||substr(a.account_number,1,4)||'xxxxxxxx'||substr(a.account_number,-4) as account_number,a.ccy_cd,a.amount_accounting,''''||a.branch_code as branch_code" +
" ,''''||a.account_branch as account_branch,a.dr_cr,a.ex_rate,to_char(a.trn_dt,'mm/dd/yyyy') as trn_dt,a.description,a.user_create,a.accounting_type " +
" from nab_accounting a " +
" where trunc(a.date_proccessing)= trunc(to_date('" + dp_Date_Process.Text + "','dd/mm/yyyy'))";
}
if (type == "ACCOUNTING_TRANSACTION")
{
sql += " and upper(trim(a.accounting_type))=upper(trim('" + type + "'))";
sql += " and upper(trim(a.accounting_type)) not in ( upper(trim('ACCOUNTING_TRANSACTION_INT')),upper(trim('ACCOUNTING_IBPS_DEBIT')))";
//string sqlString = "ACCOUNTING_IBPS_DEBIT";
sql += " and a.file_id='2'";
}else
if (type == "ACCOUNTING_ANNUALFEE")
{
sql += " and upper(trim(a.accounting_type))=upper(trim('" + type + "'))";
sql += " and upper(trim(a.accounting_type)) not in ( upper(trim('ACCOUNTING_TRANSACTION_INT')),upper(trim('ACCOUNTING_IBPS_DEBIT')))";
//string sqlString = "ACCOUNTING_IBPS_DEBIT";
sql += " and a.file_id='8'";
}
else
{
sql += " and upper(trim(a.accounting_type))=upper(trim('" + type + "'))";
}
sql += " order by a.group_id asc";
DataTable dtExport = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
this.Cursor = Cursors.Default;
return;
}
else
{
if (dtExport.Rows.Count > 0)
{
for (int i = 0; i < dtExport.Rows.Count; i++)
{
string Group = "", Account = "", Ccy_cd = "", Amount = "", Branch_code = "",
Account_Branch = "", Dr_Cr = "", Ex_rate = "", Trn_dt = "", Description = "";
//a.group_id,a.account_number,a.ccy_cd,a.amount_accounting,a.branch_code "+
//" ,a.account_branch,a.dr_cr,a.ex_rate,a.trn_dt,a.description,a.user_create,a.accounting_type "+
Group = dtExport.Rows[i]["group_id"].ToString();
Account = dtExport.Rows[i]["account_number"].ToString();
Ccy_cd = dtExport.Rows[i]["ccy_cd"].ToString();
Amount = dtExport.Rows[i]["amount_accounting"].ToString();
Branch_code = dtExport.Rows[i]["branch_code"].ToString().Trim();
Account_Branch = dtExport.Rows[i]["account_branch"].ToString().Trim();
Dr_Cr = dtExport.Rows[i]["dr_cr"].ToString();
Ex_rate = dtExport.Rows[i]["ex_rate"].ToString();
Trn_dt = dtExport.Rows[i]["trn_dt"].ToString();
Description = dtExport.Rows[i]["description"].ToString();
//Export excel
excel.Cells[i + 2, 1] = Group;// [Dòng, cột]
excel.Cells[i + 2, 2] = Account;
excel.Cells[i + 2, 3] = Ccy_cd;
excel.Cells[i + 2, 4] = Amount;
excel.Cells[i + 2, 5] = Branch_code;
excel.Cells[i + 2, 6] = Account_Branch;
excel.Cells[i + 2, 7] = Dr_Cr;
excel.Cells[i + 2, 8] = Ex_rate;
excel.Cells[i + 2, 9] = Trn_dt;
excel.Cells[i + 2, 10] = Description;
}
}
}
//----------------
excel.Visible = true;
Excel._Worksheet worksheet = (Excel._Worksheet)excel.ActiveSheet;
worksheet.Activate();
// worksheet.SaveAs("D:\\");
}
public void crearExcelReporteInsumos()
{
ControlInsumos.DAL.ArticuloDal artDal = new ControlInsumos.DAL.ArticuloDal();
int veces = artDal.countArt();
string desde = cboxAñoDesde.Text + "-" + mesDesde() + "-" + cboxDesde.Text;
string hasta = cboxAñoDesde.Text + "-" + mesHasta() + "-" + cboxHasta.Text;
string nombreArchivo = "Reporte Insumos." + DateTime.Now.ToShortDateString();
try
{
// creating Excel Application
Excel._Application app = new Excel.Application();
object misValue = System.Reflection.Missing.Value;
// creating new WorkBook within Excel application
Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
for (int r = 1; r <= veces; r++)
{
string nombreArticulo = artDal.nombreArticulo(r - 1);
dgvInformes.DataSource = b.SelectDataTable(insumosDal.reporteInsumos(r, nombreArticulo, desde, hasta));
worksheet = (Excel._Worksheet)workbook.Sheets["hoja" + r];
//worksheet = (Excel._Worksheet)workbook.ActiveSheet;
// changing the name of active sheet
if (r == 1)
{
worksheet.Name = "Resumen Insumos";
}
else
{
worksheet.Name = nombreArticulo;
}
// storing header part in Excel
for (int i = 1; i < dgvInformes.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dgvInformes.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dgvInformes.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvInformes.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgvInformes.Rows[i].Cells[j].Value.ToString();
}
}
}
// save the application
workbook.SaveAs(nombreArchivo + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbook.Close(true, misValue, misValue);
MessageBox.Show("El archivo: " + nombreArchivo + ", se ha guardado en Mis Documentos", "Informes", MessageBoxButtons.OK, MessageBoxIcon.Information);
// Exit from the application
app.Quit();
}
catch (Exception e)
{
}
}
public void FinanceStatPrint(DataTable data, string className, DateTime date, string savePath)
{
DataTable dtPresents = new FinanInfoDataAccess().GetStudentPresents(date, className);
if (dtPresents == null || dtPresents.Rows.Count == 0)
{
throw new Exception("没有要使用的数据！");
}
else
{
data.Columns.AddRange(new DataColumn[]{ new DataColumn("小计", Type.GetType("System.Double")),
new DataColumn("stuPresent", Type.GetType("System.String")),
new DataColumn("stuAbsent", Type.GetType("System.String"))});
if (dtPresents.Rows.Count != data.Rows.Count)
{
throw new Exception("检测到数据完整性错误，请尝试重新生成数据！");
}
else
{
for (int i = 0; i < dtPresents.Rows.Count; i++)
{
data.Rows[i]["小计"] = 0;
data.Rows[i]["stuPresent"] = dtPresents.Rows[i]["times"];
data.Rows[i]["stuAbsent"] = dtPresents.Rows[i]["times_abs"];
}
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\FinanceStat.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = new object[data.Rows.Count + 1, data.Columns.Count + 4];
for(int row = 0; row < data.Rows.Count; row++)
{
objData[row, 0] = row + 1;
for (int column = 0; column < data.Columns.Count - 3; column++)
{
if (column <= 2)
{
objData[row, column + 1] = data.Rows[row][column];
}
else if(column == 3)
{
objData[row, column + 1] = data.Rows[row][data.Columns.Count - 2];
objData[row, column + 2] = data.Rows[row][data.Columns.Count - 1];
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column >= 4 && column < 7)
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column == 7)
{
double temp1 = Convert.ToDouble(data.Rows[row][column - 4]) +
Convert.ToDouble(data.Rows[row][column - 3]) + Convert.ToDouble(data.Rows[row][column - 2]) +
Convert.ToDouble(data.Rows[row][column - 1]);
objData[row, column + 3] = temp1;
double temp2 = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp2;
objData[data.Rows.Count, column + 3] = temp1 + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
objData[data.Rows.Count, column + 4] = temp2 + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
else
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp;
objData[data.Rows.Count, column + 4] = temp + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
}
}
m_objRange = m_objSheet.get_Range("A6", m_objOpt);
m_objRange = m_objRange.get_Resize(data.Rows.Count + 1, data.Columns.Count + 1);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
m_objRange = m_objSheet.get_Range("G3", m_objOpt);
m_objRange.Value = "各项费用";
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 7], m_objSheet.Cells[4, data.Columns.Count]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 12;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, data.Columns.Count + 1], m_objSheet.Cells[4, data.Columns.Count + 1]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
object[, ] objColumn = new object[1, data.Columns.Count - 5];
for (int column = 3; column < data.Columns.Count - 3; column++ )
{
if (column < 7)
{
objColumn[0, column - 3] = data.Columns[column].ColumnName;
}
if (column == 7)
{
objColumn[0, column - 3] = data.Columns[data.Columns.Count - 3].ColumnName;
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
else
{
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
}
m_objRange = m_objSheet.get_Range("G5", m_objOpt);
m_objRange = m_objRange.get_Resize(1, data.Columns.Count - 5);
m_objRange.Value = objColumn;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10.5;
for (int column = 5; column < data.Columns.Count - 3; column++)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, column + 2], m_objSheet.Cells[5 + data.Rows.Count, column + 2]);
object i = m_objRange.Calculate();
string s = "asf";
}
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
}
public void FinanMgmtInfoPrint(DataSet dsFinanInfo,string savePath)
{
int rowStart = 6;
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\FinanMgmtInfo.xls",
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 = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Copy(m_objSheet,m_objOpt);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objSheet.Name = dsFinanInfo.Tables[0].Rows[0][2].ToString();
for ( int finanRow = 0;finanRow < dsFinanInfo.Tables[0].Rows.Count; finanRow ++ )
{
if ( !dsFinanInfo.Tables[0].Rows[finanRow][2].ToString().Equals(m_objSheet.Name) )
{
SetJumpBoard(rowStart,dsFinanInfo.Tables[0].Rows[finanRow-1][2].ToString());
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Copy(m_objSheet,m_objOpt);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count-1);
m_objSheet.Name = dsFinanInfo.Tables[0].Rows[finanRow][2].ToString();
rowStart = 6;
}
for ( int column = 1;column < dsFinanInfo.Tables[0].Columns.Count; column ++)
{
switch ( column )
{
case 1: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column],m_objSheet.Cells[rowStart+1,column]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 3: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart+1,column-1]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 4: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart+1,column-1]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 5: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 6: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 7: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 8: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 9: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 10: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 11: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-4],m_objSheet.Cells[rowStart,column-4]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 12: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-5],m_objSheet.Cells[rowStart+1,column-5]);
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case 13: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-5],m_objSheet.Cells[rowStart+1,column-5]);
m_objRange.MergeCells = true;
m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString();
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
}
}
rowStart += 2;
}
SetJumpBoard(rowStart,dsFinanInfo.Tables[0].Rows[dsFinanInfo.Tables[0].Rows.Count-1][2].ToString());
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
private void Dispose()
{
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (range1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
range1 = null;
}
if (range2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
range2 = null;
}
if (textBox != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(textBox);
textBox = null;
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();
this.KillExcelProcess();
}//end Dispose
/// <summary>
/// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径
/// </summary>
/// <param name="templetFilePath">Excel模板文件路径</param>
/// <param name="outputFilePath">输出Excel文件路径</param>
public ExcelHelper(string templetFilePath, string outputFilePath)
{
if (templetFilePath == null)
throw new Exception("Excel模板文件路径不能为空！");
if (outputFilePath == null)
throw new Exception("输出Excel文件路径不能为空！");
if (!File.Exists(templetFilePath))
throw new Exception("指定路径的Excel模板文件不存在！");
this.templetFile = templetFilePath;
this.outputFile = outputFilePath;
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打开模板文件，得到WorkBook对象
workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
/// <summary>
/// 构造函数，打开一个已有的工作簿
/// </summary>
/// <param name="fileName">Excel文件名</param>
public ExcelHelper(string fileName)
{
if (!File.Exists(fileName))
throw new Exception("指定路径的Excel文件不存在！");
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打开一个WorkBook
workBook = app.Workbooks.Open(fileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
/// <summary>
/// 构造函数，新建一个工作簿
/// </summary>
public ExcelHelper()
{
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
//app.Visible = true; 不自动打开
afterTime = DateTime.Now;
//新建一个WorkBook
workBook = app.Workbooks.Add(Type.Missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
public void ReadStuInfoXLS(ref DataSet dsStuInfoXls,string getPath,int classNumbers,ref Hashtable className)
{
OleDbDataAdapter myAdp = null;
OleDbConnection oleConn = null;
string[] sheetName = new string[classNumbers];
DataTable[] stuInfoXlsTable = new DataTable[classNumbers];
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件
getPath,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
for ( int i=0; i<classNumbers; i++ )
{
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(i+1)); //记录工作表名称
sheetName[i] = m_objSheet.Name.Trim();
className.Add(i,sheetName[i]);
}
m_objBooks.Close();
oleConn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=" + getPath //打开驱动器
+ ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
oleConn.Open();
for ( int i=0; i<classNumbers; i++ )
{
stuInfoXlsTable[i] = new DataTable("Table"+i.ToString());
myAdp = new OleDbDataAdapter("select * from ["+sheetName[i]+"$]", oleConn); //获取第一张工作表的内容
myAdp.Fill(stuInfoXlsTable[i]);
dsStuInfoXls.Tables.Add(stuInfoXlsTable[i]);
}
oleConn.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
private void EXCEL_FKMX()
{
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = true;
excel.get_Range("A1", excel.Cells[2, dataGridView1.Columns.Count]).Merge(false);
excel.get_Range("A3", excel.Cells[3, dataGridView1.Columns.Count]).Merge(false);
excel.get_Range("A4", "A6").Merge(false);
excel.get_Range("B4", "c6").Merge(false);
excel.get_Range("D4", "G4").Merge(false);
excel.get_Range("H4", "K4").Merge(false);
excel.get_Range("L4", "M4").Merge(false);
excel.get_Range("N4", "Q4").Merge(false);
excel.get_Range("R4", "S4").Merge(false);
excel.get_Range("E5", "F5").Merge(false);
excel.get_Range("I5", "J5").Merge(false);
excel.get_Range("O5", "P5").Merge(false);
excel.get_Range("D5", "D6").Merge(false);
excel.get_Range("G5", "G6").Merge(false);
excel.get_Range("H5", "H6").Merge(false);
excel.get_Range("K5", "K6").Merge(false);
excel.get_Range("L5", "L6").Merge(false);
excel.get_Range("M5", "M6").Merge(false);
excel.get_Range("N5", "N6").Merge(false);
excel.get_Range("Q5", "Q6").Merge(false);
excel.get_Range("R5", "R6").Merge(false);
excel.get_Range("S5", "S6").Merge(false);
excel.get_Range("T4", "T6").Merge(false);
excel.get_Range("A" + (this.dataGridView1.Rows.Count + 4).ToString(), "B" + (this.dataGridView1.Rows.Count + 6).ToString()).Merge(false);
//(excel.Cells[1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//(excel.Cells[3, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
excel.get_Range("A1", "T6").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
excel.get_Range("A3", "A3").HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//(excel.Cells[5, "m"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//(excel.Cells[5, "g"] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
excel.get_Range("A1", "t6").Font.Bold = true;
excel.Cells[4, "A"] = "序号";
excel.Cells[4, "B"] = "合同种类";
excel.get_Range("A4", "A6").Value = "序号";
excel.get_Range("B4", "c6").Value = "公司名称";
excel.get_Range("D4", "G4").Value = "合同总额";
excel.get_Range("H4", "K4").Value = (this.toolStripComboBox4.Text == "0201:产品销售" ? "已收货款" : "已付货款");
excel.get_Range("L4", "M4").Value = (this.toolStripComboBox4.Text == "0201:产品销售" ? "未收货款" : "未付货款");
excel.get_Range("N4", "Q4").Value = (this.toolStripComboBox4.Text == "0201:产品销售" ? "已开发票金额" : "已收发票金额");
excel.get_Range("R4", "S4").Value = (this.toolStripComboBox4.Text == "0201:产品销售" ? "未开票金额" : "未收票金额");
excel.get_Range("E5", "F5").Value = "本年";
excel.get_Range("I5", "J5").Value = "本年";
excel.get_Range("O5", "P5").Value = "本年";
excel.get_Range("D5", "D6").Value = "以前年度";
excel.get_Range("G5", "G6").Value = "总累计";
excel.get_Range("H5", "H6").Value = "以前年度";
excel.get_Range("K5", "K6").Value = "总累计";
excel.get_Range("L5", "L6").Value = "金额";
excel.get_Range("M5", "M6").Value = "比例";
excel.get_Range("N5", "N6").Value = "以前年度";
excel.get_Range("Q5", "Q6").Value = "总累计";
excel.get_Range("R5", "R6").Value = "金额";
excel.get_Range("S5", "S6").Value = "比例";
excel.get_Range("T4", "T6").Value = "备注";
excel.get_Range("E6", "E6").Value = "本月";
excel.get_Range("F6", "F6").Value = "本年";
excel.get_Range("I6", "I6").Value = "本月";
excel.get_Range("J6", "J6").Value = "本年";
excel.get_Range("O6", "O6").Value = "本月";
excel.get_Range("P6", "P6").Value = "本年";
excel.get_Range("A" + (this.dataGridView1.Rows.Count + 4).ToString(), "B" + (this.dataGridView1.Rows.Count + 4).ToString()).Value = "总计";
excel.get_Range("c" + (this.dataGridView1.Rows.Count + 4).ToString(), "c" + (this.dataGridView1.Rows.Count + 4).ToString()).Value = "外部";
excel.get_Range("c" + (this.dataGridView1.Rows.Count + 5).ToString(), "c" + (this.dataGridView1.Rows.Count + 5).ToString()).Value = "内部";
excel.get_Range("c" + (this.dataGridView1.Rows.Count + 6).ToString(), "c" + (this.dataGridView1.Rows.Count + 6).ToString()).Value = "合计";
excel.get_Range("a1", "t2").Value = ClassCustom.codeSub1(this.toolStripComboBox4.Text) + "合同总览表";
excel.get_Range("a3", "a3").Value = this.toolStripComboBox4.Text + " " + this.toolStripComboBox1.Text + "年" + this.toolStripComboBox2.Text + "月";
//DataTable dt = DBAdo.DtFillSql("SELECT LID,LNAME FROM ALX WHERE LID LIKE '" + ClassCustom.codeSub(this.toolStripComboBox4.Text) + "__'");
string str = ClassCustom.codeSub(this.toolStripComboBox4.Text);
DataTable dt = DBAdo.DtFillSql("SELECT CCODE,CNAME FROM ACLIENTS WHERE CCODE LIKE '01__'");
int index = 0;
for (int i = 0; i < dt.Rows.Count * 2; i++)
{
excel.get_Range("B" + (i + 7).ToString(), "B" + (i + 8).ToString()).Merge(false);
excel.get_Range("B" + (i + 7).ToString(), "B" + (i + 8).ToString()).Value = dt.Rows[index][1].ToString();
excel.get_Range("A" + (i + 7).ToString(), "A" + (i + 8).ToString()).Merge(false);
excel.get_Range("A" + (i + 7).ToString(), "A" + (i + 8).ToString()).Value = (index + 1).ToString();
excel.get_Range("C" + (i + 7).ToString(), "C" + (i + 7).ToString()).Value = "外部";
excel.get_Range("C" + (i + 8).ToString(), "C" + (i + 8).ToString()).Value = "内部";
index++;
i++;
}
excel.get_Range("d7", excel.Cells[dataGridView1.Rows.Count + 6, "t"]).NumberFormat = "#,##0.00";
excel.get_Range("m7", excel.Cells[dataGridView1.Rows.Count + 6, "m"]).NumberFormat = "0%";
excel.get_Range("s7", excel.Cells[dataGridView1.Rows.Count + 6, "s"]).NumberFormat = "0%";
//填充数据
for (int i = 0; i < dataGridView1.RowCount; i++)
{
for (int j = 3; j < dataGridView1.ColumnCount; j++)
{
if (dataGridView1[j, i].ValueType == typeof(string))
{
excel.Cells[i + 7, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
}
else
{
excel.Cells[i + 7, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
}
excel.get_Range("A1", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]).EntireColumn.AutoFit();
ClassCustom.DrawExcelBorders(excel, "A4", excel.Cells[dataGridView1.Rows.Count + 6, dataGridView1.Columns.Count]);
Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet;
sheet1.PageSetup.PrintTitleRows = "$1:$6";
//sheet1.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperFanfoldUS;
}
public void ReadUpdateGradeInfo(ref DataTable dtUpdateGradeInfo,string getPath)
{
OleDbDataAdapter myAdp = null;
OleDbConnection oleConn = null;
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件
getPath,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
oleConn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=" + getPath //打开驱动器
+ ";Extended Properties=Excel 8.0");
oleConn.Open();
myAdp = new OleDbDataAdapter("select * from [年班升级$]", oleConn); //获取第一张工作表的内容
myAdp.Fill(dtUpdateGradeInfo);
oleConn.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void WriteStuCardInfoXLS(DataSet dsStuInfo,string getPath)
{
KillProcess();
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件
getPath,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = null;
if ( dsStuInfo.Tables[0].Rows.Count > 0 )
{
objData = new object[dsStuInfo.Tables[0].Rows.Count,2];
for ( int i=0; i<dsStuInfo.Tables[0].Rows.Count; i++ )
{
objData[i,0] = dsStuInfo.Tables[0].Rows[i][0].ToString();
objData[i,1] = dsStuInfo.Tables[0].Rows[i][1].ToString();
}
}
m_objRange = m_objSheet.get_Range("A2",m_objOpt);
m_objRange = m_objRange.get_Resize(dsStuInfo.Tables[0].Rows.Count,2);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.Font.Size = 10;
m_objExcel.Visible = true;
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
}
}
/// <summary>
/// Erzeugt die Listen mit den Objekten aus der Anmeldung.
/// </summary>
public void Generate()
{
// Variablen initialisieren
judoka = new List<Judoka>();
verein = new Verein();
// Ko-Kriterien
if (!System.IO.File.Exists(fileName)) return;
Excel.Application excel = null;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
try
{
// Worksheet Initialisieren
excel = new Excel.Application();
workbook = excel.Workbooks.Open(fileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, null, false, false, false);
worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
// Verein auslesen
verein.Name = GetSheetValue(worksheet, "B2");
verein.Kurzname = GetSheetValue(worksheet, "B2");
verein.Email = GetSheetValue(worksheet, "B7");
System.Text.StringBuilder sb = new StringBuilder();
sb.AppendLine(GetSheetValue(worksheet, "B3"));
sb.AppendLine(GetSheetValue(worksheet, "B4"));
sb.AppendLine(GetSheetValue(worksheet, "B5"));
sb.AppendLine(GetSheetValue(worksheet, "B6"));
verein.Adresse = sb.ToString();
// Kämpfer auslesen
int counter = 1;
while (HasSheetJudoka(counter, worksheet))
{
Judoka j = new Judoka();
j.Vorname = GetSheetValue(worksheet, GetJudokaCell(counter, "C"));
j.Nachname = GetSheetValue(worksheet, GetJudokaCell(counter, "B"));
try
{
j.Geburtsdatum = DateTime.FromOADate(double.Parse(GetSheetValue(worksheet, GetJudokaCell(counter, "F"))));
}
catch (Exception)
{
j.Geburtsdatum = new DateTime(1900, 1, 1);
}
j.Gürtel = new Gürtel(GetSheetValue(worksheet, GetJudokaCell(counter, "D")), "9");
j.Geschlecht = new Geschlecht(GetSheetValue(worksheet, GetJudokaCell(counter, "E")));
j.Verein = verein;
judoka.Add(j);
counter++;
}
}
catch (Exception)
{
throw;
}
finally
{
// Excel beenden
workbook.Close(false, null, 0);
workbook = null;
excel.Quit();
excel = null;
System.GC.WaitForPendingFinalizers();
}
}
