/// <summary>
/// 利用Excel的QueryTable导出数据
/// </summary>
/// <param name="intSheetNumber">导出第几个sheet</param>
/// <param name="blIsMoreThan">余下的数据是否大于指定的每个Sheet的最大记录数</param>
/// <param name="strTitle">表头,需与查询sql语句对齐一致。</param>
/// <param name="strSql">查询的sql语句，表头的文字需与该sql语句对齐一致。</param>
/// <param name="strTablName">查询的表名</param>
/// <param name="strMastTitle">主标题</param>
/// </summary>
public void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan, string[] strTitle, string strSql, string strTablName, string strMastTitle)
{
string strQuery = string.Empty;
if (blIsMoreThan)
{
strQuery = "Select Top " +
this.dbSheetSize + strSql + " From " + strTablName + " Where Not OrderID In (Select Top " +
dbSheetSize * (intSheetNumber - 1) + " OrderID From " + strTablName + ")";
}
else
{
strQuery = "Select Top " + this.dbSheetSize + strSql + " From " + strTablName;
}
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber));
m_objSheet.Cells[1, 1] = strMastTitle;
m_objSheet.Cells[2, 1] = "打印日期" + DateTime.Now.ToShortDateString();
for (int i = 1; i <= strTitle.Length; i++)
{
m_objSheet.Cells[4, i] = strTitle[i - 1].ToString();
}
m_objRange = m_objSheet.get_Range("A5", m_objOpt);
m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + sqlConn.ConnectionString, m_objRange, strQuery);
m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
m_objQryTable.FieldNames = false;
m_objQryTable.Refresh(false);
}
/// <summary>
/// Add sheet
/// </summary>
public void AddSheet()
{
this.sheetIndex++;
if (this.CurBook.Sheets.Count < sheetIndex)
{
this.CurSheet = (Excel.Worksheet)this.CurBook.Sheets.Add(mValue, this.CurBook.Sheets[sheetIndex - 1], 1, Excel.XlSheetType.xlWorksheet);
}
else
{
this.CurSheet = this.CurExcel.Worksheets[sheetIndex] as Excel.Worksheet;
}
pictureIndex = 0;
}
private void Dispose(bool disposing)
{
if (disposing)
{
// Dispose managed resources.
Marshal.FinalReleaseComObject(m_objExcel);
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBooks = null;
m_objBook = null;
m_objExcel = null;
}
}
public ExcelController(bool excelVisible, IReport report)
{
dtBefore = System.DateTime.Now;
CurExcel = new Excel.ApplicationClass();
CurExcel.DisplayAlerts = false;
dtAfter = System.DateTime.Now;
CurExcel.Visible = excelVisible;
//CurExcel.Workbooks.Add(true);
CurExcel.Workbooks.Add(mValue);
sheetIndex = 1;
CurSheet = CurExcel.Worksheets[sheetIndex] as Excel.Worksheet;
CurBook = CurExcel.Workbooks[1];
pictureIndex = 0;
log = new EasilyReportLog("Excel Report", this.GetType().FullName, LogFileInfo.logFileName, report);
}
private void exp_excel()
{
m.check_process_Excel();
dsxml.Tables[0].Columns.Remove("MAQL");
int be = 5, dong = 6, sodong = dsxml.Tables[0].Rows.Count + dong, socot = dsxml.Tables[0].Columns.Count - 1, dongke = sodong - 1;
string tenfile = m.Export_Excel(dsxml, "sokhambenh");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be - 1; i++)
{
osheet.get_Range(m.getIndex(i) + "1", m.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(m.getIndex(0) + "4", m.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 0; i < 11; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i);
if (i != 3 && i != 4 && i != 8 && i != 9)
{
orange = osheet.get_Range(m.getIndex(i) + "4", m.getIndex(i) + "5");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
orange.VerticalAlignment = XlHAlign.xlHAlignCenter;
orange.MergeCells = true;
}
}
osheet.Cells[dong - 2, 4] = "Tuổi";
osheet.get_Range(m.getIndex(3) + "4", m.getIndex(4) + "4").HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
osheet.Cells[dong - 2, 9] = "Chẩn đoán";
osheet.get_Range(m.getIndex(8) + "4", m.getIndex(9) + "4").HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
osheet.Cells[dong - 2, 12] = "Xử trí";
int p = 12;
foreach (DataRow r in dsxt.Tables[0].Select("true", "ma"))
{
osheet.Cells[dong - 1, p++] = r["ten"].ToString();
}
osheet.get_Range(m.getIndex(11) + "4", m.getIndex(p - 1) + "4").HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange = osheet.get_Range(m.getIndex(11) + "5", m.getIndex(p - 2) + "5");
orange.VerticalAlignment = XlVAlign.xlVAlignBottom;
orange.Orientation = 90;
osheet.Cells[dong - 1, p] = "Bác sỹ";
orange = osheet.get_Range(m.getIndex(p - 1) + "4", m.getIndex(p - 1) + "5");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
orange.VerticalAlignment = XlHAlign.xlHAlignCenter;
orange.MergeCells = true;
p += 1;
int q = p;
osheet.Cells[dong - 2, p] = "Đối tượng";
foreach (DataRow r in dsdt.Tables[0].Select("true", "ma"))
{
osheet.Cells[dong - 1, p++] = r["ten"].ToString();
}
osheet.get_Range(m.getIndex(q - 1) + "4", m.getIndex(p - 1) + "4").HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange = osheet.get_Range(m.getIndex(q - 1) + "5", m.getIndex(p - 2) + "5");
orange.VerticalAlignment = XlVAlign.xlVAlignBottom;
orange.Orientation = 90;
osheet.Cells[dong - 1, p] = "Cấp cứu";
orange = osheet.get_Range(m.getIndex(p - 1) + "4", m.getIndex(p - 1) + "5");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
orange.VerticalAlignment = XlHAlign.xlHAlignCenter;
orange.MergeCells = true;
orange = osheet.get_Range(m.getIndex(0) + "4", m.getIndex(socot) + dongke.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 10;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.Cells[1, 1] = m.Syte; osheet.Cells[2, 1] = m.Tenbv;
osheet.Cells[2, 4] = "SỔ KHÁM BỆNH (PHÒNG LƯU)" + ((tu.Text == den.Text)?"Ngày " + tu.Text:"Từ ngày " + tu.Text + " đến " + den.Text);
orange = osheet.get_Range(m.getIndex(1) + "2", m.getIndex(socot - 1) + "3");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
oxl.Visible = true;
}
private void exp_excel(bool print)
{
d.check_process_Excel();
try
{
int be = 3, dong = 5, sodong = dsxml.Tables[0].Rows.Count + 5, socot = dsxml.Tables[0].Columns.Count - 1, dongke = sodong;
tenfile = d.Export_Excel(dsxml, "bccpkcb");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));//,Missing.Value,Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value); //,Missing.Value);
}
osheet.get_Range(d.getIndex(i_cot) + dong.ToString(), d.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 0; i < i_cot; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i).ToString();
}
osheet.Cells[dong - 1, socot + 1] = "TỔNG CỘNG";
osheet.Cells[sodong, i_cot - 1] = "TỔNG CỘNG";
orange = osheet.get_Range(d.getIndex(0) + "4", d.getIndex(1) + "4");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
int so = sodong, j = 1;
decimal tc = 0;
foreach (DataRow r in dtnhomvp.Select("true", "stt"))
{
osheet.Cells[dong - 1, j + i_cot] = r["ten"].ToString();
orange = osheet.get_Range(d.getIndex(j + i_cot) + "3", d.getIndex(j + i_cot) + "3");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
osheet.Cells[sodong, j + i_cot] = r["tc"].ToString();
osheet.get_Range(d.getIndex(j + i_cot - 1) + so.ToString(), d.getIndex(j + i_cot - 1) + so.ToString()).Font.Bold = true;
tc += decimal.Parse(r["tc"].ToString());
j++;
}
osheet.Cells[sodong, socot + 1] = tc;
osheet.get_Range(d.getIndex(socot) + so.ToString(), d.getIndex(socot) + so.ToString()).Font.Bold = true;
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 1] = d.Syte; osheet.Cells[2, 1] = d.Tenbv;
orange = osheet.get_Range(d.getIndex(1) + "1", d.getIndex(3) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
osheet.Cells[1, 3] = "BÁO CÁO CHI PHÍ KHÁM CHỮA BỆNH " + madoituong.Text.Trim().ToUpper() + " NGỌAI TRÚ";
osheet.Cells[2, 3] = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày :" + tu.Text + " đến ngày :" + den.Text;
orange = osheet.get_Range(d.getIndex(2) + "1", d.getIndex(socot) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exp_excelCanLoc(bool print)
{
try
{
d.check_process_Excel();
int be = 3, cot = ds.Tables[0].Columns.Count, dong = ds.Tables[0].Rows.Count + be + 2;
DataColumn dc = new DataColumn();
dc.ColumnName = "ghichu";
dc.DataType = Type.GetType("System.String");
ds.Tables[0].Columns.Add(dc);
DataRow drstt = ds.Tables[0].NewRow();
for (int i = 0; i < cot + 1; i++)
{
drstt[i] = i.ToString();
}
ds.Tables[0].Rows.InsertAt(drstt, 0);
tenfile = d.Export_Excel(ds, "ylenh");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
for (int i = 0; i < 7; i++)
{
osheet.Cells[be + 1, i + 1] = get_ten(i);
}
for (int i = 0; i < dsmabd.Tables[0].Rows.Count; i++)
{
osheet.Cells[be + 1, i + 8] = dsmabd.Tables[0].Rows[i]["tenbd"].ToString();
osheet.Cells[dong + 1, i + 8] = dsmabd.Tables[0].Rows[i]["soluong"].ToString();
}
osheet.Cells[4, cot + 1] = "Ghi chú";
orange = osheet.get_Range(d.getIndex(be) + "4", d.getIndex(cot) + "4");
orange.VerticalAlignment = XlVAlign.xlVAlignBottom;
orange.Orientation = 90;
orange.RowHeight = 200;
orange.EntireRow.AutoFit();
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(cot + 10) + dong + 10);
orange.Font.Name = "Arial";
orange.Font.Size = 10;
orange.Font.Bold = false;
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(cot) + (dong + 1)).Borders.LineStyle = XlBorderWeight.xlHairline;
//orange.EntireRow.AutoFit();
orange.EntireColumn.AutoFit();
orange = osheet.get_Range(d.getIndex(cot) + "1", d.getIndex(cot + 100) + dong + 10);
orange.Cells.ColumnWidth = 3;
//to mau tu cot thu 6
for (int i = 7; i < cot; i++)
{
if (i % 2 == 0)
{
orange = osheet.get_Range(d.getIndex(i) + "4", d.getIndex(i) + dong.ToString());
orange.Interior.Color = System.Drawing.Color.Beige.ToArgb();
}
}
//
osheet.get_Range(d.getIndex(4) + (dong + 1), d.getIndex(cot - 1) + (dong + 1)).Font.Bold = true;
oxl.ActiveWindow.DisplayZeros = false;
osheet.Cells[1, 2] = d.Tenbv;
osheet.Cells[2, 2] = (tu.Text == den.Text) ? "Ngày " + tu.Text : "Ngày " + tu.Text + " - " + den.Text;
osheet.Cells[1, 4] = "TỔNG HỢP Y LỆNH";
orange = osheet.get_Range(d.getIndex(2) + "1", d.getIndex(cot - 1) + "1");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
osheet.Cells[dong + 4, 26] = "In ngày " + System.DateTime.Today.Day.ToString() + " tháng " + System.DateTime.Today.Month.ToString() + " năm " + System.DateTime.Today.Year.ToString();
osheet.Cells[dong + 5, 2] = "Khoa Dược";
osheet.Cells[dong + 5, 7] = "Bác Sĩ";
osheet.Cells[dong + 5, 15] = "Điều dưỡng thực hiện";
osheet.Cells[dong + 5, 26] = "Người sao y lệnh";
osheet.Cells[dong + 8, 2] = "Họ tên...................";
osheet.Cells[dong + 8, 7] = "Họ tên...................";
osheet.Cells[dong + 8, 15] = "Họ tên...................";
osheet.Cells[dong + 8, 26] = "Họ tên...................";
//orange = osheet.get_Range(d.getIndex(0) + dong+4, d.getIndex(cot) + dong+4);
//orange.Font.Name = "Arial";
//orange.Font.Size = 8;
//orange.Font.Bold = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//osheet.PrintPreview(Missing.Value);
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/*<summary>
* 将DataTable中的数据导出到Excel中，并在服务器端AppData文件夹中生成xls文件
* </summary>
* <param name="dt">要导出数据的DataTable</param>
* <param name="head">题头数据</param>
* <param name="absFileName">文件的绝对路径</param>
* <returns></returns>
*/
public static void ExportToExcelDiagramfor9(System.Data.DataTable dt, string[] head, string absFileName)
{
//设置多少行为一个Sheet
int RowsToDivideSheet = 65535;
//计算Sheet数
int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1;
GC.Collect();
string excelTemplateDPath; //EXCEL模板默认服务器物理存放路径
string tempEFilePath = "\\TempFiles"; //EXCEL临时文件保存服务器物理存放路径
string tempEFileXPath = "/TempFiles"; //EXCEL临时文件保存服务器虚拟存放路径
Excel.Application excel = null;
Excel._Workbook xBk = null;
Excel._Worksheet xSt = null;
Excel.Workbooks workbooks; //工作簿集合
Excel.Sheets sheets; //SHEET页集合
#region
//读取配置文件中路线模板路径及名称并验证是否存在 //获取配置文件中路线模板路径及名称
excelTemplateDPath = System.Web.HttpContext.Current.Server.MapPath("~/moban");
tempEFileXPath = "~/moban" + tempEFileXPath;
tempEFilePath = excelTemplateDPath + tempEFilePath;
excelTemplateDPath += "\\TaskTemplet9.xls";
//验证EXCEL临时文件夹是否存在
if (!File.Exists(tempEFilePath))
{
Directory.CreateDirectory(tempEFilePath);
}
#endregion
try
{
#region
//启动excel进程并加载模板
//启动EXCEL进程
excel = new Excel.Application();
excel.Visible = false;
excel.UserControl = true;
excel.DisplayAlerts = false;
//加载读取模板
workbooks = excel.Workbooks;
xBk = workbooks.Add(excelTemplateDPath);
sheets = xBk.Worksheets;
xSt = (Excel._Worksheet)sheets.get_Item(1);
#endregion
//循环中要使用的变量
int dvRowStart;
int dvRowEnd;
//对全部Sheet进行操作
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
//计算起始行
dvRowStart = sheetIndex * RowsToDivideSheet;
dvRowEnd = dvRowStart + RowsToDivideSheet - 1;
if (dvRowEnd > dt.Rows.Count - 1)
{
dvRowEnd = dt.Rows.Count - 1;
}
//创建一个Sheet
if (null == xSt)
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
}
//设置SheetName
xSt.Name = "Excel";
if (sheetCount > 1)
{
xSt.Name += ((int)(sheetIndex + 1)).ToString();
}
//题头导出
int rowCount = head.Length;
int colCount = 1;
object[,] dataArray = new object[colCount, rowCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[j, i] = head[i];
}
}
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).NumberFormatLocal = "@"; //设置单元格格式为文本
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10;
//数据导出
System.Data.DataTable SheetTable = new System.Data.DataTable();
foreach (DataColumn dc in dt.Columns)
{
DataColumn newdc = new DataColumn();
newdc.ColumnName = dc.ColumnName;
newdc.DataType = dc.DataType;
SheetTable.Columns.Add(newdc);
}
for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
{
SheetTable.ImportRow(dt.Rows[drvIndex]);
}
//保存数据
rowCount = SheetTable.Rows.Count;
colCount = SheetTable.Columns.Count;
object[,] dataArray1 = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray1[i, j] = SheetTable.Rows[i][j];
}
}
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).NumberFormatLocal = "0.00_ "; //保留小数位数为2;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10;
//单元格边框
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置居中对齐
xSt.Columns.AutoFit(); //自适应宽度
//设置图标的标题
string Charttitle = dt.Rows[0][0].ToString();
}
//删除Sheet1
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true; //注意一定要加上这句
object objOpt = System.Reflection.Missing.Value;
excel.Visible = false;
xBk.SaveCopyAs(absFileName);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
catch (Exception e)
{
//throw (e);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
}
private void print(bool prn)
{
s_quay = "'";
if (quay.CheckedItems.Count > 0)
{
for (int i = 0; i < quay.Items.Count; i++)
{
if (quay.GetItemChecked(i))
{
s_quay += dtquay.Rows[i]["ma"].ToString() + "','";
}
}
}
string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text;
if (tu.Text == den.Text)
{
s_title = "Ngày " + tu.Text;
}
DateTime dt1 = d.StringToDate(tu.Text).AddDays(-d.iNgaykiemke);
DateTime dt2 = d.StringToDate(den.Text).AddDays(d.iNgaykiemke);
int y1 = dt1.Year, m1 = dt1.Month;
int y2 = dt2.Year, m2 = dt2.Month;
int itu, iden, be = 0;
string mmyy = "";
for (int i = y1; i <= y2; i++)
{
itu = (i == y1)?m1:1;
iden = (i == y2)?m2:12;
for (int j = itu; j <= iden; j++)
{
mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2);
if (d.bMmyy(mmyy))
{
xxx = user + mmyy;
sql = " select a.mabs,sum(b.soluong*b.giaban) as sotien";
sql += " from " + xxx + ".d_ngtrull a," + xxx + ".d_ngtruct b ";
sql += " where a.id=b.id";
if (s_quay.Length > 1)
{
sql += " and a.mabs in (" + s_quay.Substring(0, s_quay.Length - 2) + ")";
}
sql += " and a.ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
sql += " group by a.mabs";
if (be == 0)
{
ds = d.get_data(sql);
}
else
{
ds.Merge(d.get_data(sql));
}
be++;
}
}
}
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), d.Msg);
}
else
{
dsxml.Clear();
DataRow [] dr;
DataRow r1, r2, r3;
decimal tc = 0;
foreach (DataRow r in ds.Tables[0].Select("sotien>0", "mabs"))
{
sql = "mabs='" + r["mabs"].ToString() + "'";
r1 = d.getrowbyid(dsxml.Tables[0], sql);
if (r1 == null)
{
r3 = d.getrowbyid(dtquay, "ma='" + r["mabs"].ToString() + "'");
if (r3 != null)
{
r2 = dsxml.Tables[0].NewRow();
r2["mabs"] = r["mabs"].ToString();
r2["hoten"] = r3["hoten"].ToString();
r2["sotien"] = r["sotien"].ToString();
r2["tenkp"] = "";
r2["trichthuong"] = 0; //decimal.Parse(r["sotien"].ToString())*(tyle.Value/100);
dsxml.Tables[0].Rows.Add(r2);
}
}
else
{
dr = dsxml.Tables[0].Select(sql);
if (dr.Length > 0)
{
dr[0]["sotien"] = decimal.Parse(dr[0]["sotien"].ToString()) + decimal.Parse(r["sotien"].ToString());
}
}
tc += decimal.Parse(r["sotien"].ToString());
}
foreach (DataRow r in dsxml.Tables[0].Rows)
{
r["trichthuong"] = decimal.Parse(r["sotien"].ToString()) * (tyle.Value / 100);
}
doiso.Doisototext dd = new doiso.Doisototext();
if (prn)
{
frmReport f1 = new frmReport(d, dsxml.Tables[0], i_userid, "d_dsbacsi.rpt", "", s_title, "", "", "", "", "", "", "", dd.Doiso_Unicode(Convert.ToInt64(tc).ToString()));
f1.ShowDialog(this);
}
else
{
d.check_process_Excel();
string tenfile = d.Export_Excel(dsxml, "bacsi");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
oxl.Visible = true;
}
}
}
public void OpenExcelAppWithFile(string filePath, int sheetIndex, string sheetName, bool visible)
{
_excelApplication = new Excel.Application();
_excelApplication.Visible = visible;
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Excel._Workbook)(_workBooks.Add(filePath));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(sheetIndex));
_excelSheet.Name = sheetName;
}
public bool SelectSheetByName(string sheetname, bool isActivate)
{
bool isFound = false;
Excel._Worksheet sheet = null;
for (int i = 1; i <= _excelSheets.Count; i++)
{
sheet = (Excel._Worksheet)(_excelSheets.get_Item(i));
if (sheet.Name.Trim().ToUpper() == sheetname.Trim().ToUpper())
{
isFound = true;
_excelSheet = sheet;
break;
}
}
if (isFound && isActivate)
{
_excelSheet.Activate();
}
return isFound;
}
private void exp_excel(bool print)
{
d.check_process_Excel();
try
{
int be = 3, dong = 5, sodong = dsxml.Tables[0].Rows.Count + 5, socot = dsxml.Tables[0].Columns.Count - 1, dongke = sodong;
tenfile = d.Export_Excel(dsxml, "solankham");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));//,Missing.Value,Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value); //,Missing.Value);
}
osheet.get_Range(d.getIndex(be + 2) + dong.ToString(), d.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0";
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 0; i <= socot; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i).ToString();
}
osheet.Cells[sodong, 2] = "TỔNG CỘNG";
orange = osheet.get_Range(d.getIndex(0) + "4", d.getIndex(1) + "4");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
int so = sodong;
osheet.Cells[sodong, 4] = soluot;
osheet.Cells[sodong, 5] = dcongkham;
osheet.Cells[sodong, 6] = dthuoc;
osheet.Cells[sodong, 7] = dcls;
osheet.Cells[sodong, 8] = dcongkham + dthuoc + dcls;
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlPortrait;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.Cells[sodong + 1, 6] = "Ngày ... tháng ... năm ....";
osheet.Cells[sodong + 2, 2] = "THỦ TRƯỞNG ĐƠN VỊ";
osheet.Cells[sodong + 2, 6] = " NGƯỜI LẬP BIỂU ";
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 1] = d.Syte; osheet.Cells[2, 1] = d.Tenbv;
orange = osheet.get_Range(d.getIndex(1) + "1", d.getIndex(3) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
osheet.Cells[1, 3] = "TỔNG HỢP SỐ LƯỢT KHÁM BỆNH " + madoituong.Text.Trim().ToUpper() + " NGỌAI TRÚ";
osheet.Cells[2, 3] = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày :" + tu.Text + " đến ngày :" + den.Text;
orange = osheet.get_Range(d.getIndex(2) + "1", d.getIndex(socot) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void load_grid()
{
if (loai.SelectedIndex == -1)
{
loai.Focus();
return;
}
if (tu.Text == "" || den.Text == "")
{
if (tu.Text == "")
{
tu.Focus();
}
else
{
den.Focus();
}
return;
}
ds.Clear();
songay = 7;
sql = "select a.mabn,a.hoten,sum(b.soluong*b.dongia-b.mien-b.thieu-b.tra) as sotien";
sql += " from xxx.v_vienphill a inner join xxx.v_vienphict b on a.id=b.id ";
sql += " inner join " + user + ".v_giavp c on b.mavp=c.id ";
sql += " inner join " + user + ".v_loaivp d on c.id_loai=d.id ";
sql += " inner join " + user + ".v_nhomvp e on d.id_nhom=e.ma ";
sql += " where b.soluong*b.dongia-b.mien-b.thieu-b.tra>0 ";
sql += " and a.ngay between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
sql += " and d.id=" + decimal.Parse(dtloai.Rows[loai.SelectedIndex]["nhomvp"].ToString());
sql += " group by a.mabn,a.hoten";
sql += " order by a.mabn,a.hoten";
dsvp = m.get_data_mmyy(sql, tu.Text, den.Text, false);
sql = "select a.mabn,f.hoten,sum(a.lt) as lt,sum(a.cp) as cp,000000000000.00 as vp";
sql += " from xxx.cls_ketqua a inner join " + user + ".btdbn b on a.mabn=b.mabn ";
sql += " inner join " + user + ".cls_loai c on a.loai=c.id ";
sql += " left join " + user + ".cls_may d on a.idmay=d.id";
sql += " left join " + user + ".cls_noidung e on a.idvung=e.id";
sql += " left join " + user + ".btdbn f on a.mabn=f.mabn";
sql += " left join " + user + ".dmbs g on a.mabs=g.ma";
sql += " left join xxx.cls_motact h on a.id=h.id ";
sql += " where " + m.for_ngay("a.ngay", stime) + " between to_date('" + tu.Text + "'," + stime + ") and to_date('" + den.Text + "'," + stime + ")";
if (loai.SelectedIndex != -1)
{
sql += " and a.loai=" + int.Parse(loai.SelectedValue.ToString());
}
if (s_trasau != "")
{
sql += " and a.madoituong not in (" + s_trasau.Substring(0, s_trasau.Length - 1) + ")";
}
if (s_mien != "")
{
sql += " and a.madoituong not in (" + s_mien.Substring(0, s_mien.Length - 1) + ")";
}
sql += " group by a.mabn,f.hoten";
sql += " order by a.mabn,f.hoten";
DataSet dstmp = m.get_data_mmyy(sql, tu.Text, den.Text, false);
ds = dstmp.Copy();
ds.Clear();
if (dstmp.Tables[0].Rows.Count == 0)
{
MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), LibMedi.AccessData.Msg);
}
else
{
DataRow r1, r2;
DataRow[] dr;
foreach (DataRow r in dstmp.Tables[0].Rows)
{
r1 = m.getrowbyid(ds.Tables[0], "mabn='" + r["mabn"].ToString() + "'");
if (r1 == null)
{
r2 = ds.Tables[0].NewRow();
r2["mabn"] = r["mabn"].ToString();
r2["hoten"] = r["hoten"].ToString();
r2["cp"] = r["cp"].ToString();
r2["lt"] = r["lt"].ToString();
r2["vp"] = 0;
foreach (DataRow r3 in dsvp.Tables[0].Select("mabn='" + r["mabn"].ToString() + "'"))
{
r2["vp"] = decimal.Parse(r2["vp"].ToString()) + decimal.Parse(r3["sotien"].ToString());
}
ds.Tables[0].Rows.Add(r2);
}
else
{
dr = ds.Tables[0].Select("mabn='" + r["mabn"].ToString() + "'");
if (dr.Length > 0)
{
dr[0]["cp"] = decimal.Parse(dr[0]["cp"].ToString()) + decimal.Parse(r["cp"].ToString());
dr[0]["lt"] = decimal.Parse(dr[0]["lt"].ToString()) + decimal.Parse(r["lt"].ToString());
}
}
}
foreach (DataRow r in dsvp.Tables[0].Rows)
{
r1 = m.getrowbyid(ds.Tables[0], "mabn='" + r["mabn"].ToString() + "'");
if (r1 == null)
{
r2 = ds.Tables[0].NewRow();
r2["mabn"] = r["mabn"].ToString();
r2["hoten"] = r["hoten"].ToString();
r2["cp"] = 0;
r2["lt"] = 0;
r2["vp"] = 0;
foreach (DataRow r3 in dsvp.Tables[0].Select("mabn='" + r["mabn"].ToString() + "'"))
{
r2["vp"] = decimal.Parse(r2["vp"].ToString()) + decimal.Parse(r3["sotien"].ToString());
}
ds.Tables[0].Rows.Add(r2);
}
}
if (rb2.Checked)
{
dstmp.Clear();
dstmp = ds.Copy();
ds.Clear();
ds.Merge(dstmp.Tables[0].Select("cp<>vp", "mabn"));
}
m.check_process_Excel();
string tenfile = m.Export_Excel(ds, "doichieu");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
oxl.ActiveWindow.DisplayZeros = false;
for (int i = 0; i < 5; i++)
{
osheet.Cells[1, i + 1] = get_ten(i);
}
int socot = ds.Tables[0].Rows.Count + 1;
osheet.get_Range(m.getIndex(0) + "1", m.getIndex(ds.Tables[0].Columns.Count - 1) + socot.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
oxl.Visible = true;
}
}
private void exp_excel(bool print)
{
try
{
m_v.check_process_Excel();
int i_rec = 0, be = 4, dong = 6, sodong = ads.Tables[0].Rows.Count + 6, socot = ads.Tables[0].Columns.Count - 1, dongke = sodong - 1;
char[] cSplit = { '+' };
string[] sTitle = haison.Split(cSplit);
i_rec = sTitle.Length;
tenfile = m_v.f_export_excel(ads.Tables[0], "doanhthu");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(m_v.getIndex(i) + "1", m_v.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(m_v.getIndex(0) + "5", m_v.getIndex(0) + "5").EntireRow.Delete(Missing.Value);//remove row field
osheet.get_Range(m_v.getIndex(2) + "5", m_v.getIndex(socot) + sodong.ToString()).NumberFormat = "###,###,###,###";
osheet.get_Range(m_v.getIndex(0) + "4", m_v.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
osheet.Cells[dong - 2, 1] = "STT";
osheet.Cells[dong - 2, 2] = "Nội dung";
osheet.Cells[dong + 3, 2] = "Tổng cộng";
for (int i = 0; i < i_rec; i++)
{
osheet.Cells[dong - 2, i + 3] = sTitle[i].ToString();
osheet.Cells[dong + 3, i + 3] = "=SUM(" + m_v.getIndex(i + 2) + "5:" + m_v.getIndex(i + 2) + "8)";
}
orange = osheet.get_Range(m_v.getIndex(i_rec * 2 + 4) + "4", m_v.getIndex(i_rec * 2 + 5) + "4");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
orange = osheet.get_Range(m_v.getIndex(0) + "1", m_v.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 1] = m_v.Syte; osheet.Cells[2, 1] = m_v.Tenbv;
orange = osheet.get_Range(m_v.getIndex(1) + "1", m_v.getIndex(3) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
osheet.Cells[1, 8] = "BÁO CÁO DOANH THU THEO NGƯỜI THU";
osheet.Cells[2, 8] = (tu.Value == den.Value) ? "Ngày " + tu.Text : "Từ ngày :" + tu.Text + " đến ngày :" + den.Text;
orange = osheet.get_Range(m_v.getIndex(3) + "1", m_v.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exp_excel(bool print)
{
try
{
int be = 3, cot = ds.Tables[0].Columns.Count, dong = ds.Tables[0].Rows.Count + be + 2;
tenfile = d.Export_Excel(ds, "ylenh");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
for (int i = 0; i < 5; i++)
{
osheet.Cells[be + 1, i + 1] = get_ten(i);
}
DataRow [] dr = dsmabd.Tables[0].Select("true", "stt"); //"tenbd";
for (int i = 0; i < dr.Length; i++)
{
osheet.Cells[be + 1, i + 6] = dr[i]["tenbd"].ToString();
osheet.Cells[dong, i + 6] = dr[i]["soluong"].ToString();
}
//Phong giuong
orange = osheet.get_Range(d.getIndex(1) + "4", d.getIndex(2) + "4");
orange.VerticalAlignment = XlVAlign.xlVAlignBottom;
orange.Orientation = 90;
orange.RowHeight = 180;
//Ten Thuoc
orange = osheet.get_Range(d.getIndex(be + 2) + "4", d.getIndex(cot - 1) + "4");
orange.VerticalAlignment = XlVAlign.xlVAlignBottom;
orange.WrapText = false;
orange.Orientation = 90;
orange.RowHeight = 180;
orange.Font.Bold = true;
orange.EntireRow.AutoFit();
//Hien thi chi tiet
orange = osheet.get_Range(d.getIndex(0) + "5", d.getIndex(cot - 1) + dong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 10;
orange.Font.Bold = false;
orange.RowHeight = 16;
orange.NumberFormat = "###0.##";
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(cot) + dong.ToString()).Borders.LineStyle = XlLineStyle.xlDot; // XlBorderWeight.xlThin;
orange.EntireColumn.AutoFit();
//cot hoten BN: In dam
orange = osheet.get_Range(d.getIndex(4) + "4", d.getIndex(4) + dong.ToString());
orange.Font.Name = "Arial";
orange.Font.Bold = true;
orange.EntireColumn.AutoFit();
//
string s_tt = (tt1.Checked)?tt1.Text:(tt2.Checked)?tt2.Text:"";
osheet.get_Range(d.getIndex(4) + dong.ToString(), d.getIndex(cot - 1) + dong.ToString()).Font.Bold = true;
oxl.ActiveWindow.DisplayZeros = false;
osheet.Cells[1, 2] = makp.Text + " " + s_tt;
osheet.Cells[2, 2] = (tu.Text == den.Text)?"Ngày " + tu.Text:"Ngày " + tu.Text + " - " + den.Text;
osheet.Cells[2, 6] = ((s_tenphieu == "")?"":s_tenphieu + " : ") + ((s_buoi == "")?"Cả ngày":s_buoi);
osheet.Cells[1, 5] = "TỔNG HỢP Y LỆNH";
orange = osheet.get_Range(d.getIndex((cot > 3)?4:cot - 1) + "1", d.getIndex(cot - 1) + "1");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
//border lai dong cot
int jcot = (cot < 16)?16:cot;
//
for (int ji = 0; ji < jcot; ji++)
{
orange = osheet.get_Range(m.getIndex(ji) + "4", m.getIndex(ji) + dong);
orange.Cells.BorderAround(5, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexAutomatic, 0);
if (ji >= cot)
{
orange.ColumnWidth = 3; //[b.getIndex(0)+"3",b.getIndex(ji)]
}
}
for (int ji = 4; ji < dong; ji++)
{
orange = osheet.get_Range(m.getIndex(0) + ji, m.getIndex(jcot) + ji);
orange.Cells.BorderAround(5, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexAutomatic, 0);
}
orange = osheet.get_Range(m.getIndex(0) + "4:" + m.getIndex(0) + dong, m.getIndex(0) + "4:" + m.getIndex(jcot) + dong);
orange.Cells.BorderAround(1, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 2);
//
//ke them row trong
for (int ji = 0; ji < jcot; ji++)
{
orange = osheet.get_Range(m.getIndex(ji) + (dong + 3), m.getIndex(ji) + (dong + 15));
orange.Cells.BorderAround(5, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexAutomatic, 0);
if (ji >= cot)
{
orange.ColumnWidth = 3; //[b.getIndex(0)+"3",b.getIndex(ji)]
}
}
for (int ji = dong + 3; ji < dong + 15; ji++)
{
orange = osheet.get_Range(m.getIndex(0) + ji, m.getIndex(jcot - 1) + ji);
orange.Cells.BorderAround(5, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexAutomatic, 0);
}
//
for (int ji = 5; ji <= dong + 15; ji++)
{
orange = osheet.get_Range(m.getIndex(0) + ji, m.getIndex(cot) + ji);
orange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
orange.RowHeight = 16;
orange.WrapText = false;
}
//
//Ten Thuoc
orange = osheet.get_Range(d.getIndex(be + 2) + "4", d.getIndex(cot - 1) + "4");
orange.VerticalAlignment = XlVAlign.xlVAlignBottom;
orange.WrapText = false;
orange.Orientation = 90;
orange.RowHeight = 180;
orange.Font.Bold = true;
orange.EntireRow.AutoFit();
//end binh
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exp_excel(bool print)
{
try
{
DataSet tmp = new DataSet();
tmp = ds.Copy();
ds.Clear();
ds.Merge(tmp.Tables[0].Select("true", "ten,dang"));
int k = 1;
foreach (DataRow r in ds.Tables[0].Rows)
{
r["stt"] = k++;
}
int be = 5, dong = 7, sodong = ds.Tables[0].Rows.Count + 6, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong;
tenfile = d.Export_Excel(ds, "bcngay");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(d.getIndex(be) + dong.ToString(), d.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(d.getIndex(0) + "5", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 1; i < dong - 2; i++)
{
osheet.Cells[dong - 1, i] = get_ten(i - 1);
}
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot + 7) + (sodong + 7).ToString());
osheet.Cells[dong - 1, dsngay.Tables[0].Rows.Count + 5] = "Tổng cộng";
osheet.Cells[dong - 1, dsngay.Tables[0].Rows.Count + 6] = "Ghi Chú";
for (int i = 0; i < dsngay.Tables[0].Rows.Count; i++)
{
osheet.Cells[dong - 1, i + 5] = " " + dsngay.Tables[0].Rows[i]["ten"].ToString();
}
osheet.get_Range(d.getIndex(4) + "6", d.getIndex(dsngay.Tables[0].Rows.Count + 3) + "6").Orientation = 90;
osheet.get_Range(d.getIndex(0) + "6", d.getIndex(dsngay.Tables[0].Rows.Count + 3) + "6").RowHeight = 30;
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 2] = d.Syte; osheet.Cells[2, 2] = d.Tenbv;
osheet.Cells[3, 2] = "Khoa: " + makp.Text;
osheet.Cells[1, socot] = "MS: 16D/BV-01";
osheet.Cells[2, socot] = "Số:";
orange = osheet.get_Range(osheet.Cells[1, socot], osheet.Cells[2, socot]);
orange.Font.Bold = false;
orange.HorizontalAlignment = XlHAlign.xlHAlignLeft;
orange = osheet.get_Range(osheet.Cells[dong - 1, 1], osheet.Cells[dong - 1, dsngay.Tables[0].Rows.Count + 6]);
orange.Font.Bold = true;
orange = osheet.get_Range(d.getIndex(1) + "5", d.getIndex(1) + "6");
orange.Merge(Type.Missing);
//excelApp.get_Range("A1:A360,B1:E1", Type.Missing).Merge(Type.Missing)
orange = osheet.get_Range(d.getIndex(2) + "5", d.getIndex(2) + "6");
orange.Merge(Type.Missing);
orange = osheet.get_Range(d.getIndex(3) + "5", d.getIndex(3) + "6");
orange.Merge(Type.Missing);
orange = osheet.get_Range(d.getIndex(0) + "5", d.getIndex(0) + "6");
orange.Merge(Type.Missing);
orange = osheet.get_Range(d.getIndex(socot) + "5", d.getIndex(socot) + "6");
orange.Merge(Type.Missing);
orange = osheet.get_Range(d.getIndex(socot - 1) + "5", d.getIndex(socot - 1) + "6");
orange.Merge(Type.Missing);
orange = osheet.get_Range(d.getIndex(4) + "5", d.getIndex(socot - 2) + "5");
orange.Merge(Type.Missing);
orange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
osheet.Cells[5, 5] = "Ngày";
osheet.get_Range(d.getIndex(0) + "7", d.getIndex(socot) + "7").EntireRow.Insert(Missing.Value);
osheet.Cells[7, 1] = "A";
osheet.Cells[7, 2] = "B";
osheet.Cells[7, 3] = "C";
osheet.Cells[7, 4] = "D";
osheet.Cells[7, socot] = "E";
osheet.Cells[7, socot + 1] = "G";
orange = osheet.get_Range(d.getIndex(0) + "7", d.getIndex(socot + 2) + "7");
string s_ngay = d.Ngay_hethong;
osheet.Cells[sodong + 3, 2] = "NGƯỜI THỐNG KÊ";
osheet.Cells[sodong + 3, 6] = "KẾ TOÁN DƯỢC";
osheet.Cells[sodong + 3, 19] = "Ngày " + s_ngay.Substring(0, 2) + " tháng " + s_ngay.Substring(3, 2) + " năm " + s_ngay.Substring(6, 4);
osheet.Cells[sodong + 4, 19] = "TRƯỞNG KHOA " + makp.Text.ToUpper();
osheet.Cells[sodong + 7, 2] = "Họ tên: ";
osheet.Cells[sodong + 7, 6] = "Họ tên: ";
osheet.Cells[sodong + 8, 19] = "Họ tên: ";
orange = osheet.get_Range(osheet.Cells[sodong + 3, 2], osheet.Cells[sodong + 4, 20]);
orange.Font.Bold = true;
//orange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
orange.Orientation = 0;
//osheet.Cells[1,4]="BÁO CÁO SỬ DỤNG";
osheet.Cells[1, 4] = "THỐNG KÊ " + dsngay.Tables[0].Rows.Count.ToString() + " NGÀY SỬ DỤNG THUỐC, HÓA CHẤT,\n VẬT TƯ Y TẾ TIÊU HAO";
osheet.Cells[2, 4] = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày : " + tu.Text + " đến : " + den.Text;
orange = osheet.get_Range(d.getIndex(3) + "1", d.getIndex(socot - 2) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exp_excel(bool print)
{
d.check_process_Excel();
ds = dsxml.Copy();
int dong = 3, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong + 1;
tenfile = d.Export_Excel(ds, "BAOCAONXT");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < 2; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(d.getIndex(3) + dong.ToString(), d.getIndex(socot + 1) + sodong.ToString()).NumberFormat = format_soluong;
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
int pos = 11;
int row = dong - 1;
foreach (DataRow r in dtdmkho.Rows)
{
osheet.Cells[row, pos] = r["ten"].ToString();
osheet.get_Range(d.getIndex(pos - 1) + row.ToString(), d.getIndex(pos) + row.ToString()).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
pos += 2;
}
for (int i = 0; i < 10; i++)
{
osheet.Cells[dong, i + 1] = get_ten(i);
if (i < 4)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + dong.ToString()).MergeCells = true;
}
//else osheet.get_Range(d.getIndex(i)+"1",d.getIndex(i)+"2").MergeCells=true;
}
for (int i = 8; i < ds.Tables[0].Columns.Count; i++)
{
osheet.Cells[dong, i + 1] = get_ten((i % 2 == 0)?4:5);
}
osheet.Cells[dong - 1, 5] = "Tồn đầu";
osheet.Cells[dong - 1, 7] = "Nhập";
osheet.Cells[dong - 1, 9] = "Nhập khác";
osheet.Cells[dong - 2, 11] = "Xuất";
osheet.Cells[dong - 1, 11 + dtdmkho.Rows.Count * 2] = "Tồn cuối";
osheet.get_Range(d.getIndex(10) + "1", d.getIndex(ds.Tables[0].Columns.Count - 3) + "1").HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange = osheet.get_Range(d.getIndex(4) + "1", d.getIndex(5) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.VerticalAlignment = XlVAlign.xlVAlignCenter;
orange.MergeCells = true;
orange = osheet.get_Range(d.getIndex(6) + "1", d.getIndex(7) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.VerticalAlignment = XlVAlign.xlVAlignCenter;
orange.MergeCells = true;
orange = osheet.get_Range(d.getIndex(8) + "1", d.getIndex(9) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.VerticalAlignment = XlVAlign.xlVAlignCenter;
orange.MergeCells = true;
orange = osheet.get_Range(d.getIndex(socot - 1) + "1", d.getIndex(socot) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.VerticalAlignment = XlVAlign.xlVAlignCenter;
orange.MergeCells = true;
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 10;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
string s_title = "Từ tháng " + tu.Value.ToString().PadLeft(2, '0') + " đến " + den.Value.ToString().PadLeft(2, '0');
if (tu.Text == den.Text)
{
s_title = "Tháng " + tu.Value.ToString().PadLeft(2, '0');
}
s_title += " Năm " + yyyy.Value.ToString();
osheet.PageSetup.LeftHeader = d.Syte + "\n" + d.Tenbv;
osheet.PageSetup.CenterHeader = "&\"Arial,Bold\"&14BÁO CÁO NHẬP XUẤT TỒN\n" + s_title;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
private void butIn_Click(object sender, System.EventArgs e)
{
d.check_process_Excel();
#region
DataSet dsxml = new DataSet();
dsxml = ds.Copy();
dsxml.Clear();
DataRow r2;
int stt = 0, tt = 1;
sql = "manhom,tenbd";
DataRow [] dr = ds.Tables[0].Select("true", sql);
for (int i = 0; i < dr.Length; i++)
{
if (stt != int.Parse(dr[i]["manhom"].ToString()))
{
stt = int.Parse(dr[i]["manhom"].ToString());
r2 = dsxml.Tables[0].NewRow();
r2["stt"] = 0;
r2["tenbd"] = dr[i]["tennhom"].ToString().ToUpper();
r2["dang"] = "";
r2["tc"] = 0;
foreach (DataRow r3 in dtkho.Rows)
{
r2["c_" + r3["id"].ToString().PadLeft(3, '0')] = 0;
}
dsxml.Tables[0].Rows.Add(r2);
}
r2 = dsxml.Tables[0].NewRow();
r2["stt"] = tt;
r2["tenbd"] = dr[i]["tenbd"].ToString();
r2["dang"] = dr[i]["dang"].ToString();
foreach (DataRow r3 in dtkho.Rows)
{
r2["c_" + r3["id"].ToString().PadLeft(3, '0')] = dr[i]["c_" + r3["id"].ToString().PadLeft(3, '0')];
}
r2["tc"] = dr[i]["tc"].ToString();
dsxml.Tables[0].Rows.Add(r2);
tt++;
}
dsxml.Tables[0].Columns.Remove("manhom");
dsxml.Tables[0].Columns.Remove("tennhom");
dsxml.Tables[0].Columns.Remove("mabd");
dsxml.Tables[0].Columns.Remove("manguon");
dsxml.Tables[0].Columns.Remove("tennguon");
dsxml.Tables[0].Columns.Remove("ma");
dsxml.Tables[0].Columns.Remove("tenhc");
#endregion
int be = 0;
int dong = dsxml.Tables[0].Rows.Count + be + 1, socot = dsxml.Tables[0].Columns.Count + 1;
string tenfile = d.Export_Excel(dsxml, "tonkho");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
oxl.ActiveWindow.DisplayZeros = false;
int j = 0;
for (; j < 4; j++)
{
osheet.Cells[be + 1, j + 1] = get_ten(j);
}
foreach (DataRow r in dtkho.Rows)
{
osheet.Cells[be + 1, ++j] = r["ten"].ToString();
}
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot - 2) + dong.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot - 2) + dong.ToString()).EntireColumn.AutoFit();
oxl.Visible = true;
}
private void exp_excel(bool print)
{
try
{
int be = 3, dong = 5, sodong = ds.Tables[0].Rows.Count + 5, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 1;
tenfile = ttb.Export_Excel(ds, "bcngay");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
for (int i = 0; i < be; i++)
{
osheet.get_Range(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(ttb.getIndex(be) + dong.ToString(), ttb.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(ttb.getIndex(0) + "4", ttb.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 1; i < dong; i++)
{
osheet.Cells[dong - 1, i] = get_ten(i - 1);
}
orange = osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(socot) + sodong.ToString());
osheet.Cells[dong - 1, dsngay.Tables[0].Rows.Count + 5] = "Cộng";
osheet.Cells[dong - 1, dsngay.Tables[0].Rows.Count + 6] = "Tổng trả";
for (int i = 0; i < dsngay.Tables[0].Rows.Count; i++)
{
osheet.Cells[dong - 1, i + 5] = " " + dsngay.Tables[0].Rows[i]["ten"].ToString();
}
osheet.get_Range(ttb.getIndex(4) + "4", ttb.getIndex(dsngay.Tables[0].Rows.Count + 5) + "4").Orientation = 90;
osheet.get_Range(ttb.getIndex(0) + "4", ttb.getIndex(dsngay.Tables[0].Rows.Count + 5) + "4").RowHeight = 30;
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 2] = ttb.Syte; osheet.Cells[2, 2] = ttb.Tenbv;
osheet.Cells[1, 4] = "BÁO CÁO SỬ DỤNG";
string s_title = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày : " + tu.Text + " đến : " + den.Text;
osheet.Cells[2, 4] = s_title;
orange = osheet.get_Range(ttb.getIndex(3) + "1", ttb.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// �ͷ��ڴ�ռ�
/// </summary>
public void Dispose()
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;
CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (System.Exception)
{
// MessageBox.Show("���ͷ�Excel�ڴ�ռ�ʱ������һ������"+ex.Message);
}
finally
{
//foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
// if (pro.StartTime > this.dtBefore && pro.StartTime < this.dtAfter)
// {
// try
// {
// pro.Kill();
// }
// catch { }
// }
}
System.GC.SuppressFinalize(this);
}
public void OpenExcelApp(bool visible)
{
_excelApplication = new Excel.Application();
_excelApplication.Visible = visible;
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Excel._Workbook)(_workBooks.Add(_value));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1));
}
private void exp_excel(bool print)
{
try
{
string loai = loaibenh.Text != ""?"(" + loaibenh.Text.ToUpper() + ")":"";
int be = 5, dong = 4, sodong = ds.Tables[0].Rows.Count + 1, socot = ds.Tables[0].Columns.Count + 1, dongke = sodong - 1;
tenfile = m.Export_Excel(ds, "bcngay");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
osheet.get_Range(b.getIndex(0) + "1", b.getIndex(0) + "1").EntireRow.Delete(Missing.Value);
for (int i = 0; i < be; i++)
{
osheet.get_Range(b.getIndex(i) + "1", b.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(b.getIndex(1) + dong.ToString(), b.getIndex(socot - 2) + (dong + sodong + 1).ToString()).NumberFormat = "#,##";
osheet.get_Range(b.getIndex(0) + dong.ToString(), b.getIndex(socot - 2) + (dong + dongke + 1).ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
osheet.get_Range(b.getIndex(0) + (dong + 1), b.getIndex(0) + dong).Merge(Missing.Value);
osheet.get_Range(b.getIndex(0) + (dong + 1), b.getIndex(0) + dong).VerticalAlignment = XlVAlign.xlVAlignCenter;
osheet.get_Range(b.getIndex(1) + dong, b.getIndex(16) + dong).Merge(Missing.Value);
osheet.get_Range(b.getIndex(1) + dong, b.getIndex(16) + dong).HorizontalAlignment = XlHAlign.xlHAlignCenter;
osheet.get_Range(b.getIndex(17) + dong, b.getIndex(32) + dong).Merge(Missing.Value);
osheet.get_Range(b.getIndex(17) + dong, b.getIndex(32) + dong).HorizontalAlignment = XlHAlign.xlHAlignCenter;
osheet.get_Range(b.getIndex(33) + dong, b.getIndex(48) + dong).Merge(Missing.Value);
osheet.get_Range(b.getIndex(33) + dong, b.getIndex(48) + dong).HorizontalAlignment = XlHAlign.xlHAlignCenter;
osheet.get_Range(b.getIndex(49) + (dong + 1), b.getIndex(49) + dong).Merge(Missing.Value);
osheet.Cells[dong, 1] = get_tenhead(0);
osheet.Cells[dong, 2] = get_tenhead(1);
osheet.Cells[dong, 18] = get_tenhead(2);
osheet.Cells[dong, 34] = get_tenhead(3);
osheet.Cells[dong, 50] = get_tenhead(4);
for (int i = 1; i < socot; i++)
{
osheet.Cells[dong + 1, i] = get_ten(i - 1);
}
orange = osheet.get_Range(b.getIndex(0) + "1", b.getIndex(socot - 1) + (dong + sodong).ToString());
osheet.get_Range(b.getIndex(1) + (dong + 1), b.getIndex(socot - 3) + (dong + 1)).Orientation = 90;
osheet.get_Range(b.getIndex(1) + (dong + 1), b.getIndex(socot - 2) + (dong + 1)).RowHeight = 50;
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 1] = b.Syte; osheet.Cells[2, 1] = b.Tenbv;
osheet.Cells[1, 4] = "TÌNH HÌNH BỆNH NHÂN NHẬP XUẤT" + loai;
osheet.Cells[2, 4] = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày : " + tu.Text + " đến : " + den.Text;
orange = osheet.get_Range(b.getIndex(3) + "1", b.getIndex(socot - 1) + "2");
// orange.HorizontalAlignment=XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void SelectSheet(int sheetIndex, bool isActivate)
{
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(sheetIndex + 1));
if (isActivate)
{
_excelSheet.Activate();
}
}
private void excelNotPBS()
{
DateTime pReadDate = DateTime.Now;
string pSheetName = "MedlemNotPBS";
Excel.Application oXL = null;;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Window oWindow;
Excel.Range oRng;
var rec_regnskab = Program.qryAktivRegnskab();
string SaveAs = rec_regnskab.Eksportmappe + pSheetName + pReadDate.ToString("_yyyyMMdd_hhmmss") + ".xls";
var MedlemmerAll = from h in Program.dbData3060.tblMedlems
select new clsMedlemNotPBSAll
{
Nr = h.Nr,
Navn = h.Navn,
Kaldenavn = h.Kaldenavn,
Adresse = h.Adresse,
Postnr = h.Postnr,
Bynavn = h.Bynavn,
Telefon = h.Telefon,
Email = h.Email,
Kon = h.Kon.ToString(),
FodtDato = h.FodtDato,
erMedlem = ((bool)Program.dbData3060.erMedlem(h.Nr)) ? 1 : 0,
erPBS = ((bool)Program.dbData3060.erPBS(h.Nr)) ? 1 : 0,
};
var MedlemmerNotPBS = from h in MedlemmerAll
where h.erMedlem == 1 && h.erPBS == 0
select new clsMedlemNotPBS
{
Nr = h.Nr,
Navn = h.Navn,
Kaldenavn = h.Kaldenavn,
Adresse = h.Adresse,
Postnr = h.Postnr,
Bynavn = h.Bynavn,
Telefon = h.Telefon,
Email = h.Email,
Kon = h.Kon,
PBSnr = "03985644",
Debgrnr = "00001",
Kundenr = 032001610000000 + (int)h.Nr
};
using (new ExcelUILanguageHelper())
{
try
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = oXL.Workbooks.Add((Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oWindow = oXL.ActiveWindow;
if (pSheetName.Length > 0)
{
oSheet.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length);
}
int row = 1;
this.MainformProgressBar.Value = 0;
this.MainformProgressBar.Minimum = 0;
this.MainformProgressBar.Maximum = (from h in Program.dbData3060.tblMedlems select h).Count();
this.MainformProgressBar.Step = 1;
this.MainformProgressBar.Visible = true;
foreach (clsMedlemNotPBS m in MedlemmerNotPBS)
{
this.MainformProgressBar.PerformStep();
row++;
Type objectType = m.GetType();
PropertyInfo[] properties = objectType.GetProperties();
int col = 0;
foreach (PropertyInfo property in properties)
{
col++;
string Name = property.Name;
//string NamePropertyType = property.GetValue(m, null).GetType().ToString();
oSheet.Cells[row, col] = property.GetValue(m, null);
if (row == 2)
{
object[] CustomAttributes = property.GetCustomAttributes(false);
foreach (var att in CustomAttributes)
{
Type tp = att.GetType();
if (tp.ToString() == "nsPuls3060.Fieldattr")
{
Fieldattr attr = (Fieldattr)att;
string heading = attr.Heading;
oSheet.Cells[1, col] = heading;
}
}
}
}
}
oRng = (Excel.Range)oSheet.Rows[1, Missing.Value];
oRng.Font.Name = "Arial";
oRng.Font.Size = 12;
oRng.Font.Strikethrough = false;
oRng.Font.Superscript = false;
oRng.Font.Subscript = false;
oRng.Font.OutlineFont = false;
oRng.Font.Shadow = false;
oRng.Font.Bold = true;
oRng.HorizontalAlignment = Excel.Constants.xlCenter;
oRng.VerticalAlignment = Excel.Constants.xlBottom;
oRng.WrapText = false;
oRng.Orientation = 0;
oRng.AddIndent = false;
oRng.IndentLevel = 0;
oRng.ShrinkToFit = false;
oRng.MergeCells = false;
string BottomRight = "L" + row.ToString();
oRng = oSheet.get_Range("L2", BottomRight);
oRng.NumberFormat = "##############";
oSheet.Cells.EntireColumn.AutoFit();
oWindow.SplitRow = 1;
oWindow.SplitColumn = 2;
oWindow.FreezePanes = true;
oSheet.get_Range("A1", Missing.Value).Select();
for (var i = oWB.Worksheets.Count; i > 0; i--)
{
Excel._Worksheet oSheetWrk = (Excel._Worksheet)oWB.Worksheets.get_Item(i);
if (oSheetWrk.Name != "MedlemNotPBS")
{
oSheetWrk.Delete();
}
}
oWB.SaveAs(SaveAs, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Saved = true;
oXL.Visible = true;
this.MainformProgressBar.Visible = false;
this.emailExcelFile(SaveAs, "Puls3060 Medlemmer ikke tilmeldt PBS");
//oXL.Quit();
//oXL = null;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
private void exp_excel_eee(bool print)
{
try
{
d.check_process_Excel();
int be = 3, cot = ds.Tables[0].Columns.Count, dong = ds.Tables[0].Rows.Count + be + 2;
tenfile = d.Export_Excel(ds, "TONGHOP");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
for (int i = 0; i < 4; i++)
{
osheet.Cells[be + 1, i + 1] = get_ten(i);
}
DataRow [] dr = dsmabd.Tables[0].Select("true", "tenbd");
for (int i = 0; i < dr.Length; i++)
{
osheet.Cells[be + 1, i + 4] = dr[i]["tenbd"].ToString();
osheet.Cells[dong, i + 4] = dr[i]["soluong"].ToString();
}
orange = osheet.get_Range(d.getIndex(be) + "4", d.getIndex(cot - 1) + "4");
orange.VerticalAlignment = XlVAlign.xlVAlignBottom;
orange.Orientation = 90;
orange.RowHeight = 200;
orange.EntireRow.AutoFit();
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(cot - 1) + dong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 10;
orange.Font.Bold = false;
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(cot - 1) + dong.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
//orange.EntireRow.AutoFit();
orange.EntireColumn.AutoFit();
osheet.get_Range(d.getIndex(3) + dong.ToString(), d.getIndex(cot - 1) + dong.ToString()).Font.Bold = true;
oxl.ActiveWindow.DisplayZeros = false;
osheet.Cells[1, 2] = ""; //makp.Text;
osheet.Cells[2, 3] = (tu.Text == den.Text)?"Ngày " + tu.Text:"Ngày " + tu.Text + " - " + den.Text;
osheet.Cells[1, 3] = "TỔNG HỢP SỬ DỤNG THUỐC";
orange = osheet.get_Range(d.getIndex(2) + "1", d.getIndex(cot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exp_excel(bool print)
{
try
{
d.check_process_Excel();
int i_rec = 0, be = 4, dong = 6, sodong = ads.Tables[0].Rows.Count + 6, socot = ads.Tables[0].Columns.Count - 1, dongke = sodong - 1;
char[] cSplit = { '+' };
string[] sTitle = haison.Split(cSplit);
i_rec = sTitle.Length;
tenfile = d.Export_Excel(ads, "HOSOBENHAN");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(d.getIndex(0) + "5", d.getIndex(0) + "5").EntireRow.Delete(Missing.Value); //remove row field
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 0; i < i_rec; i++)
{
osheet.Cells[dong - 2, i + 1] = sTitle[i].ToString();
}
orange = osheet.get_Range(d.getIndex(i_rec * 2 + 4) + "4", d.getIndex(i_rec * 2 + 5) + "4");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
// osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
// osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
// osheet.PageSetup.LeftMargin = 20;
// osheet.PageSetup.RightMargin = 20;
// osheet.PageSetup.TopMargin = 30;
// osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 1] = d.Syte; osheet.Cells[2, 1] = d.Tenbv;
orange = osheet.get_Range(d.getIndex(1) + "1", d.getIndex(3) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
osheet.Cells[1, 8] = "SỔ VÀO VIỆN - RA VIỆN - CHUYỂN VIỆN";
osheet.Cells[2, 8] = (tu.Text == den.Text) ? "Tháng " + tu.Text : "Từ ngày " + tu.Text + " đến ngày" + den.Text;
if (s_tenkp.Trim().Trim(',') != "")
{
osheet.Cells[3, 8] = s_tenkp;
}
orange = osheet.get_Range(d.getIndex(3) + "1", d.getIndex(socot - 1) + "3");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void excelExternt()
{
DateTime pReadDate = DateTime.Now;
string pSheetName = "MedlemEkstern";
Excel.Application oXL = null;;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Window oWindow;
Excel.Range oRng;
var rec_regnskab = Program.qryAktivRegnskab();
string SaveAs = rec_regnskab.Eksportmappe + pSheetName + pReadDate.ToString("_yyyyMMdd_hhmmss") + ".xls";
clsRest objRest = new clsRest();
string strxmldata = objRest.HttpGet2(clsRest.urlBaseType.data, "personlist");
XDocument xmldata = XDocument.Parse(strxmldata);
string Status = xmldata.Descendants("Status").First().Value;
if (Status != "True")
{
return;
}
var MedlemmerAll = from h in xmldata.Descendants("Person")
orderby clsPassXmlDoc.attr_val_int(h, "Nr")
select new clsMedlemExternAll
{
Nr = clsPassXmlDoc.attr_val_int(h, "Nr"),
Navn = clsPassXmlDoc.attr_val_string(h, "Navn"),
Kaldenavn = clsPassXmlDoc.attr_val_string(h, "Kaldenavn"),
Adresse = clsPassXmlDoc.attr_val_string(h, "Adresse"),
Postnr = clsPassXmlDoc.attr_val_string(h, "Postnr"),
Bynavn = clsPassXmlDoc.attr_val_string(h, "Bynavn"),
Telefon = clsPassXmlDoc.attr_val_string(h, "Telefon"),
Email = clsPassXmlDoc.attr_val_string(h, "Email"),
Kon = clsPassXmlDoc.attr_val_string(h, "Kon"),
FodtDato = clsPassXmlDoc.attr_val_date(h, "FodtDato"),
erMedlem = (clsPassXmlDoc.attr_val_bool(h, "erMedlem")) ? 1 : 0,
erPBS = (clsPassXmlDoc.attr_val_bool(h, "Tilmeldtpbs")) ? 1 : 0,
};
using (new ExcelUILanguageHelper())
{
try
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = oXL.Workbooks.Add((Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oWindow = oXL.ActiveWindow;
if (pSheetName.Length > 0)
{
oSheet.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length);
}
int row = 1;
this.MainformProgressBar.Value = 0;
this.MainformProgressBar.Minimum = 0;
this.MainformProgressBar.Maximum = (from h in Program.karMedlemmer select h).Count();
this.MainformProgressBar.Step = 1;
this.MainformProgressBar.Visible = true;
foreach (clsMedlemExternAll m in MedlemmerAll)
{
this.MainformProgressBar.PerformStep();
row++;
Type objectType = m.GetType();
PropertyInfo[] properties = objectType.GetProperties();
int col = 0;
foreach (PropertyInfo property in properties)
{
col++;
string Name = property.Name;
//string NamePropertyType = property.GetValue(m, null).GetType().ToString();
oSheet.Cells[row, col] = property.GetValue(m, null);
if (row == 2)
{
object[] CustomAttributes = property.GetCustomAttributes(false);
foreach (var att in CustomAttributes)
{
Type tp = att.GetType();
if (tp.ToString() == "nsPuls3060.Fieldattr")
{
Fieldattr attr = (Fieldattr)att;
string heading = attr.Heading;
oSheet.Cells[1, col] = heading;
}
}
}
}
}
oRng = (Excel.Range)oSheet.Rows[1, Missing.Value];
oRng.Font.Name = "Arial";
oRng.Font.Size = 12;
oRng.Font.Strikethrough = false;
oRng.Font.Superscript = false;
oRng.Font.Subscript = false;
oRng.Font.OutlineFont = false;
oRng.Font.Shadow = false;
oRng.Font.Bold = true;
oRng.HorizontalAlignment = Excel.Constants.xlCenter;
oRng.VerticalAlignment = Excel.Constants.xlBottom;
oRng.WrapText = false;
oRng.Orientation = 0;
oRng.AddIndent = false;
oRng.IndentLevel = 0;
oRng.ShrinkToFit = false;
oRng.MergeCells = false;
string BottomRight = "J" + row.ToString();
oRng = oSheet.get_Range("J2", BottomRight);
oRng.NumberFormat = "dd-mm-yyyy";
oSheet.Cells.EntireColumn.AutoFit();
oWindow.SplitRow = 1;
oWindow.SplitColumn = 2;
oWindow.FreezePanes = true;
oSheet.get_Range("A1", Missing.Value).Select();
for (var i = oWB.Worksheets.Count; i > 0; i--)
{
Excel._Worksheet oSheetWrk = (Excel._Worksheet)oWB.Worksheets.get_Item(i);
if (oSheetWrk.Name != "MedlemEkstern")
{
oSheetWrk.Delete();
}
}
oWB.SaveAs(SaveAs, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Saved = true;
oXL.Visible = true;
this.MainformProgressBar.Visible = false;
this.sendMedlem(SaveAs);
//oXL.Quit();
//oXL = null;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
private void exp_excel()
{
try
{
int be = 21, dong = 23, sodong = ds.Tables[0].Rows.Count + 24, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 1;
tenfile = ttb.Export_Excel(ds, "bbthanhly");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(1) + "1").EntireColumn.Delete(Missing.Value);
for (int i = 0; i < be; i++)
{
osheet.get_Range(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(ttb.getIndex(5) + dong.ToString(), ttb.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(ttb.getIndex(0) + "21", ttb.getIndex(socot - 1) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 1; i < 6; i++)
{
osheet.Cells[dong - 1, i] = get_title(i - 1);
orange = osheet.get_Range(ttb.getIndex(i - 1) + "21", ttb.getIndex(i - 1) + "22");
orange.MergeCells = true;
}
for (int i = 0; i < dtkho.Rows.Count; i++)
{
osheet.Cells[dong - 1, i + 6] = dtkho.Rows[i]["ten"].ToString();
}
i_rec = dtkho.Rows.Count;
for (int i = 5; i < 9; i++)
{
osheet.Cells[dong - 1, i + i_rec + 1] = get_title(i);
}
for (int i = 6 + i_rec; i < 6 + i_rec + 3; i++)
{
orange = osheet.get_Range(ttb.getIndex(i) + "21", ttb.getIndex(i) + "22");
orange.MergeCells = true;
}
osheet.Cells[dong - 2, 6] = "Số lượng";
orange = osheet.get_Range(ttb.getIndex(5) + "21", ttb.getIndex(i_rec + 5) + "21");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
orange = osheet.get_Range(ttb.getIndex(0) + dong.ToString(), ttb.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 1] = ttb.Syte; osheet.Cells[2, 1] = ttb.Tenbv;
osheet.Cells[1, 3] = "BIÊN BẢN THANH LÝ,HỦY";
osheet.Cells[2, 3] = ngay;
for (int i = 0; i < 16; i++)
{
osheet.Cells[i + 4, 2] = get_ten(i);
}
for (int i = 0; i < 12; i++)
{
osheet.Cells[i + 6, 7] = get_chucdanh(i);
}
orange = osheet.get_Range(ttb.getIndex(2) + "1", ttb.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
osheet.Cells[sodong - 1, 2] = "Tổng số tiền :";
osheet.Cells[sodong - 1, 5 + i_rec + 3] = tongcong;
string s = doiso.Doiso_Unicode(ds.Tables[0].Rows.Count.ToString()).Trim();
osheet.Cells[sodong + 1, 2] = "Tổng số :" + s.Substring(0, s.Length - 4) + " khoản";
osheet.Cells[sodong + 2, 2] = "Số tiền :" + doiso.Doiso_Unicode(Convert.ToInt64(tongcong).ToString());
int so1 = sodong + 4, so2 = sodong + 5;
osheet.Cells[sodong + 4, 3] = "HỘI ĐỒNG THANH LÝ";
osheet.Cells[sodong + 5, 3] = "(Ký)";
orange = osheet.get_Range(ttb.getIndex(2) + so1.ToString(), ttb.getIndex(7) + so2.ToString());
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
so1 = sodong - 1;
orange = osheet.get_Range(ttb.getIndex(1) + so1.ToString(), ttb.getIndex(socot) + so2.ToString());
orange.Font.Bold = true;
for (int i = sodong + 6; i < sodong + 6 + 12; i++)
{
osheet.Cells[i, 2] = get_ten(i - sodong - 4);
}
for (int i = sodong + 6; i < sodong + 6 + 12; i++)
{
osheet.Cells[i, 7] = get_chucdanh(i - sodong - 6);
}
oxl.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void setWorksheetName(int sheetIndex, string worksheetName)
{
// Missing.Value
Excel._Worksheet sheet = (Excel._Worksheet)(xlsWb.Worksheets[(object)sheetIndex]);
sheet.Name = worksheetName;
}
private void excelInternt()
{
DateTime pReadDate = DateTime.Now;
string pSheetName = "MedlemIntern";
Excel.Application oXL = null;;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Window oWindow;
Excel.Range oRng;
var rec_regnskab = Program.qryAktivRegnskab();
string SaveAs = rec_regnskab.Eksportmappe + pSheetName + pReadDate.ToString("_yyyyMMdd_hhmmss") + ".xls";
var MedlemmerAll = from h in Program.karMedlemmer
join d1 in Program.dbData3060.TblMedlem on h.Nr equals d1.Nr into details1
from x in details1.DefaultIfEmpty() //new TblMedlem { Nr = -1, Kon = "X", FodtDato = new DateTime(1900, 1, 1) })
select new clsMedlemInternAll
{
Nr = h.Nr,
Navn = h.Navn,
Kaldenavn = h.Kaldenavn,
Adresse = h.Adresse,
Postnr = h.Postnr,
Bynavn = h.Bynavn,
Telefon = h.Telefon,
Email = h.Email,
Kon = x.Kon,
FodtDato = x.FodtDato,
Bank = h.Bank,
erMedlem = (h.erMedlem()) ? 1 : 0,
indmeldelsesDato = h.indmeldelsesDato,
udmeldelsesDato = h.udmeldelsesDato,
kontingentBetaltTilDato = h.kontingentBetaltTilDato,
opkrævningsDato = h.opkrævningsDato,
kontingentTilbageførtDato = h.kontingentTilbageførtDato,
};
using (new ExcelUILanguageHelper())
{
try
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = false;
//Get a new workbook.
oWB = oXL.Workbooks.Add((Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oWindow = oXL.ActiveWindow;
if (pSheetName.Length > 0)
{
oSheet.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length);
}
int row = 1;
this.MainformProgressBar.Value = 0;
this.MainformProgressBar.Minimum = 0;
this.MainformProgressBar.Maximum = (from h in Program.karMedlemmer select h).Count();
this.MainformProgressBar.Step = 1;
this.MainformProgressBar.Visible = true;
foreach (clsMedlemInternAll m in MedlemmerAll)
{
this.MainformProgressBar.PerformStep();
row++;
Type objectType = m.GetType();
PropertyInfo[] properties = objectType.GetProperties();
int col = 0;
foreach (PropertyInfo property in properties)
{
col++;
string Name = property.Name;
//string NamePropertyType = property.GetValue(m, null).GetType().ToString();
oSheet.Cells[row, col] = property.GetValue(m, null);
if (row == 2)
{
object[] CustomAttributes = property.GetCustomAttributes(false);
foreach (var att in CustomAttributes)
{
Type tp = att.GetType();
if (tp.ToString() == "nsPuls3060.Fieldattr")
{
Fieldattr attr = (Fieldattr)att;
string heading = attr.Heading;
oSheet.Cells[1, col] = heading;
}
}
}
}
}
oRng = (Excel.Range)oSheet.Rows[1, Missing.Value];
oRng.Font.Name = "Arial";
oRng.Font.Size = 12;
oRng.Font.Strikethrough = false;
oRng.Font.Superscript = false;
oRng.Font.Subscript = false;
oRng.Font.OutlineFont = false;
oRng.Font.Shadow = false;
oRng.Font.Bold = true;
oRng.HorizontalAlignment = Excel.Constants.xlCenter;
oRng.VerticalAlignment = Excel.Constants.xlBottom;
oRng.WrapText = false;
oRng.Orientation = 0;
oRng.AddIndent = false;
oRng.IndentLevel = 0;
oRng.ShrinkToFit = false;
oRng.MergeCells = false;
string BottomRight = "J" + row.ToString();
oRng = oSheet.get_Range("J2", BottomRight);
oRng.NumberFormat = "dd-mm-yyyy";
BottomRight = "R" + row.ToString();
oRng = oSheet.get_Range("M2", BottomRight);
oRng.NumberFormat = "dd-mm-yyyy";
oSheet.Cells.EntireColumn.AutoFit();
oWindow.SplitRow = 1;
oWindow.SplitColumn = 2;
oWindow.FreezePanes = true;
oSheet.get_Range("A1", Missing.Value).Select();
for (var i = oWB.Worksheets.Count; i > 0; i--)
{
Excel._Worksheet oSheetWrk = (Excel._Worksheet)oWB.Worksheets.get_Item(i);
if (oSheetWrk.Name != "MedlemIntern")
{
oSheetWrk.Delete();
}
}
oWB.SaveAs(SaveAs, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Saved = true;
oXL.Visible = true;
this.MainformProgressBar.Visible = false;
//oXL.Quit();
//oXL = null;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
private void ecxelPoster()
{
DateTime pReadDate = DateTime.Now;
string pSheetName = "Poster";
Excel.Application oXL = null;;
Excel._Workbook oWB;
Excel._Worksheet oSheetPoster;
Excel._Worksheet oSheetRegnskab;
Excel.Window oWindow;
Excel.Range oRng;
var rec_regnskab = Program.qryAktivRegnskab();
string SaveAs = rec_regnskab.Eksportmappe + pSheetName + pReadDate.ToString("_yyyyMMdd_hhmmss") + ".xls";
var JournalPoster = from h in Program.karPosteringer
join d1 in Program.karKontoplan on h.Konto equals d1.Kontonr into details1
from x in details1.DefaultIfEmpty()
orderby h.Nr
select new clsJournalposter
{
ds = (x.Type == "Drift") ? "D" : "S",
k = IUAP(x.Type, x.DK),
Konto = h.Konto.ToString() + "-" + x.Kontonavn,
Dato = h.Dato,
Bilag = h.Bilag,
Nr = h.Nr,
Id = h.Id,
Tekst = h.Tekst,
Beløb = h.Bruttobeløb,
};
var erMedlem = from h in Program.dbData3060.tblMedlems
where h.Status == 1
select h;
using (new ExcelUILanguageHelper())
{
try
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;
//oXL.Visible = true; //For debug
//Get a new workbook.
oWB = oXL.Workbooks.Add((Missing.Value));
oSheetPoster = (Excel._Worksheet)oWB.ActiveSheet;
oWindow = oXL.ActiveWindow;
if (pSheetName.Length > 0)
{
oSheetPoster.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length);
}
int row = 1;
this.MainformProgressBar.Value = 0;
this.MainformProgressBar.Minimum = 0;
this.MainformProgressBar.Maximum = (from h in Program.karPosteringer select h).Count();
this.MainformProgressBar.Step = 1;
this.MainformProgressBar.Visible = true;
foreach (clsJournalposter m in JournalPoster)
{
this.MainformProgressBar.PerformStep();
row++;
Type objectType = m.GetType();
PropertyInfo[] properties = objectType.GetProperties();
int col = 0;
foreach (PropertyInfo property in properties)
{
col++;
string Name = property.Name;
//string NamePropertyType = property.GetValue(m, null).GetType().ToString();
oSheetPoster.Cells[row, col] = property.GetValue(m, null);
if (row == 2)
{
object[] CustomAttributes = property.GetCustomAttributes(false);
foreach (var att in CustomAttributes)
{
Type tp = att.GetType();
if (tp.ToString() == "nsPuls3060.Fieldattr")
{
Fieldattr attr = (Fieldattr)att;
string heading = attr.Heading;
oSheetPoster.Cells[1, col] = heading;
}
}
}
}
}
oRng = (Excel.Range)oSheetPoster.Rows[1, Missing.Value];
oRng.Font.Name = "Arial";
oRng.Font.Size = 12;
oRng.Font.Strikethrough = false;
oRng.Font.Superscript = false;
oRng.Font.Subscript = false;
oRng.Font.OutlineFont = false;
oRng.Font.Shadow = false;
oRng.Font.Bold = true;
oRng.HorizontalAlignment = Excel.Constants.xlCenter;
oRng.VerticalAlignment = Excel.Constants.xlBottom;
oRng.WrapText = false;
oRng.Orientation = 0;
oRng.AddIndent = false;
oRng.IndentLevel = 0;
oRng.ShrinkToFit = false;
oRng.MergeCells = false;
string BottomRight = "D" + row.ToString();
oRng = oSheetPoster.get_Range("D2", BottomRight);
oRng.NumberFormat = "dd-mm-yyyy";
oSheetPoster.Cells.EntireColumn.AutoFit();
oWindow.SplitRow = 1;
oWindow.FreezePanes = true;
oSheetPoster.get_Range("A1", Missing.Value).Select();
oSheetRegnskab = (Excel._Worksheet)oWB.Worksheets.Add(System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
oRng = oSheetRegnskab.get_Range("C2", Missing.Value);
oRng.Formula = "Antal medlemmer: " + erMedlem.Count().ToString();
//oXL.Visible = true; //For debug
PivotField _pvtField = null;
PivotTable _pivot = oSheetPoster.PivotTableWizard(
XlPivotTableSourceType.xlDatabase, //SourceType
oSheetPoster.get_Range(oSheetPoster.Cells[1, 1], oSheetPoster.Cells[row, 9]), //SourceData
oSheetRegnskab.get_Range("A3", Missing.Value), //TableDestination
"PivotTable1", //TableName
System.Type.Missing, //RowGrand
System.Type.Missing, //CollumnGrand
System.Type.Missing, //SaveData
System.Type.Missing, //HasAutoformat
System.Type.Missing, //AutoPage
System.Type.Missing, //Reserved
System.Type.Missing, //BackgroundQuery
System.Type.Missing, //OptimizeCache
System.Type.Missing, //PageFieldOrder
System.Type.Missing, //PageFieldWrapCount
System.Type.Missing, //ReadData
System.Type.Missing); //Connection
_pvtField = (PivotField)_pivot.PivotFields("ds");
_pvtField.Orientation = XlPivotFieldOrientation.xlRowField;
_pvtField = (PivotField)_pivot.PivotFields("k");
_pvtField.Orientation = XlPivotFieldOrientation.xlRowField;
_pvtField = (PivotField)_pivot.PivotFields("Konto");
_pvtField.Orientation = XlPivotFieldOrientation.xlRowField;
_pvtField = (PivotField)_pivot.PivotFields("Dato");
_pvtField.Orientation = XlPivotFieldOrientation.xlColumnField;
_pvtField = (PivotField)_pivot.PivotFields("Beløb");
_pvtField.Orientation = XlPivotFieldOrientation.xlDataField;
_pvtField.Function = XlConsolidationFunction.xlSum;
_pvtField.NumberFormat = "#,##0";
oSheetRegnskab.Name = "Regnskab";
oRng = oSheetRegnskab.get_Range("D3", Missing.Value);
oRng.Select();
bool[] Periods = { false, false, false, false, true, false, false };
oRng.Group(true, true, Missing.Value, Periods);
oRng = oSheetRegnskab.get_Range("D4", "P4");
oRng.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
oSheetRegnskab.PageSetup.LeftHeader = "&14Regnskab Puls 3060";
oSheetRegnskab.PageSetup.CenterHeader = "";
oSheetRegnskab.PageSetup.RightHeader = "&P af &N";
oSheetRegnskab.PageSetup.LeftFooter = "&Z&F";
oSheetRegnskab.PageSetup.CenterFooter = "";
oSheetRegnskab.PageSetup.RightFooter = "&D&T";
oSheetRegnskab.PageSetup.LeftMargin = oXL.InchesToPoints(0.75);
oSheetRegnskab.PageSetup.RightMargin = oXL.InchesToPoints(0.75);
oSheetRegnskab.PageSetup.TopMargin = oXL.InchesToPoints(1);
oSheetRegnskab.PageSetup.BottomMargin = oXL.InchesToPoints(1);
oSheetRegnskab.PageSetup.HeaderMargin = oXL.InchesToPoints(0.5);
oSheetRegnskab.PageSetup.FooterMargin = oXL.InchesToPoints(0.5);
oSheetRegnskab.PageSetup.PrintHeadings = false;
oSheetRegnskab.PageSetup.PrintGridlines = true;
oSheetRegnskab.PageSetup.CenterHorizontally = false;
oSheetRegnskab.PageSetup.CenterVertically = false;
oSheetRegnskab.PageSetup.Orientation = XlPageOrientation.xlLandscape;
oSheetRegnskab.PageSetup.Draft = false;
oSheetRegnskab.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
oSheetRegnskab.PageSetup.FirstPageNumber = 1;
oSheetRegnskab.PageSetup.Order = XlOrder.xlDownThenOver;
oSheetRegnskab.PageSetup.BlackAndWhite = false;
oSheetRegnskab.PageSetup.Zoom = 100;
oSheetRegnskab.PageSetup.PrintErrors = XlPrintErrors.xlPrintErrorsDisplayed;
oWB.ShowPivotTableFieldList = false;
for (var i = oWB.Worksheets.Count; i > 0; i--)
{
Excel._Worksheet oSheetWrk = (Excel._Worksheet)oWB.Worksheets.get_Item(i);
if ((oSheetWrk.Name != "Regnskab") && (oSheetWrk.Name != "Poster"))
{
oSheetWrk.Delete();
}
}
oSheetRegnskab.get_Range("A1", Missing.Value).Select();
oWB.SaveAs(SaveAs, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Saved = true;
oXL.Visible = true;
this.MainformProgressBar.Visible = false;
this.emailExcelFile(SaveAs, "Puls3060 Regnskab");
//oXL.Quit();
//oXL = null;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
private void exp_excel(bool print)
{
try
{
int i_rec = dtdmkho.Rows.Count, be = 4, dong = 6, sodong = ds.Tables[0].Rows.Count + 6, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong - 1;
tenfile = d.Export_Excel(ds, "danhap");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(d.getIndex(be) + dong.ToString(), d.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(socot - 1) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 0; i < i_rec; i++)
{
osheet.Cells[dong - 2, i *2 + 5] = dtdmkho.Rows[i]["ten"].ToString();
orange = osheet.get_Range(d.getIndex(i * 2 + 4) + "4", d.getIndex(i * 2 + 5) + "4");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
}
osheet.Cells[dong - 2, i_rec *2 + 5] = "Tổng cộng";
orange = osheet.get_Range(d.getIndex(i_rec * 2 + 4) + "4", d.getIndex(i_rec * 2 + 5) + "4");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
for (int i = 1; i <= be; i++)
{
osheet.Cells[dong - 1, i] = get_ten(i + 1);
orange = osheet.get_Range(d.getIndex(i - 1) + "4", d.getIndex(i - 1) + "5");
orange.MergeCells = true;
}
for (int i = be; i < socot; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i % 2);
}
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 2] = d.Syte; osheet.Cells[2, 2] = d.Tenbv;
orange = osheet.get_Range(d.getIndex(1) + "1", d.getIndex(3) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
s_mmyy = tu.Value.ToString().PadLeft(2, '0') + yyyy.Value.ToString().PadLeft(4, '0').Substring(2, 2);
s_tu = tu.Value.ToString().PadLeft(2, '0');
s_den = den.Value.ToString().PadLeft(2, '0');
s_yy = yyyy.Value.ToString().PadLeft(4, '0').Substring(2, 2);
osheet.Cells[1, 4] = "BÁO CÁO LĨNH TỪ " + khott.Text.Trim().ToUpper();
osheet.Cells[2, 4] = (tu.Value == den.Value)?"Tháng : " + s_tu + "/" + yyyy.Value.ToString():"Từ tháng :" + s_tu + "/" + yyyy.Value.ToString() + " đến tháng :" + s_den + "/" + yyyy.Value.ToString();
orange = osheet.get_Range(d.getIndex(3) + "1", d.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exp_excel(bool print)
{
d.check_process_Excel();
ds = dsxml.Copy();
int dong = 2, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong - 1;
tenfile = d.Export_Excel(ds, "sudung");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
//for(int i=0;i<be;i++) osheet.get_Range(d.getIndex(i)+"1",d.getIndex(i)+"1").EntireRow.Insert(Missing.Value);
osheet.get_Range(d.getIndex(3) + dong.ToString(), d.getIndex(socot + 1) + sodong.ToString()).NumberFormat = format_soluong;
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
int pos = 5, colfix = 4;
if (bGia)
{
pos = 6; colfix = 5;
}
foreach (DataRow r in dtmakp.Select("", "stt,makp"))
{
osheet.Cells[dong - 1, pos] = r["tenkp"].ToString();
pos += 1;
}
for (int i = 0; i < colfix; i++)
{
osheet.Cells[dong - 1, i + 1] = (bGia) ? get_ten_gia(i) : get_ten(i);
}
//
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 10;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
// osheet.Cells[1,2]=d.Syte;osheet.Cells[2,2]=d.Tenbv;
// osheet.Cells[1,4]="BÁO CÁO SỬ DỤNG NỘI TRÚ";
// osheet.Cells[2,4]=s_title;
string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text;
if (tu.Text == den.Text)
{
s_title = "Ngày " + tu.Text;
}
osheet.PageSetup.LeftHeader = d.Syte + "\n" + d.Tenbv;
osheet.PageSetup.CenterHeader = "&\"Arial,Bold\"&14BÁO CÁO SỬ DỤNG NỘI TRÚ\n" + s_title;
// orange=osheet.get_Range(d.getIndex(3)+"1",d.getIndex(socot-1)+"2");
// orange.HorizontalAlignment=XlHAlign.xlHAlignCenterAcrossSelection;
// orange.Font.Size=12;
// orange.Font.Bold=true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
private void imp_excel()
{
oxl = new Excel.Application();
oxl.Visible = true;
owb = (Excel._Workbook)(oxl.Workbooks.Add(Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
int i, j, be = 4, k, cot = dt.Columns.Count, dong = dt.Rows.Count;
string ten, tit = (maphuongxa.SelectedIndex == 1)?"TỈNH":"QUẬN";
k = (maphuongxa.SelectedIndex == 0)?2:3;
DataRow r;
for (i = 0; i < cot; i++)
{
ten = dt.Columns[i].ColumnName.ToString();
if (i > k)
{
r = m.getrowbyid(dstmp.Tables[0], "ma='" + ten.Substring(2) + "'");
if (r != null)
{
ten = r["ten"].ToString().Trim();
}
}
else
{
switch (i)
{
case 0: ten = "MÃ ICD";
break;
case 1: ten = "TÊN BỆNH";
break;
case 2: ten = "TỔNG CỘNG";
break;
case 3: ten = "TS TRONG " + tit;
break;
}
}
osheet.Cells[3, i + 1] = ten;
}
for (i = 0; i < dong; i++)
{
for (j = 0; j < cot; j++)
{
osheet.Cells[i + be, j + 1] = dt.Rows[i][j].ToString();
}
}
osheet.get_Range(m.getIndex(0) + "3", m.getIndex(cot - 1) + (dong + be - 1)).Borders.LineStyle = Excel.XlLineStyle.xlContinuous; osheet.get_Range(m.getIndex(2) + "3", m.getIndex(cot) + "3").Orientation = 90;
orange = osheet.get_Range(m.getIndex(0) + "3", m.getIndex(1) + "3");
orange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
orange.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
orange = osheet.get_Range(m.getIndex(0) + "3", m.getIndex(cot) + "3");
orange.EntireColumn.AutoFit();
orange.Font.Bold = true;
dong += be;
osheet.get_Range(m.getIndex(2) + dong.ToString(), m.getIndex(cot) + dong.ToString()).Font.Bold = true;
osheet.Cells[1, 2] = this.Text.Trim().ToUpper();
if (tu.Text != "" && den.Text != "")
{
osheet.Cells[2, 2] = "Từ ngày : " + tu.Text + " đến : " + den.Text;
}
}
private void exp_excel(System.Data.DataTable dts)
{
if (d.check_open_Excel() == true)
{
DialogResult dlg = MessageBox.Show("Bạn phải tắt các chương trình Excel đang chạy trước khi tổng hợp.\nBạn có muốn tự động tắt luôn không?", "Export to Excel", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (dlg == DialogResult.Yes)
{
d.check_process_Excel();
}
else
{
return;
}
}
d.check_process_Excel();
try
{
System.Data.DataTable dtxml = dts;
int be = 9, i_cot = 22, dong = 10, sodong = dtxml.Rows.Count + 10, socot = dtxml.Columns.Count - 1, dongke = sodong + 2;
string tenfile = d.Export_Excel(dtxml, s_tenreport);
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));//,Missing.Value,Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value); //,Missing.Value);
}
osheet.get_Range(d.getIndex(9) + dong.ToString(), d.getIndex(socot - 5) + dongke.ToString()).NumberFormat = "#,##0";
osheet.get_Range(d.getIndex(0) + "7", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
//dong ten cot
for (int i = 0; i < i_cot; i++)
{
osheet.Cells[dong - 1, i + 1] = getTencot(i).ToString();
}
orange = osheet.get_Range(d.getIndex(0) + "9", d.getIndex(i_cot - 1) + "9");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
//end dong ten cot
//dong stt
for (int i = 0; i < i_cot; i++)
{
osheet.Cells[dong, i + 1] = getSTT(i).ToString();
}
orange = osheet.get_Range(d.getIndex(0) + "10", d.getIndex(i_cot - 1) + "10");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Bold = true;
//end dong stt
int so = sodong, j = 0;
//Tinh tong
osheet.Cells[sodong + 1, 2] = "Tổng cộng A + B + C";
for (int k = 21; k <= 21; k++)
{
osheet.Cells[sodong + 1, k] = "=SUM(" + d.getIndex(k - 1) + "9:" + d.getIndex(k - 1) + (sodong).ToString() + ")";
}
orange = osheet.get_Range(d.getIndex(0) + (sodong + 1).ToString(), d.getIndex(socot) + (sodong + 1).ToString());
orange.HorizontalAlignment = XlHAlign.xlHAlignRight;
orange.Font.Bold = true;
//end dong tong
//tieu de
osheet.Cells[1, 1] = "Tên cơ sở khám, chữa bệnh: BỆNH VIỆN NHÂN DÂN 115";
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(3) + "1");
orange.MergeCells = true;
osheet.Cells[2, 1] = "Mã số: 79024";
orange = osheet.get_Range(d.getIndex(0) + "2", d.getIndex(3) + "2");
orange.MergeCells = true;
//s_tenBaoCao = "THỐNG KÊ DỊCH VỤ KỸ THUẬT THANH TOÁN BHYT ";
osheet.Cells[2, socot - 6] = s_tenBaoCao;
s_tieuDeExcel = "THỐNG KÊ DỊCH VỤ KỸ THUẬT THANH TOÁN BHYT ";
osheet.Cells[3, 4] = s_tieuDeExcel;
orange = osheet.get_Range(d.getIndex(3) + "3", d.getIndex(socot - 5) + "3");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 16;
orange.Font.Bold = true;
string s_title = haison1.s_title;
osheet.Cells[4, 4] = s_title;
orange = osheet.get_Range(d.getIndex(3) + "4", d.getIndex(socot - 5) + "4");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 10;
orange.Font.Bold = true;
osheet.Cells[5, 4] = "(Gửi cùng với file dữ liệu hàng tháng)";
orange = osheet.get_Range(d.getIndex(3) + "5", d.getIndex(socot - 5) + "5");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
osheet.Cells[6, socot - 6] = "Đơn vị : đồng";
//end tieu de
//format cột
osheet.Cells[7, 2] = "Thông tin về kết quả trúng thầu cơ sở KCB áp dụng để mua sắm";
orange = osheet.get_Range(d.getIndex(1) + "7", d.getIndex(4) + "7");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.MergeCells = true;
orange.Font.Bold = true;
osheet.Cells[7, 19] = "Số lượng";
orange = osheet.get_Range(d.getIndex(18) + "7", d.getIndex(19) + "7");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.MergeCells = true;
orange.Font.Bold = true;
/*
* osheet.Cells[7, 10] = "TỔNG CHI PHÍ KHÁM, CHỮA BỆNH BHYT";
* orange = osheet.get_Range(d.getIndex(9) + "7", d.getIndex(20) + "7");
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
* orange.Font.Bold = true;
*
* osheet.Cells[7, 23] = "Đề nghị BHXH thanh toán ";
* orange = osheet.get_Range(d.getIndex(22) + "7", d.getIndex(23) + "7");
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
* orange.Font.Bold = true;
*
* osheet.Cells[8, 11] = "Không áp dụng tỷ lệ thanh toán";
* orange = osheet.get_Range(d.getIndex(10) + "8", d.getIndex(15) + "8");
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
* orange.Font.Bold = true;
*
* osheet.Cells[8, 17] = "Thanh toán theo tỷ lệ";
* orange = osheet.get_Range(d.getIndex(16) + "8", d.getIndex(18) + "8");
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
* orange.Font.Bold = true;*/
//megre cot
for (int i = 0; i < i_cot; i++)
{
if ((i > 0 && i < 5) || (i > 17 && i < 20))
{
orange = osheet.get_Range(d.getIndex(i) + "8", d.getIndex(i) + "9");
orange.MergeCells = true;
}
else
{
orange = osheet.get_Range(d.getIndex(i) + "7", d.getIndex(i) + "9");
orange.MergeCells = true;
}
}/*
* //end
* //end format cột
*
* //footer
* osheet.Cells[sodong + 2, 1] = "Số tiền đề nghị thanh toán (viết bằng chữ) ";// +data.doiTienThanhSo(t_deNghiThanhToan);
* orange = osheet.get_Range(d.getIndex(0) + (sodong + 2), d.getIndex(socot - 1) + (sodong + 2));
* orange.MergeCells = true;
*
* osheet.Cells[sodong + 5, 2] = "Người lập biểu";
* orange = osheet.get_Range(d.getIndex(1) + (sodong + 5), d.getIndex(2) + (sodong + 5));
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
*
* osheet.Cells[sodong + 6, 2] = "(Ký, họ tên)";
* orange = osheet.get_Range(d.getIndex(1) + (sodong + 6), d.getIndex(2) + (sodong + 6));
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
* orange.Font.Italic = true;
*
* osheet.Cells[sodong + 5, 7] = "Trưởng phòng KHTH";
* orange = osheet.get_Range(d.getIndex(6) + (sodong + 5), d.getIndex(8) + (sodong + 5));
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
*
* osheet.Cells[sodong + 6, 7] = "(Ký, họ tên)";
* orange = osheet.get_Range(d.getIndex(6) + (sodong + 6), d.getIndex(8) + (sodong + 6));
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
* orange.Font.Italic = true;
*
* /* osheet.Cells[sodong + 5, 15] = "Kế toán trưởng";
* orange = osheet.get_Range(d.getIndex(14) + (sodong + 5), d.getIndex(15) + (sodong + 5));
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
*
* osheet.Cells[sodong + 6, 15] = "(Ký, họ tên)";
* orange = osheet.get_Range(d.getIndex(14) + (sodong + 6), d.getIndex(15) + (sodong + 6));
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
* orange.Font.Italic = true;
*
* osheet.Cells[sodong + 4, 20] = "....., ngày.....tháng....năm....";
* orange = osheet.get_Range(d.getIndex(18) + (sodong + 4), d.getIndex(21) + (sodong + 4));
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
* orange.Font.Italic = true;
*
* osheet.Cells[sodong + 5, 20] = "Thủ trưởng đơn vị";
* orange = osheet.get_Range(d.getIndex(19) + (sodong + 5), d.getIndex(20) + (sodong + 5));
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
*
* osheet.Cells[sodong + 6, 20] = "(Ký, họ tên, đóng dấu)";
* orange = osheet.get_Range(d.getIndex(19) + (sodong + 6), d.getIndex(20) + (sodong + 6));
* orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
* orange.MergeCells = true;
* orange.Font.Italic = true;
*
* orange = osheet.get_Range(d.getIndex(0) + (sodong + 3), d.getIndex(socot - 1) + (sodong + 5));
* orange.Font.Bold = true;*/
//format chung
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Times New Roman";
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
orange = osheet.get_Range(d.getIndex(0) + "7", d.getIndex(socot) + sodong.ToString());
orange.Font.Size = 8;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
//end
// orange = osheet.get_Range(d.getIndex(socot - 7) + "6", d.getIndex(socot - 7) + "6");
// if (print) osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// else
oxl.Visible = true;
//end format chung
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void exp_excel(bool print, bool biendong)
{
try
{
int be = 3, dong = 5, sodong = ds.Tables[0].Rows.Count + 5, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 1;
tenfile = d.Export_Excel(ds, "theodoigia");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(d.getIndex(be) + dong.ToString(), d.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.000";
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 1; i < dong; i++)
{
osheet.Cells[dong - 1, i] = get_ten(i - 1);
}
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 2] = d.Syte; osheet.Cells[2, 2] = d.Tenbv;
s_mmyy = tu.Value.ToString().PadLeft(2, '0') + yyyy.Value.ToString().PadLeft(4, '0').Substring(2, 2);
s_tu = tu.Value.ToString().PadLeft(2, '0');
s_den = den.Value.ToString().PadLeft(2, '0');
s_yy = yyyy.Value.ToString().PadLeft(4, '0').Substring(2, 2);
osheet.Cells[1, 4] = "THEO DÕI BIẾN ĐỘNG GIÁ";
osheet.Cells[2, 4] = (tu.Value == den.Value && yy.Value == yyyy.Value)?"Tháng : " + s_tu + "/" + yyyy.Value.ToString():"Từ tháng :" + s_tu + "/" + yy.Value.ToString() + " đến tháng :" + s_den + "/" + yyyy.Value.ToString();
orange = osheet.get_Range(d.getIndex(3) + "1", d.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
osheet.get_Range(d.getIndex(4) + "4", d.getIndex(ds.Tables[0].Columns.Count) + "4").NumberFormat = "@";
osheet.get_Range(d.getIndex(4) + "4", d.getIndex(ds.Tables[0].Columns.Count) + "4").RowHeight = 24;
if (!biendong)
{
for (int i = 0; i < dtngay.Rows.Count; i++)
{
osheet.Cells[dong - 1, i + 5] = dtngay.Rows[i]["ngaydoi"].ToString();
}
}
else
{
osheet.Cells[dong - 1, 5] = s_tu + "/" + yy.Value.ToString();
if (s_tu != s_den)
{
osheet.Cells[dong - 1, 6] = s_den + "/" + yyyy.Value.ToString();
osheet.Cells[dong - 1, 7] = "Chênh lệch";
}
}
orange = osheet.get_Range(d.getIndex(4) + "5", d.getIndex(socot + 1) + sodong.ToString());
orange.EntireColumn.AutoFit();
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}