/// <summary>
/// 设置行高
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="endRow">结束行</param>
/// <param name="height">行高</param>
public void SetRowHeight(int startRow, int endRow, int height)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing];
range.RowHeight = height;
}
//--------------------------------------------------------------------------------------------------------
/// <summary>
/// 关闭Excel文件，释放对象；最后一定要调用此函数，否则会引起异常
/// </summary>
/// <param></param>
public void CloseExcelApplication()
{
try
{
xlsWbs = null;
xlsWb = null;
xlsWs = null;
xlsRg = null;
if (xlsApp != null)
{
xlsApp.ActiveWorkbook.Close(false, null, null);
xlsApp.Workbooks.Close();
//Object missing = Type.Missing;
xlsApp.Quit();
xlsApp = null;
//ReleaseAllRef(excelApplication);//Error
}
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="startColumn">起始列(列对应的字母)</param>
/// <param name="endColumn">结束列(列对应的字母)</param>
/// <param name="width"></param>
public void SetColumnWidth(string startColumn, string endColumn, int width)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];
range.ColumnWidth = width;
}
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";
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";
osheet.Cells[2, 4] = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày : " + tu.Text + " đến : " + den.Text;
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>
/// <param name="columnNum">列号</param>
public void ColumnAutoFit(string column)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 字体样式(加粗,斜体,下划线)
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="isBold">是否加粗</param>
/// <param name="isItalic">是否斜体</param>
/// <param name="underline">下划线类型</param>
public void FontStyle(int startRow, int startColumn, int endRow, int endColumn, bool isBold, bool isItalic, UnderlineStyle underline)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.Bold = isBold;
range.Font.Underline = underline;
range.Font.Italic = isItalic;
}
/// <summary>
/// 插入一行
/// </summary>
/// <param name="CellRowID">要插入所在行的索引位置，插入后其原有行下移</param>
/// <param name="RowNum">要插入行的个数</param>
public void InsertRow(int CellRowID, int RowNum)//插入空行
{
if (CellRowID <= 0)
{
throw new Exception("行索引超出范围！");
}
if (RowNum <= 0)
{
throw new Exception("插入行数无效！");
}
try
{
xlsRg = (Excel.Range)xlsWs.Rows[CellRowID, Missing.Value];
for (int i = 0; i < RowNum; i++)
{
xlsRg.Insert(Excel.XlDirection.xlDown);
}
xlsRg = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
public void mergeOneLineCells(string startCell, string endCell)
{
//合并一行单元格
xlsRg = xlsWs.get_Range(startCell, endCell);
//excelRange.Merge(true);
xlsRg.MergeCells = true;
}
/// <summary>
/// 自动调整行高
/// </summary>
/// <param name="columnNum">列号</param>
public void RowAutoFit(int rowNum)
{
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing];
range.EntireColumn.AutoFit();
}
//Ribbon界面的回调函数，响应事件，将Excel表格数据写入CAD
public void OnButton1Pressed(IRibbonControl control)
{
//选取Excel表格数据
wbk = app.ActiveWorkbook;
wsh = (Worksheet)wbk.ActiveSheet;
Excel.Range rngLeftTop = null;
Excel.Range rngRightButtom = null;
rngLeftTop = (Excel.Range)app.InputBox("点击左上角单元格", Type: 8);
rngRightButtom = (Excel.Range)app.InputBox("点击右下角单元格", Type: 8);
object[,] data = (object[, ])wsh.Range[rngLeftTop.Address + ":" + rngRightButtom.Address].Value2;
//把数据导入Access数据库
try
{
AccessDataBase.WriteDB(data);
MessageBox.Show("选定的数据读取完毕，点击确定导入CAD");
}
catch (SystemException ex)
{
MessageBox.Show(ex.ToString());
}
//选定的数据读取完毕，点击确定导入CAD
if (rngLeftTop != null & rngRightButtom != null)
{
//打开cad
try
{
AcadApp = (Autodesk.AutoCAD.Interop.AcadApplication)System.Runtime.InteropServices.Marshal.GetActiveObject("AutoCAD.Application");
AcadDoc = AcadApp.ActiveDocument;
}
catch
{
OpenFileDialog op = new OpenFileDialog();
op.Filter = "CAD文件(*.dwg)|*.dwg|CAD图形文件(*.dxf)|*.dxf";
op.Title = "打开CAD文件";
op.ShowDialog();
string filePath = op.FileName;
if (filePath == "")
{
MessageBox.Show("选择CAD文件无效!!", "文件无效!!");
}
else
{
AcadApp = new Autodesk.AutoCAD.Interop.AcadApplication();
AcadDoc = AcadApp.Documents.Open(filePath, null, null);
}
}
AcadApp.Application.Visible = true;
//使CAD程序跳到在最前面，需要添加引用“Microsoft.VisualBasic”
Microsoft.VisualBasic.Interaction.AppActivate(AcadApp.Caption);
//让CAD自动执行netload命令加载程序集DLL,如果注册表加载方法无效的话
AcadDoc.SendCommand("(command \"_netload\" \"" + @"C:\\Windows\\System\\xl2cad\\xl2cad.dll" + "\") ");
AcadDoc.SendCommand("zdbtext ");
}
else
{
MessageBox.Show("没有选择数据！");
}
}
/// <summary>
/// 自动调整列宽
/// </summary>
/// <param name="columnNum">列号</param>
public void ColumnAutoFit(int columnNum)
{
string strcolumnNum = GetColumnName(columnNum);
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];
range.EntireColumn.AutoFit();
}
private void exp_excel(bool print)
{
try
{
ttb.check_process_Excel();
ds = dsxml.Copy();
int be = 2, dong = 4, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong - 1;
tenfile = ttb.Export_Excel(ds, "dutru");
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(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(ttb.getIndex(be - 1) + dong.ToString(), ttb.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(ttb.getIndex(0) + "3", ttb.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
int pos = 7;
foreach (DataRow r in dtmakp.Select("makp<>''", "stt,makp"))
{
osheet.Cells[dong - 1, pos] = r["tenkp"].ToString();
pos += 1;
}
//osheet.Cells[dong-1,pos]="Tồn "+makho.Text;
for (int i = 0; i < 6; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i);
}
orange = osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(socot) + sodong.ToString());
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.Cells[1, 2] = ttb.Syte; osheet.Cells[2, 2] = ttb.Tenbv;
s_mmyy = tu.Value.ToString().PadLeft(2, '0') + yyyy.Value.ToString().PadLeft(4, '0').Substring(2, 2);
osheet.Cells[1, 4] = "TỔNG HỢP DÙ TRÙ";
string s_title = ttb.title(tu.Value.ToString(), tu.Value.ToString()) + " năm " + yyyy.Value.ToString();
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 { }
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="startColumn">起始列</param>
/// <param name="endColumn">结束列</param>
/// <param name="width"></param>
public void SetColumnWidth(int startColumn, int endColumn, int width)
{
string strStartColumn = GetColumnName(startColumn);
string strEndColumn = GetColumnName(endColumn);
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing];
range.ColumnWidth = width;
}
private void exp_excel()
{
try
{
string s;
int be = 3, dong = 6, sodong = ds.Tables[0].Rows.Count + 6, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 2;
tenfile = d.Export_Excel(ds, "biendong");
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.Cells[dong - 2, 3] = "Tên hoạt chất";
osheet.Cells[dong - 2, 4] = "Tên thuốc - hàm lượng";
osheet.Cells[dong - 2, 5] = "ĐVT";
osheet.Cells[dong - 2, 6] = "Diễn giải";
for (int i = 0; i < i_rec; i++)
{
s = ds.Tables[0].Columns[i + 6].ColumnName.ToString();
osheet.Cells[dong - 2, i + 7] = "'" + s.Substring(4, 2) + "/" + s.Substring(2, 2) + "/" + s.Substring(0, 2);
}
osheet.Cells[dong - 2, i_rec + 7] = "Tỷ lệ";
osheet.Cells[dong - 2, i_rec + 8] = "Ghi chú";
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(0) + "1").EntireColumn.Delete(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;
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;
osheet.Cells[1, 3] = "BIẾN ĐỘNG GIÁ THUỐC";
osheet.Cells[2, 3] = "Từ ngày " + tu.Text + " đến " + den.Text;
orange = osheet.get_Range(d.getIndex(2) + "1", d.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
oxl.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void setOneCellBorder(int CellRowID, int CellColumnID)
{
//设置某个单元格的边框
xlsRg = (Excel.Range)xlsWs.Cells[CellRowID, CellColumnID];
xlsRg.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
xlsRg.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
xlsRg.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
xlsRg.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
//excelRange.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
//excelRange.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
}
private void exp_excel(bool print)
{
d.check_process_Excel();
ds = dsxml.Copy();
int be = 2, dong = 4, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong - 1;
tenfile = d.Export_Excel(ds, "cosotutruc");
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(be - 1) + dong.ToString(), d.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(d.getIndex(0) + "3", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 0; i < 8; i++)
{
osheet.Cells[dong - 1, i + 1] = get_ten(i);
}
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.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Ú";
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.Cells[2, 4] = 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;
}
}
public void setCellsBorder(string startCell, string endCell)
{
//设置某个范围内的单元格的边框
xlsRg = xlsWs.get_Range(startCell, endCell);
xlsRg.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
xlsRg.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
xlsRg.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
xlsRg.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
xlsRg.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
//excelRange.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
}
private void FillCellsWithData(System.Data.DataTable dt, int pos, int row)
{
Product p = new Product();
int counter = row;
int itemsTotal = dt.Rows.Count;
string Quant = "B" + pos.ToString();
string ID = "A" + pos.ToString();
string Name = "C" + pos.ToString();
string Name2 = "D" + pos.ToString();
string Price = "E" + pos.ToString();
string Discount = "F" + pos.ToString();
string Total = "G" + pos.ToString();
Excel.Range QuantCell = (Excel.Range)excelWorksheet.get_Range(Quant, Quant);
QuantCell.Value2 = dt.Rows[counter]["Quantity"].ToString();
//Excel.Range IDCell = (Excel.Range)excelWorksheet.get_Range(ID, ID);
//int item = counter + 1;
//IDCell.Value2 = item.ToString();
Excel.Range PriceCell = (Excel.Range)excelWorksheet.get_Range(Price, Price);
PriceCell.Value2 = dt.Rows[counter]["Price"].ToString();
if (m_iInvoiceType == (int)InvoiceType.Sale)
{
Excel.Range DiscountCell = (Excel.Range)excelWorksheet.get_Range(Discount, Discount);
DiscountCell.Value2 = dt.Rows[counter]["Discount"].ToString();
m_TotalDiscount += decimal.Parse(dt.Rows[counter]["Discount"].ToString());
Excel.Range TotalCell = (Excel.Range)excelWorksheet.get_Range(Total, Total);
TotalCell.Value2 = dt.Rows[counter]["Grand Total"].ToString();
//m_TotalDue += decimal.Parse(dt.Rows[counter]["Grand Total"].ToString());
}
Excel.Range NameCell = (Excel.Range)excelWorksheet.get_Range(Name, Name2);
//p = p.GetProduct(Int32.Parse(dt.Rows[counter]["ID"].ToString()));
if (m_iInvoiceType == (int)InvoiceType.Sale)
{
NameCell.Value2 = dt.Rows[counter]["Name"].ToString();
if (NameCell.Value2.ToString().Contains("<< "))
{
QuantCell.Value2 = "";
PriceCell.Value2 = "";
}
}
else
{
NameCell.Value2 = dt.Rows[counter]["Product"].ToString();
}
p = null;
}
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, "cosotutruc");
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(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;
foreach (DataRow r in dtmakp.Select("makp<>''", "stt,makp"))
{
osheet.Cells[dong - 1, pos] = r["tenkp"].ToString();
pos += 1;
}
for (int i = 0; i < 4; i++)
{
osheet.Cells[dong - 1, i + 1] = 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";
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 CƠ SỐ TỦ TRỰC\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;
}
}
public static void Merge(string sourceFile, string baseFile, string targetFile, int rowCount, int columnCount)
{
object MISSING = Type.Missing;
Excel.Application excel = new Excel.Application();
try
{
Excel.Workbook sourceBook = excel.Workbooks.Open(sourceFile,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING);
Excel.Worksheet sourceSheet = (Excel.Worksheet)sourceBook.Sheets[1];
Excel.Workbook baseBook = excel.Workbooks.Open(baseFile,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING);
Excel.Worksheet baseSheet = (Excel.Worksheet)baseBook.Sheets[1];
//baseSheet.Unprotect(string.Empty);
for (int column = 1; column <= columnCount; column++)
{
for (int row = 1; row <= rowCount; row++)
{
Excel.Range baseRange = (Excel.Range)baseSheet.Cells[row, column];
if (baseRange.AllowEdit)
{
Excel.Range sourceRange = (Excel.Range)sourceSheet.Cells[row, column];
baseRange.Value2 = sourceRange.Value2;
}
}
}
//baseSheet.Protect(string.Empty,
// MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
// MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING);
sourceBook.Close(false, MISSING, MISSING);
baseBook.SaveCopyAs(targetFile);
baseBook.Close(false, MISSING, MISSING);
}
finally
{
Office.ExcelHelper.Kill(excel);
}
Thread.Sleep(100);
}
private void exp_excel(bool print)
{
d.check_process_Excel();
ds = dsxml.Copy();
int k = 7, dong = 1, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong;
tenfile = d.Export_Excel(ds, "BCNHAPKHO");
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(1) + 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;
for (int i = 0; i < 6; i++)
{
osheet.Cells[dong, i + 1] = get_ten(i);
}
for (int j = Convert.ToInt16(tu.Value); j <= Convert.ToInt16(den.Value); j++)
{
osheet.Cells[dong, k++] = "Tháng " + j.ToString().PadLeft(2, '0');
}
osheet.Cells[dong, k] = "Tổng cộng";
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 KHO\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;
}
}
/// <summary>
/// 设置一个Cell的数据格式
/// </summary>
/// <param name="CellRowID">CellRowID是cell的行索引</param>
/// <param name="CellColumnID">CellColumnID是cell的列索引</param>
///<param name="Value">数据格式</param>
public void setOneCellNumberFormat(int CellRowID, int CellColumnID, string numberFormat)
{
try
{
xlsRg = (Excel.Range)xlsWs.Cells[CellRowID, CellColumnID];
xlsRg.NumberFormatLocal = numberFormat;
xlsRg = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
/// <summary>
/// 向一个Cell写入数据
/// </summary>
/// <param name="CellRowID">CellRowID是cell的行索引</param>
/// <param name="CellColumnID">CellColumnID是cell的列索引</param>
///<param name="Value">要写入该单元格的数据值</param>
public void setOneCellValue(int CellRowID, int CellColumnID, string Value)
{
try
{
xlsRg = (Excel.Range)xlsWs.Cells[CellRowID, CellColumnID];
xlsRg.Value2 = Value;//Value2?
xlsRg = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
/// <summary>
/// 向连续区域一次性写入数据;只有在区域连续和写入的值相同的情况下可以使用方法
/// </summary>
/// <param name="StartCell">StartCell是要写入区域的左上角单元格</param>
/// <param name="EndCell">EndCell是要写入区域的右下角单元格</param>
/// <param name="Value">要写入指定区域所有单元格的数据值</param>
public void setCellsValue(string StartCell, string EndCell, string Value)
{
try
{
xlsRg = xlsWs.get_Range(StartCell, EndCell);
xlsRg.Value2 = Value;
xlsRg = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
/// <summary>
/// 拷贝区域.限制：在同一个工作表中复制
/// </summary>
/// <param name="SourceStart">源区域的左上角单元格</param>
/// <param name="SourceEnd">源区域的右下角单元格</param>
/// <param name="DesStart">目标区域的左上角单元格</param>
/// <param name="DesEnd">目标区域的右下角单元格</param>
public void CopyCells(string SourceStart, string SourceEnd, string DesStart, string DesEnd)
{
try
{
xlsCSRg = xlsWs.get_Range(SourceStart, SourceEnd);
xlsRg = xlsWs.get_Range(DesStart, DesEnd);
xlsCSRg.Copy(xlsRg);
xlsCSRg = null;
xlsRg = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
public static void DataToColumnHead(DataRow[] drs, Worksheet sheet, int StartRow, int StartCol, int ColHeight, int ColWidth, string[,] Fields, ref int RowNum)
{
if ((drs.Length != 0) && (Fields != null))
{
int num = StartRow;
int num2 = StartCol;
int length = drs.Length;
for (int i = 0; i < length; i++)
{
DataRow dr = drs[i];
for (int j = 0; j < ColHeight; j++)
{
for (int k = 0; k < ColWidth; k++)
{
string columnName = Fields[j, k];
if ((columnName != null) && (columnName.Trim() != ""))
{
Excel.Range range = TExcel.GetCell(sheet, num + j, num2 + k);
try
{
string str;
if (((str = columnName.ToUpper()) != null) && (string.IsInterned(str) == "@@ROWNUM"))
{
RowNum++;
range.Value = (int)RowNum;
}
else
{
range.Value = TExcel.GetItemValue(dr, columnName, false);
}
}
catch
{
}
finally
{
TExcel.DisposeObject(range);
}
}
}
}
num2 += ColWidth;
}
}
}
private void butIn_Click(object sender, System.EventArgs e)
{
d.check_process_Excel();
int be = 3;
int dong = dsxml.Tables[0].Rows.Count + be + 1, socot = dsxml.Tables[0].Columns.Count;
string ten, tenfile = d.Export_Excel(dsxml, "sudungthuoc");
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 < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
osheet.Cells[be + 1, 1] = "Tên thuốc - hàm lượng";
osheet.Cells[be + 1, 2] = "ĐVT";
int j = 3;
for (; j < socot - 1; j++)
{
ten = dsxml.Tables[0].Columns[j].ColumnName.Substring(2);
osheet.Cells[be + 1, j] = ten.Substring(6, 2) + "/" + ten.Substring(4, 2) + "/" + ten.Substring(2, 2);
}
osheet.Cells[be + 1, j] = "Tổng cộng";
orange = osheet.get_Range(m.getIndex(0) + "4", m.getIndex(socot - 2) + "4");
orange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
orange.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
osheet.get_Range(d.getIndex(be - 1) + "4", d.getIndex(socot - 2) + "4").Orientation = 90;
osheet.get_Range(d.getIndex(0) + "4", d.getIndex(socot - 2) + dong.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
osheet.get_Range(m.getIndex(0) + "4", m.getIndex(socot - 2) + dong.ToString()).EntireColumn.AutoFit();
osheet.Cells[1, 1] = "Họ tên :" + dataGrid1[dataGrid1.CurrentCell.RowNumber, 1].ToString().Trim() + "-" + dataGrid1[dataGrid1.CurrentCell.RowNumber, 2].ToString().Trim();
osheet.Cells[2, 1] = "Ngày vào :" + dataGrid1[dataGrid1.CurrentCell.RowNumber, 5].ToString();
osheet.Cells[2, 4] = "Ngày phẫu thủ thuật :" + dataGrid1[dataGrid1.CurrentCell.RowNumber, 6].ToString().Substring(0, 10);
osheet.Cells[3, 1] = "Phương pháp phẫu thủ thuật :" + dataGrid1[dataGrid1.CurrentCell.RowNumber, 7].ToString().Trim();
oxl.Visible = true;
}
/// <summary>
/// 读取一个连续区域的Cell的值(矩形区域，包含一行或一列,或多行，多列)，返回一个一维字符串数组。
/// </summary>
/// <param name="StartCell">StartCell是要写入区域的左上角单元格</param>
/// <param name="EndCell">EndCell是要写入区域的右下角单元格</param>
/// <returns>值的集合</returns>
public string[] getCellsValue(string StartCell, string EndCell)
{
string[] sValue = null;
xlsRg = (Excel.Range)xlsWs.get_Range(StartCell, EndCell);
sValue = new string[xlsRg.Count];
int rowStartIndex = ((Excel.Range)xlsWs.get_Range(StartCell, StartCell)).Row; //起始行号
int columnStartIndex = ((Excel.Range)xlsWs.get_Range(StartCell, StartCell)).Column; //起始列号
int rowNum = xlsRg.Rows.Count; //行数目
int columnNum = xlsRg.Columns.Count; //列数目
int index = 0;
for (int i = rowStartIndex; i < rowStartIndex + rowNum; i++)
{
for (int j = columnStartIndex; j < columnNum + columnStartIndex; j++)
{
//读到空值null和读到空串""分别处理
sValue[index] = ((Excel.Range)xlsWs.Cells[i, j]).Text.ToString();
index++;
}
}
return(sValue);
}
public void SetFont(string fromAddress, string toAddress, string fontName, int fontSize)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Font.Name = fontName;
_excelRange.Font.Size = fontSize;
}
/// <summary>
/// Write value to worksheet
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="objValue">ReportItem or FieldItem or other value</param>
public void WriteValue(PointInfo startPoint, PointInfo endPoint, object objValue)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
objRange.Value2 = objValue;
}
public void DeleteRow(PointInfo startPoint, PointInfo endPoint)
{
object[] cell = this.ConvertPointToCell(startPoint, endPoint);
object startCell = cell[0];
object endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
objRange.Select();
objRange.Rows.Delete(Excel.XlDirection.xlUp);//������
}
/// <summary>
/// Set font of excel sheet area
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="font"></param>
public void SetFont(PointInfo startPoint, PointInfo endPoint, System.Drawing.Font font)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
if (font != null)
{
objRange = CurSheet.get_Range(startCell, endCell);
if (font.Bold)
{
objRange.Font.Bold = font.Bold;
}
if (font.Italic)
{
objRange.Font.Italic = font.Italic;
}
objRange.Font.Name = font.Name;
objRange.Font.Size = font.Size;
if (font.Strikeout)
{
objRange.Font.Strikethrough = font.Strikeout;
}
if (font.Underline)
{
objRange.Font.Underline = font.Underline;
}
}
}
public void SetRowHeight(PointInfo startPoint, PointInfo endPoint, double height)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
if (height != 0)
{
objRange.RowHeight = height;
}
}
public void SetBackgroundPatternColor(string fromAddress, string toAddress, System.Drawing.Color color)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
_excelRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(color);
}
public void SetStrikeThrough(string fromAddress, string toAddress)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Font.Strikethrough = true;
}
public void SetRowHight(string beginRow, string endRow, double hight)
{
_excelRange = _excelSheet.get_Range(beginRow, endRow );
_excelRange.EntireRow.RowHeight = hight;
}
public void SetOulineBorders(string fromAddress, string toAddress, int lineWeight)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.Constants.xlSolid;
_excelRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = lineWeight;
_excelRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.Constants.xlSolid;
_excelRange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = lineWeight;
_excelRange.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.Constants.xlSolid;
_excelRange.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = lineWeight;
_excelRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.Constants.xlSolid;
_excelRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = lineWeight;
}
public void SetOrientation(string fromAddress, string toAddress, Excel.XlOrientation orientation)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Orientation = orientation;
}
public void SetColumnWidth(string beginCol, string endCol, double width)
{
_excelRange = _excelSheet.get_Range(beginCol + "1", endCol + "1");
_excelRange.EntireColumn.ColumnWidth = width;
}
public void SetItalic(string fromAddress, string toAddress)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Font.Italic = true;
}
public void SetColumnWidthAndAuto_ByMin(string beginCol, string endCol, double width)
{
int beginColIndex = CalculateExcelColumnIndex_Number(beginCol);
int endColIndex = CalculateExcelColumnIndex_Number(endCol);
if (beginColIndex > endColIndex)
{
int temp = beginColIndex;
beginColIndex = endColIndex;
endColIndex = beginColIndex;
}
for (int i = beginColIndex; i <= endColIndex; i++)
{
string strColIndex = CalculateExcelColumnIndex_Alphabet(i);
_excelRange = _excelSheet.get_Range(strColIndex + "1", strColIndex + "1");
_excelRange.EntireColumn.AutoFit();
if ((double)_excelRange.EntireColumn.ColumnWidth > width)
{
_excelRange.EntireColumn.ColumnWidth = width;
}
}
}
public void SetTextColor(string fromAddress, string toAddress, System.Drawing.Color color)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Font.Color = System.Drawing.ColorTranslator.ToOle(color);
}
public void SetBold(string fromAddress, string toAddress)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Font.Bold = true;
}
public void SetUndeline(string fromAddress, string toAddress)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Font.Underline = true;
}
public void SetAlign(string fromAddress, string toAddress, Excel.XlHAlign horizontalAlign, Excel.XlVAlign verticalAlign)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.HorizontalAlignment = horizontalAlign;
_excelRange.VerticalAlignment = verticalAlign;
}
public void SetWrapText(string fromAddress, string toAddress, bool isWrapText)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.WrapText = isWrapText;
}
public void MergeCells(string fromAddress, string toAddress, bool autoAlignCenter)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.MergeCells = true;
if (autoAlignCenter)
{
_excelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
}
public void WriteBlock(string startCellAddress, int rowCount, int colCount, object dataBlock)
{
_excelRange = _excelSheet.get_Range(startCellAddress, _value);
_excelRange = _excelRange.get_Resize(rowCount, colCount);
_excelRange.set_Value(_value, dataBlock);
}
/// <summary>
/// Set excel row height
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="font">letter font</param>
/// <param name="height">unit by letter count</param>
public void SetRowHeight(PointInfo startPoint, PointInfo endPoint, System.Drawing.Font font, int height)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
if (height != 0)
{
objRange.RowHeight = UnitConversion.GetLetterHeight(height, font);
}
}
public void WriteBlock(int rowIndex, int colIndex, int rowCount, int colCount, object dataBlock)
{
_excelRange = _excelSheet.get_Range(GetExcelAddress(colIndex, rowIndex), _value);
_excelRange = _excelRange.get_Resize(rowCount, colCount);
_excelRange.set_Value(_value, dataBlock);
}
/// <summary>
/// Insert picture to excel sheet
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="imagePath"></param>
public void InsertPicutre(PointInfo startPoint, PointInfo endPoint, Image image)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
string imagePath = "";
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
imagePath = @"C:\" + Guid.NewGuid().ToString() + ".jpg";
((Bitmap)image).Save(imagePath);
((Excel.Pictures)CurSheet.Pictures(mValue)).Insert(imagePath, mValue);
pictureIndex++;
((Excel.Picture)CurSheet.Pictures(pictureIndex)).Left = (double)objRange.Left;
((Excel.Picture)CurSheet.Pictures(pictureIndex)).Top = (double)objRange.Top;
double height = ((Excel.Picture)CurSheet.Pictures(pictureIndex)).Height;
if ((double)objRange.RowHeight < height)
{
objRange.RowHeight = height;
}
File.Delete(imagePath);
}
public void WriteCell(string cellAddress, object value, string strFont, int size)
{
_excelRange = _excelSheet.get_Range(cellAddress, _value);
//_excelRange = _excelRange.get_Resize(10, 4);
_excelRange.set_Value(_value, value);
_excelRange.Cells.Font.Name = strFont;
_excelRange.Cells.Font.Size = size;
}
public void CopyRange(PointInfo srcStartPoint, PointInfo srcEndPoint, PointInfo desStartPoint)
{
#region Variable Definition
object[] cell = null;
object srcStartCell = null;
object srcEndCell = null;
object desStartCell = null;
object desEndCell = null;
Excel.Range desRange = null;
#endregion
cell = this.ConvertPointToCell(srcStartPoint, srcEndPoint);
srcStartCell = cell[0];
srcEndCell = cell[1];
objRange = this.CurSheet.get_Range(srcStartCell, srcEndCell);
cell = this.ConvertPointToCell(desStartPoint, desStartPoint);
desStartCell = cell[0];
desEndCell = cell[1];
desRange = this.CurSheet.get_Range(desStartCell, desStartCell);
objRange.Copy(desRange);
desRange = null;
}
public void SetColumnAutoFit(string beginCol, string endCol)
{
_excelRange = _excelSheet.get_Range(beginCol + "1", endCol + "1");
_excelRange.EntireColumn.AutoFit();
}
public void SetFontProperty(string fromAddress, string toAddress, bool isBold, bool isItalic, bool isStikeThrough,
bool isUnderline, bool isSubscript, bool isSuperscript)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Font.Bold = isBold;
_excelRange.Font.Italic = isItalic;
_excelRange.Font.Strikethrough = isStikeThrough;
_excelRange.Font.Underline = isUnderline;
_excelRange.Font.Subscript = isSubscript;
_excelRange.Font.Superscript = isSuperscript;
}
public void SetDiagonalDown_Up(string fromAddress, string toAddress, int lineWeight)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Borders[Excel.XlBordersIndex.xlDiagonalDown].Weight = lineWeight;
_excelRange.Borders[Excel.XlBordersIndex.xlDiagonalUp].Weight = lineWeight;
}
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 = ttb.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(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.000";
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());
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;
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(ttb.getIndex(3) + "1", ttb.getIndex(socot - 1) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
osheet.get_Range(ttb.getIndex(4) + "4", ttb.getIndex(ds.Tables[0].Columns.Count) + "4").NumberFormat = "@";
osheet.get_Range(ttb.getIndex(4) + "4", ttb.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(ttb.getIndex(4) + "5", ttb.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);
}
}
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);
}
}
public void SetNonBorders(string fromAddress, string toAddress)
{
_excelRange = _excelSheet.get_Range(fromAddress, toAddress);
_excelRange.Borders.LineStyle = Excel.Constants.xlNone;
//_excelRange.Borders.Weight = lineWeight;
}
public void WriteCell(string cellAddress, object value)
{
_excelRange = _excelSheet.get_Range(cellAddress, _value);
//_excelRange = _excelRange.get_Resize(10, 4);
_excelRange.set_Value(_value, value);
_excelRange.Cells.Font.Name = "Calibri";
_excelRange.Cells.Font.Size = 10;
}