private void AutoSizeColumns(XLSheet sheet)
{
for (int c = 0; c < sheet.Columns.Count; c++)
{
int colWidth = -1;
for (int r = 0; r < sheet.Rows.Count; r++)
{
object value = sheet[r, c].Value;
if (value != null)
{
// get value (unformatted at this point)
string text = value.ToString();
// format value if cell has a style with format set
var s = sheet[r, c].Style;
if (s != null && s.Format.Length > 0 && value is IFormattable)
{
string fmt = XLStyle.FormatXLToDotNet(s.Format);
text = ((IFormattable)value).ToString(fmt, CultureInfo.CurrentCulture);
}
// get font (default or style)
var font = this._book.DefaultFont;
if (s != null && s.Font != null)
{
font = s.Font;
}
// measure string (add a little tolerance)
_tblMeasure.FontFamily = new FontFamily(font.FontName);
_tblMeasure.FontSize = 3 * font.FontSize / 2;
_tblMeasure.FontWeight = font.Bold ? FontWeights.Bold : FontWeights.Normal;
_tblMeasure.FontStyle = font.Italic ? FontStyles.Italic : FontStyles.Normal;
_tblMeasure.Text = text;
// keep widest so far
int w = (int)(_tblMeasure.ActualWidth);
if (w > colWidth)
colWidth = w;
}
}
// done measuring, set column width
if (colWidth > -1)
sheet.Columns[c].Width = C1XLBook.PixelsToTwips(colWidth);
}
}
/// <summary>
/// Changes to using the passed in sheet. Note that changing to a new sheet automatically resets the
/// internal row and column counter used by WriteRecords.
/// </summary>
/// <param name="sheet">Sheet to change to</param>
public void ChangeSheet(
int sheet)
{
// Peform any column resizing for the current sheet before we change it
PerformColumnResize();
// Insert all the sheets up to the index we need if the count is less
var sheets = _book.Sheets;
if (sheet >= sheets.Count)
{
for (var i = sheets.Count; i <= sheet; i++)
{
sheets.Insert(i);
}
}
_sheet = sheets[sheet];
_row = _col = 0;
}
private void _btCreate_Click(object sender, RoutedEventArgs e)
{
// create new workbook
if (_book == null)
{
_book = new C1XLBook();
}
// clear the book
_book.Clear();
_book.Sheets.Clear();
// load from resources
Assembly a = Assembly.GetExecutingAssembly();
foreach (string res in a.GetManifestResourceNames())
{
if (!res.ToLower().EndsWith(".xlsx"))
{
continue;
}
using (var stream = a.GetManifestResourceStream(res))
{
// load Excel file
var book = new C1XLBook();
book.Load(stream, FileFormat.OpenXml);
// clone and rename first sheet (sheet names must be unique)
var ss = res.Split('.');
Debug.Assert(ss.Length >= 3);
XLSheet clone = book.Sheets[0].Clone();
clone.Name = ss[ss.Length - 2];
// add cloned sheet to main book
_book.Sheets.Add(clone);
}
}
// allow save the file
_lblStatus.Text = "You can save workbook";
_btnSave.IsEnabled = true;
}
// read print settings from form, apply to sheet
private void ApplyPrintSettings()
{
XLSheet sheet = c1XLBook1.Sheets[0];
XLPrintSettings ps = sheet.PrintSettings;
// paper size, orientation
if (_cmbPaperSize.SelectedItem != null)
{
ps.PaperKind = (PaperKind)_cmbPaperSize.SelectedItem;
}
ps.Landscape = _rdLandscape.Checked;
// scaling
// ** note:
// setting the FitPagesAcross, FitPagesDown, and ScalingFactor properties
// changes the value of AutoScale, so set AutoScale last.
ps.FitPagesAcross = decimal.ToInt32(_udAcross.Value);
ps.FitPagesDown = decimal.ToInt32(_udDown.Value);
ps.ScalingFactor = decimal.ToInt32(_udPercentSize.Value);
ps.AutoScale = _rdFit.Checked;
// start page
try
{
ps.StartPage = int.Parse(_txtStartPage.Text);
}
catch
{
ps.StartPage = 1;
}
// margins
ps.MarginLeft = decimal.ToDouble(_udLeft.Value);
ps.MarginTop = decimal.ToDouble(_udTop.Value);
ps.MarginRight = decimal.ToDouble(_udRight.Value);
ps.MarginBottom = decimal.ToDouble(_udBottom.Value);
ps.MarginHeader = decimal.ToDouble(_udHeader.Value);
ps.MarginFooter = decimal.ToDouble(_udFooter.Value);
// header/footer
ps.Header = _txtHeader.Text;
ps.Footer = _txtFooter.Text;
}
private void _btCreate_Click(object sender, RoutedEventArgs e)
{
// create new workbook
if (_book == null)
{
_book = new C1XLBook();
}
// clear the book
_book.Clear();
// add some styles
XLStyle s1 = new XLStyle(_book);
XLStyle s2 = new XLStyle(_book);
XLStyle s3 = new XLStyle(_book);
s1.Format = "#,##0.00000";
s2.Format = "#,##0.00000";
s2.Font = new XLFont("Courier New", 14);
s3.Format = "dd-MMM-yy";
// populate sheet with some random values
XLSheet sheet = _book.Sheets[0];
Random r = new Random();
for (int i = 0; i < 100; i++)
{
sheet[i, 0].Value = r.NextDouble() * 100000;
sheet[i, 0].Style = (i % 13 == 0) ? s2 : s1;
}
for (int i = 0; i < 100; i++)
{
sheet[i, 1].Value = new DateTime(2005, r.Next(1, 12), r.Next(1, 28));
sheet[i, 1].Style = s3;
}
// automatic sizing
AutoSizeColumns(sheet);
// allow save the file
_lblStatus.Text = "You can save workbook";
_btnSave.IsEnabled = true;
}
private void BtnExcel_Click(object sender, EventArgs e)
{
//throw new NotImplementedException();
if (grfRptName.Row == 1 && grfRpt.Rows.Count > 0)
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.DefaultExt = "xls";
dlg.Filter = "Excel |*.xls";
dlg.InitialDirectory = ic.iniC.pathSaveExcelAppointment;
dlg.FileName = "*.xls";
if (dlg.ShowDialog() != DialogResult.OK)
{
return;
}
// clear book
C1XLBook _book = new C1XLBook();
//_book.Clear();
//_book.Sheets.Clear();
// copy grids to book sheets
//foreach (TabPage pg in _tab.TabPages)
//{
// C1FlexGrid grid = pg.Controls[0] as C1FlexGrid;
XLSheet sheet = _book.Sheets.Add("pharmacy");
ic.SaveSheet(grfRpt, sheet, _book, false);
//}
// save selected sheet index
if (_book.Sheets.Count >= 1)
{
_book.Sheets.SelectedIndex = 1;
}
// save the book
_book.Save(dlg.FileName);
Application.DoEvents();
Process.Start("explorer.exe", dlg.FileName);
}
}
public void ExportBudgetForPrimavera_GetDrawingLogMainByProjList(string saveLoc, string xml, int sortCode, int drwgSpec)
{
SqlDataReader dr;
C1XLBook book = new C1XLBook();
XLSheet sheet = book.Sheets[0];
int indx;
decimal tmpRate;
// must be output with the following columns
// code,blank,description,quantity,uom,hours,rate,cost
// dr = CBBudgetLine.GetExportListByBudget(budgetID);
dr = CBDrawingLog.GetExportListForDrawingLog_Proj(xml, sortCode, drwgSpec);
indx = 0;
tmpRate = 0;
while (dr.Read())
{
sheet[indx, 3].Value = dr["DrawingID"].ToString(); // code
sheet[indx, 4].Value = ""; // blank
sheet[indx, 5].Value = dr["Department"]; // description
sheet[indx, 6].Value = dr["Project"].ToString(); // quantity
sheet[indx, 7].Value = dr["ProjectNumber"]; // uom
sheet[indx, 8].Value = dr["Title1"].ToString(); // hours
sheet[indx, 9].Value = dr["RevisionNumber"].ToString();
sheet[indx, 10].Value = dr["IssueDate"].ToString(); // rate
sheet[indx, 11].Value = dr["IssuedFor"].ToString(); // cost
sheet[indx, 12].Value = dr["TransNo"].ToString();
indx++;
}
dr.Close();
book.Save(saveLoc);
}
public static void ListsToExcelUsingC1 <T>(this IList <T> list, ref XLSheet ExcelWorkSheet, string SheetName) //vipin
{
try
{
// int row = 1; // Initialize Excel Row Start Position = 1
int row = 0;
PropertyInfo[] props = typeof(T).GetProperties();
List <PropertyInfo> propList = new List <PropertyInfo>();
propList.AddRange(props.ToList());
//Writing Columns Name in Excel Sheet
for (int col = 1; col <= propList.Count; col++)
{
// ExcelWorkSheet[row, col].Value = propList[col - 1].Name;
// ExcelWorkSheet[row, col-1].Value = propList[col - 1].Name; //vipin
ExcelWorkSheet[row, col - 1].Value = ArtilceDetailsColumnColumnChange(propList[col - 1].Name.ToString().Trim(), SheetName);
}
row = row + 1;
//Writing Rows into Excel Sheet
foreach (var item in list)
{
// int colIdx = 1;
int colIdx = 0; //vipin
// Excel row and column start positions for writing Row=1 and Col=1
foreach (var prop in propList)
{
ExcelWorkSheet[row, colIdx].Value = prop.GetValue(item, null);// list[listRow][col - 1].ToString();
colIdx = colIdx + 1;
}
row = row + 1;
}
}
catch (Exception ex)
{
throw ex;
}
} //ADDED BY VIPIN ON 30-03-2017
private void AutoSizeColumns(XLSheet sheet)
{
using (Graphics graphics = Graphics.FromHwnd(IntPtr.Zero))
{
for (int index1 = 0; index1 < sheet.Columns.Count; ++index1)
{
int num = -1;
for (int index2 = 0; index2 < sheet.Rows.Count; ++index2)
{
object obj = sheet[index2, index1].Value;
if (obj != null)
{
string str = obj.ToString();
XLStyle style = sheet[index2, index1].Style;
if (style != null && style.Format.Length > 0 && obj is IFormattable)
{
string dotNet = XLStyle.FormatXLToDotNet(style.Format);
str = ((IFormattable)obj).ToString(dotNet, (IFormatProvider)CultureInfo.CurrentCulture);
}
Font font = this.oBook.DefaultFont;
if (style != null && style.Font != null)
{
font = style.Font;
}
Size size = Size.Ceiling(graphics.MeasureString(str + "XX", font));
if (size.Width > num)
{
num = size.Width;
}
}
}
if (num > -1)
{
sheet.Columns[index1].Width = C1XLBook.PixelsToTwips((double)num);
}
}
}
}
private void button1_Click(object sender, System.EventArgs e)
{
string path = Application.StartupPath;
int pos = path.IndexOf(@"\bin");
path = path.Substring(0, pos);
string tempdir = Application.ExecutablePath.Substring(0,
Application.ExecutablePath.LastIndexOf("\\") + 1);
string xlsFileName = tempdir + @"combineSheets.xls";
if (File.Exists(xlsFileName))
{
File.Delete(xlsFileName);
}
// clear the book
c1XLBook1.Clear();
c1XLBook1.Sheets.Clear();
foreach (string fileName in Directory.GetFiles(path, "*.xls"))
{
// load Excel file
C1XLBook book = new C1XLBook();
book.Load(fileName);
// clone and rename first sheet (sheet names must be unique)
XLSheet clone = book.Sheets[0].Clone();
clone.Name = Path.GetFileNameWithoutExtension(fileName);
// add cloned sheet to main book
c1XLBook1.Sheets.Add(clone);
}
c1XLBook1.Save(xlsFileName);
System.Diagnostics.Process.Start(xlsFileName);
}
// handle toolbar
private void toolBar1_ButtonClick(object sender, System.Windows.Forms.ToolBarButtonClickEventArgs e)
{
// apply border to current selection
if (e.Button == tbBorderOn)
{
// assign border style to selection
CellRange rg = _flex.Selection;
rg.Style = _flex.Styles["Border"];
}
// remove border from current selection
if (e.Button == tbBorderOff)
{
// remove border style from selection
CellRange rg = _flex.Selection;
rg.Style = null;
}
// change border color
if (e.Button == tbBorderColor)
{
// show color picker dialog
ColorDialog dlg = new ColorDialog();
dlg.Color = _bdrBrush.Color;
// if the user clicked OK, set new border color
if (dlg.ShowDialog() == DialogResult.OK)
{
_bdrBrush.Color = dlg.Color;
}
}
// thicker/thinner outer border
if (e.Button == tbIncreaseOut)
{
if (_bdrOutside < 10)
{
_bdrOutside++;
}
}
if (e.Button == tbDecreaseOut)
{
if (_bdrOutside > 0)
{
_bdrOutside--;
}
}
// thicker/thinner inner border
if (e.Button == tbIncreaseIn)
{
if (_bdrInside < 10)
{
_bdrInside++;
}
}
if (e.Button == tbDecreaseIn)
{
if (_bdrInside > 0)
{
_bdrInside--;
}
}
// save current sheet (with borders) into an Excel file
if (e.Button == tbExcel)
{
_c1xl.Clear();
_c1xl.DefaultFont = _flex.Font;
XLSheet sheet = _c1xl.Sheets[0];
for (int c = 0; c < _flex.Cols.Count; c++)
{
// save column width (twips)
sheet.Columns[c].Width = (int)(_flex.Cols[c].WidthDisplay / 96f * 1440);
// save cells on this column
for (int r = 0; r < _flex.Rows.Count; r++)
{
// save cell value
sheet[r, c].Value = _flex[r, c];
// we only want cells with style set to "Border"
CellStyle s = _flex.GetCellStyle(r, c);
if (s == null || s.Name != "Border")
{
continue;
}
// get custom border widths for this cell
// (depends on neighbor cells)
Margins m = GetBorderMargins(r, c);
// create stytle for this cell
XLStyle xs = new XLStyle(_c1xl);
if (m.Top > 0)
{
xs.BorderTop = GetLineStyle(m.Top);
xs.BorderColorTop = _bdrBrush.Color;
}
if (m.Left > 0)
{
xs.BorderLeft = GetLineStyle(m.Left);
xs.BorderColorLeft = _bdrBrush.Color;
}
if (m.Right > 0)
{
xs.BorderRight = GetLineStyle(m.Right);
xs.BorderColorRight = _bdrBrush.Color;
}
if (m.Bottom > 0)
{
xs.BorderBottom = GetLineStyle(m.Bottom);
xs.BorderColorBottom = _bdrBrush.Color;
}
sheet[r, c].Style = xs;
}
}
// save book
string fileName = Application.StartupPath + @"\borders.xls";
_c1xl.Save(fileName);
System.Diagnostics.Process.Start(fileName);
}
// repaint control to show changes
_flex.Invalidate();
}
public void RPTta(C1XLBook book, string RPT_ID, string title, string wh)
{
//string ID="RPT1";
book = new C1XLBook();
XLSheet sheet = book.Sheets[0];
FormatExcel.Init_Excel(sheet);
sheet.Name = "Sheet1";
XLStyle Title = FormatExcel.Get_Style(book, 16, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, false, "");
XLStyle Caption = FormatExcel.Get_Style(book, 10, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, true, "");
XLStyle StringN = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, false, "");
XLStyle String = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "");
XLStyle INT = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,###");
XLStyle GIO = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,###");
XLStyle DOU = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,###.##");
string sql = "";
sql = "Select * from FILE07B where RPT_ID=N'" + RPT_ID + "' and SHO_BT=1 ORDER BY SEQ_NO";
Func.RecordSet rscon = new Func.RecordSet(sql, PublicFunction.C_con);
sql = GET_SQL(rscon, wh);
Func.RecordSet rs = new RecordSet(sql, PublicFunction.C_con);
try
{
if (rs.rows <= 0)
{
return;
}
}
catch { return; }
int r = 0, cols = 0;
ArrayList a = new ArrayList();
a.Add(0);
sheet.Columns[0].Width = 680;
for (int i = 0; i < rs.cols; i++)
{
cols++;
a.Add(i);
}
// tieu de
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0],
T_String.GetDataFromSQL("COM_N1", "FILA01A"), Title);
r++;
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0], title, Title);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 2], PublicFunction.L_Get_RPT("RptTa", 1) + ":" + PublicFunction.A_UserID, StringN);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 2], PublicFunction.L_Get_RPT("RptTa", 2) + ":" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm"), StringN);
r++;
FormatExcel.Set_Cell(sheet[r, 0], "STT", Caption);
for (int i = 0; i < rscon.rows; i++)
{
FormatExcel.Set_Cell(sheet[r, i + 1], rscon.record(i, "COL_NM"), Caption);
sheet.Columns[i + 1].Width = T_String.IsNullTo0(rscon.record(i, "WID_VL")) * 17;
}
r++;
//
for (int i = 0; i < rs.rows; i++)
{
FormatExcel.Set_Cell(sheet[r, 0], i + 1 + "", INT);
for (int c = 0; c < rscon.rows; c++)
{
int c1 = c + 1;
switch (rscon.record(c, "TYP_ID") + "")
{
case "1": // DateTime
{
try
{
FormatExcel.Set_Cell(sheet[r, c1], DateTime.Parse(rs.record(i, c) + "").ToString("yyyy/MM/dd"), String);
}
catch { FormatExcel.Set_Cell(sheet[r, c1], "", String); }
break;
}
case "2": // boolean
{
if (rs.record(i, c) + "" != "True")
{
FormatExcel.Set_Cell(sheet[r, c1], "False", String);
}
else
{
FormatExcel.Set_Cell(sheet[r, c1], "True", String);
}
break;
}
case "":
{
FormatExcel.Set_Cell(sheet[r, c1], rs.record(i, c), String);
break;
}
case "3": // 00:00
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(rs.record(i, c)).ToString("##:##"), String);
// if(rscon.record(c,"SUM_BT")+""=="True")
// {
// a[c1]= T_String.CongTG(T_String.IsNullTo00(a[c1]+""),T_String.IsNullTo00(rs.record(i,c)));
// }
break;
}
case "4": // double
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(rs.record(i, c)).ToString("#,###.##"), DOU);
if (rscon.record(c, "SUM_BT") + "" == "True")
{
a[c1] = T_String.IsNullTo00(a[c1] + "") + T_String.IsNullTo00(rs.record(i, c));
}
break;
}
case "5": // int
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(rs.record(i, c)).ToString("#,###.##"), INT);
if (rscon.record(c, "SUM_BT") + "" == "True")
{
a[c1] = T_String.IsNullTo00(a[c1] + "") + T_String.IsNullTo00(rs.record(i, c));
}
break;
}
}
}
r++;
if (i + 1 == rs.rows) //dong cuoi
{
for (int c = 0; c < rscon.rows; c++)
{
int c1 = c + 1;
if (rscon.record(c, "SUM_BT") + "" == "True")
{
switch (rscon.record(c, "TYP_ID") + "")
{
// case "3": // 00:00
// {
// FormatExcel.Set_Cell(sheet[r,c1], T_String.IsNullTo00(a[c1]+"").ToString("##:##"),String);
// break;
// }
case "4": // double
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(a[c1] + "").ToString("#,###.##"), DOU);
break;
}
case "5": // int
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(a[c1] + "").ToString("#,###.##"), INT);
break;
}
}
}
}
}
}
// }
try
{
string fileName = Application.StartupPath + @"\\Reports\\TAPO_" + RPT_ID + ".xls";
book.Save(fileName);
System.Diagnostics.Process.Start(fileName);
}
catch
{
MessageBox.Show("You must close TAPO_" + RPT_ID + ".xls file first!!!!!");
return;
}
}
/// <summary>
/// hàm thực iheenj viecj
/// export to excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdExportToExcel_Click(object sender, EventArgs e)
{
try
{
try
{
_dtData =
SPs.ThuocSotonghopthuocHangngay(dtFromDate.Value, dtToDate.Value, Utility.Int16Dbnull(cboStock.SelectedValue, -1),
Utility.Int16Dbnull(cboDoiTuong.SelectedValue, -1), Utility.Int16Dbnull(cbotinhtrang.SelectedValue, -1), "").GetDataSet().
Tables[0];
if (_dtData.Rows.Count > 0)
{
Utility.SetDataSourceForDataGridEx(grdList, _dtData, true, true, "1=1", "");
const string reportcode = "thuoc_sotonghop_hangngay";
string duongdan = Utility.GetPathExcel(reportcode);
var book = new C1XLBook();
book.Load(duongdan);
book.DefaultFont = new Font("Time New Roman", 11, FontStyle.Regular);
XLSheet sheet = book.Sheets[0];
DataTable dt = _dtData;
int idxRow = 6;
int idxColSh = 0;
string condition =
string.Format("Từ ngày {0} đến {1} - Đối tượng : {2} - Tình trạng :{3}",
dtFromDate.Text, dtToDate.Text,
cboDoiTuong.SelectedIndex >= 0
? Utility.sDbnull(cboDoiTuong.Text)
: "Tất cả",
cbotinhtrang.SelectedIndex > 0
? Utility.sDbnull(cbotinhtrang.Text)
: "Tất cả");
const int idxRowT = 5;
const int idxColShT = 0;
//sheet[5, 0].SetValue("STT", HamDungChung.styleStringCenter(book));
//sheet[5, 1].SetValue("HỌ TÊN NGƯỜI BỆNH", HamDungChung.styleStringCenter(book));
for (int i = 0; i < dt.Columns.Count; i++)
{
if (Utility.sDbnull(dt.Columns[i].ColumnName) == "ten_benhnhan")
{
sheet[idxRowT, idxColShT + i].SetValue(Utility.sDbnull("HỌ TÊN NGƯỜI BỆNH"), HamDungChung.styleStringCenter_Bold(book));
}
sheet[idxRowT, idxColShT + i].SetValue(Utility.sDbnull(dt.Columns[i].ColumnName), HamDungChung.styleStringCenter_Bold(book));
if (i >= 2)
{
sheet[idxRowT, idxColShT + i].SetValue(Utility.sDbnull(dt.Columns[i].ColumnName), HamDungChung.styleStringCenter_Rotate(book));
}
}
//sheet[3, idxColSh].SetValue(Convert.ToString(condition), HamDungChung.styleStringCenter(book));
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(i + 1), HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToString(dt.Rows[i]["ten_benhnhan"]), HamDungChung.styleStringLeft(book));
for (int j = 2; j < dt.Columns.Count; j++)
{
sheet[idxRow, idxColSh + j].SetValue(Convert.ToString(dt.Rows[i][dt.Columns[j].ColumnName]), HamDungChung.styleNumber(book));
}
idxRow = idxRow + 1;
}
// vị trí dòng dữ liệu của table tiếp theo, vị trí cột bắt đầu t? 0
string getTime = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss"));
string pathDirectory = AppDomain.CurrentDomain.BaseDirectory + "TemplateExcel\\ExportExcel\\";
if (!Directory.Exists(pathDirectory))
{
Directory.CreateDirectory(pathDirectory);
}
book.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" + reportcode +
getTime + ".xls");
Process.Start(
new ProcessStartInfo(AppDomain.CurrentDomain.BaseDirectory +
"\\TemplateExcel\\ExportExcel\\" +
reportcode + getTime + ".xls"));
}
else
{
Utility.ShowMsg("Không có dữ liệu để báo cáo!");
}
}
catch (Exception ex)
{
Utility.ShowMsg("Lỗi: " + ex.Message);
}
//Janus.Windows.GridEX.GridEXRow[] gridExRows = grdList.GetCheckedRows();
//if (grdList.RowCount <= 0)
//{
// Utility.ShowMsg("Không có dữ liệu", "Thông báo");
// grdList.Focus();
// return;
//}
//saveFileDialog1.Filter = "Excel File(*.xls)|*.xls";
//saveFileDialog1.FileName = string.Format("{0}.xls", baocaO_TIEUDE1.TIEUDE);
////saveFileDialog1.ShowDialog();
//if (saveFileDialog1.ShowDialog() == DialogResult.OK)
//{
// string sPath = saveFileDialog1.FileName;
// FileStream fs = new FileStream(sPath, FileMode.Create);
// fs.CanWrite.CompareTo(true);
// fs.CanRead.CompareTo(true);
// gridEXExporter1.Export(fs);
// fs.Dispose();
//}
//saveFileDialog1.Dispose();
//saveFileDialog1.Reset();
}
catch (Exception exception)
{
}
}
/// <summary>
/// Loads the content of an XLSheet into a C1FlexGrid.
/// </summary>
public static void Load(XLSheet sheet, C1FlexGrid flex)
{
// clear style cache if this is a new book
if (sheet.Book != _lastBook)
{
_cellStyles.Clear();
_excelStyles.Clear();
_lastBook = sheet.Book;
}
// set default parameters
flex.FontFamily = new FontFamily(sheet.Book.DefaultFont.FontName);
flex.FontSize = PointsToPixels(sheet.Book.DefaultFont.FontSize);
flex.Rows.DefaultSize = TwipsToPixels(sheet.DefaultRowHeight);
flex.Columns.DefaultSize = TwipsToPixels(sheet.DefaultColumnWidth);
flex.IsReadOnly = sheet.Locked;
flex.GridLinesVisibility = sheet.ShowGridLines
? GridLinesVisibility.All
: GridLinesVisibility.None;
//flex.GridLinesBrush = sheet.GridColor;
flex.HeadersVisibility = sheet.ShowHeaders
? HeadersVisibility.All
: HeadersVisibility.None;
flex.GroupRowPosition = sheet.OutlinesBelow
? GroupRowPosition.BelowData
: GroupRowPosition.AboveData;
// add columns
flex.Columns.Clear();
foreach (XLColumn c in sheet.Columns)
{
// create column, give it a unique name so undo/ColumnLayout work
var col = new Column();
col.ColumnName = col.GetHashCode().ToString("x0");
// set size and visibility
if (c.Width > -1)
{
col.Width = new GridLength(TwipsToPixels(c.Width));
}
col.Visible = c.Visible;
// set style
if (c.Style != null)
{
col.CellStyle = GetCellStyle(c.Style);
}
// and add to the grid
flex.Columns.Add(col);
}
// add rows
flex.Rows.Clear();
foreach (XLRow r in sheet.Rows)
{
var row = new ExcelRow();
if (r.Height > -1)
{
row.Height = TwipsToPixels(r.Height);
}
if (r.Style != null)
{
row.CellStyle = GetCellStyle(r.Style);
}
row.Level = r.OutlineLevel;
row.Visible = r.Visible;
flex.Rows.Add(row);
}
// add cells
for (int r = 0; r < flex.Rows.Count; r++)
{
for (int c = 0; c < flex.Columns.Count; c++)
{
var cell = sheet[r, c];
if (cell != null)
{
if (!string.IsNullOrEmpty(cell.Formula))
{
// save formula
var formula = cell.Formula.Trim();
if (!formula.StartsWith("="))
{
formula = string.Format("={0}", formula);
}
flex[r, c] = formula;
}
else if (cell.Value != null)
{
// save value
flex[r, c] = cell.Value;
}
if (cell.Style != null)
{
// save style
var row = flex.Rows[r] as ExcelRow;
var col = flex.Columns[c];
row.SetCellStyle(col, GetCellStyle(cell.Style));
}
}
}
}
// at least 20 columns, 50 rows
while (flex.Columns.Count < 20)
{
flex.Columns.Add(new Column());
}
while (flex.Rows.Count < 50)
{
flex.Rows.Add(new ExcelRow());
}
// load merged cells
var xmm = flex.MergeManager as ExcelMergeManager;
if (xmm == null)
{
xmm = new ExcelMergeManager();
}
xmm.GetMergedRanges(sheet);
// freeze rows/columns
flex.Rows.Frozen = sheet.Rows.Frozen;
flex.Columns.Frozen = sheet.Columns.Frozen;
// update selection
if (sheet.SelectedCells != null && sheet.SelectedCells.Count > 0)
{
// review: using the last one seems to work, but why?
var sel = sheet.SelectedCells[sheet.SelectedCells.Count - 1];
flex.Select(sel.RowFrom, sel.ColumnFrom, sel.RowTo, sel.ColumnTo, false);
}
else
{
flex.Select(0, 0);
}
}
private void cmdExcel_Click(object sender, EventArgs e)
{
try
{
string reportcode = "BHYT_21A_EXCEL";
string duongdan = Utility.GetPathExcel(reportcode);
var book = new C1XLBook();
book.Load(duongdan);
book.DefaultFont = new Font("Time New Roman", 11, FontStyle.Regular);
XLSheet sheet = book.Sheets[0];
DataTable dt = m_dataTH;
int idxRow = 6;
int idxColSh = 0;
string codintion = string.Format("Từ ngày {0} đến ngày {1}. Tuyến {2}",
dtpFromDate.Value.ToString("dd/MM/yyyy"), dtpToDate.Value.ToString("dd/MM/yyyy"), cboTuyen.Text);
sheet[3, idxColSh].SetValue(Convert.ToString(codintion), HamDungChung.styleStringCenter(book));
int sttloaidichvu = 1;
if (chktuyen.Checked)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i == 0)
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(dt.Rows[i]["DoiTuong"]), HamDungChung.styleStringLeft_Bold(book));
idxRow = idxRow + 1;
sheet[idxRow, idxColSh].SetValue(string.Format("{0}.{1}", sttloaidichvu, Convert.ToString(dt.Rows[i]["Ten_nhombaocao_dichvu"])), HamDungChung.styleStringLeft_Bold(book));
sttloaidichvu = sttloaidichvu + 1;
idxRow = idxRow + 1;
}
else
{
if (dt.Rows[i]["DoiTuong"].ToString() != dt.Rows[i - 1]["DoiTuong"].ToString())
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(dt.Rows[i]["DoiTuong"]), HamDungChung.styleStringLeft_Bold(book));
idxRow = idxRow + 1;
sttloaidichvu = 1;
sheet[idxRow, idxColSh].SetValue(string.Format("{0}.{1}", sttloaidichvu, Convert.ToString(dt.Rows[i]["Ten_nhombaocao_dichvu"])), HamDungChung.styleStringLeft_Bold(book));
sttloaidichvu = sttloaidichvu + 1;
idxRow = idxRow + 1;
}
if (dt.Rows[i]["DoiTuong"].ToString() == dt.Rows[i - 1]["DoiTuong"].ToString() && dt.Rows[i]["Ten_nhombaocao_dichvu"].ToString() != dt.Rows[i - 1]["Ten_nhombaocao_dichvu"].ToString())
{
sheet[idxRow, idxColSh].SetValue(string.Format("{0}.{1}", sttloaidichvu, Convert.ToString(dt.Rows[i]["Ten_nhombaocao_dichvu"])), HamDungChung.styleStringLeft_Bold(book));
sttloaidichvu = sttloaidichvu + 1;
idxRow = idxRow + 1;
}
}
sheet[idxRow, idxColSh].SetValue(Convert.ToString(dt.Rows[i]["ma_QD"]),
HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToString(dt.Rows[i]["ma_chitiet_bhyt"]),
HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 2].SetValue(Convert.ToString(dt.Rows[i]["ten_chitietdichvu"]),
HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToDecimal(dt.Rows[i]["SO_LUONG"]),
HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToDecimal(dt.Rows[i]["soluong_nt"]),
HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 5].SetValue(Convert.ToDecimal(dt.Rows[i]["DON_GIA"]),
HamDungChung.styleDecimalBoldAllBorder_Money(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToDecimal(dt.Rows[i]["thanh_tien"]),
HamDungChung.styleDecimalBoldAllBorder_Money(book));
idxRow = idxRow + 1;
}
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i == 0)
{
sheet[idxRow, idxColSh].SetValue(string.Format("{0}.{1}", sttloaidichvu, Convert.ToString(dt.Rows[i]["Ten_nhombaocao_dichvu"])), HamDungChung.styleStringLeft_Bold(book));
sttloaidichvu = sttloaidichvu + 1;
idxRow = idxRow + 1;
}
else
{
if (dt.Rows[i]["Ten_nhombaocao_dichvu"].ToString() != dt.Rows[i - 1]["Ten_nhombaocao_dichvu"].ToString())
{
sheet[idxRow, idxColSh].SetValue(string.Format("{0}.{1}", sttloaidichvu, Convert.ToString(dt.Rows[i]["Ten_nhombaocao_dichvu"])), HamDungChung.styleStringLeft_Bold(book));
sttloaidichvu = sttloaidichvu + 1;
idxRow = idxRow + 1;
}
}
sheet[idxRow, idxColSh].SetValue(Convert.ToString(dt.Rows[i]["ma_QD"]),
HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToString(dt.Rows[i]["ma_chitiet_bhyt"]),
HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 2].SetValue(Convert.ToString(dt.Rows[i]["ten_chitietdichvu"]),
HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToDecimal(dt.Rows[i]["SO_LUONG"]),
HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToDecimal(dt.Rows[i]["soluong_nt"]),
HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 5].SetValue(Convert.ToDecimal(dt.Rows[i]["DON_GIA"]),
HamDungChung.styleDecimalBoldAllBorder_Money(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToDecimal(dt.Rows[i]["thanh_tien"]),
HamDungChung.styleDecimalBoldAllBorder_Money(book));
idxRow = idxRow + 1;
}
}
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToDecimal(dt.Compute("Sum(SO_LUONG)", "1=1")), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToDecimal(dt.Compute("Sum(soluong_nt)", "1=1")), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToDecimal(dt.Compute("Sum(thanh_tien)", "1=1")), HamDungChung.styleDecimalBoldAllBorder_Money(book));
string getdate = string.Format("Ngày {0} tháng {1} năm {2}", dtpNgayIn.Value.Day,
dtpNgayIn.Value.Month, dtpNgayIn.Value.Year);
sheet[idxRow + 2, 5].SetValue(getdate, HamDungChung.styleStringCenter_UnBorder(book));
sheet[idxRow + 3, 1].SetValue("NGƯỜI LẬP BẢNG", HamDungChung.styleDecimalBoldAllBorder_Money(book));
sheet[idxRow + 3, 4].SetValue("PHÒNG TÀI CHÍNH KẾ TOÁN",
HamDungChung.styleDecimalBoldAllBorder_Money(book));
sheet[idxRow + 3, 5].SetValue("GIÁM ĐỐC BỆNH VIỆN", HamDungChung.styleDecimalBoldAllBorder_Money(book));
// vị trí dòng dữ liệu của table tiếp theo, vị trí cột bắt đầu t? 0
string getTime = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss"));
string pathDirectory = AppDomain.CurrentDomain.BaseDirectory + "TemplateExcel\\ExportExcel\\";
if (!Directory.Exists(pathDirectory))
{
Directory.CreateDirectory(pathDirectory);
}
book.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" + reportcode +
getTime + ".xls");
Process.Start(
new ProcessStartInfo(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" +
reportcode + getTime + ".xls"));
}
catch (Exception ex)
{
Utility.ShowMsg("Lỗi: " + ex.Message);
}
}
private void cmdExportToExcel_Click(object sender, EventArgs e)
{
try
{
int trangthai = -1;
if (radTatca.Checked)
{
trangthai = -1;
}
if (radDathuchien.Checked)
{
trangthai = 1;
}
if (radChuathuchien.Checked)
{
trangthai = 0;
}
DataTable dtDanhsach =
SPs.BaocaoThongkedanhsachThuchienchucnang(dtFromDate.Value, dtToDate.Value,
Utility.Int16Dbnull(cboDoituongKCB.SelectedValue, -1),
Utility.sDbnull(cboKhoa.SelectedValue, "KKB"), Args,
Utility.Int32Dbnull(txtdichvu.MyID, -1), trangthai).GetDataSet().Tables[0];
if (dtDanhsach.Rows.Count > 0)
{
Utility.SetDataSourceForDataGridEx(grdResult, dtDanhsach, false, true, "1=1", "");
string reportcode = "";
string duongdan = "";
string codintion = "";
DataTable dt = new DataTable();
int idxRow = 0;
int idxColSh = 0;
var book = new C1XLBook();
XLSheet sheet = book.Sheets[0];
string getTime = "";
string pathDirectory = "";
switch (Args.Substring(0, 2))
{
case "SA":
reportcode = "baocao_thongkedanhsach_sieuam";
duongdan = Utility.GetPathExcel(reportcode);
book = new C1XLBook();
book.Load(duongdan);
book.DefaultFont = new Font("Time New Roman", 11, FontStyle.Regular);
sheet = book.Sheets[0];
dt = dtDanhsach;
idxRow = 7;
idxColSh = 0;
codintion = string.Format("Từ ngày {0} đến ngày {1}. Đối tượng: {2}",
dtFromDate.Value.ToString("dd/MM/yyyy"), dtToDate.Value.ToString("dd/MM/yyyy"),
cboDoituongKCB.Text);
sheet[3, idxColSh].SetValue(Convert.ToString(codintion),
HamDungChung.styleStringCenter(book));
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(i + 1), HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToString(dt.Rows[i]["ten_benhnhan"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 2].SetValue(Convert.ToString(dt.Rows[i]["nam_sinh"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToString(dt.Rows[i]["gioi_tinh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToString(dt.Rows[i]["dia_chi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 5].SetValue(Convert.ToString(dt.Rows[i]["IsBHYT"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 7].SetValue(Convert.ToString(dt.Rows[i]["ten_khoaphong"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 8].SetValue(Convert.ToString(dt.Rows[i]["ten_chitietdichvu"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 9].SetValue(Convert.ToString(dt.Rows[i]["ket_qua"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 10].SetValue(Convert.ToString(dt.Rows[i]["nguoi_thuchien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 11].SetValue(Convert.ToString(dt.Rows[i]["ghi_chu"]), HamDungChung.styleStringLeft(book));
idxRow = idxRow + 1;
}
getTime = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss"));
pathDirectory = AppDomain.CurrentDomain.BaseDirectory +
"TemplateExcel\\ExportExcel\\";
if (!Directory.Exists(pathDirectory))
{
Directory.CreateDirectory(pathDirectory);
}
book.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" +
reportcode +
getTime + ".xls");
Process.Start(
new ProcessStartInfo(AppDomain.CurrentDomain.BaseDirectory +
"\\TemplateExcel\\ExportExcel\\" +
reportcode + getTime + ".xls"));
break;
case "XQ":
reportcode = "baocao_thongkedanhsach_xquang";
duongdan = Utility.GetPathExcel(reportcode);
book = new C1XLBook();
book.Load(duongdan);
book.DefaultFont = new Font("Time New Roman", 11, FontStyle.Regular);
sheet = book.Sheets[0];
dt = dtDanhsach;
idxRow = 7;
idxColSh = 0;
codintion = string.Format("Từ ngày {0} đến ngày {1}. Đối tượng: {2}",
dtFromDate.Value.ToString("dd/MM/yyyy"), dtToDate.Value.ToString("dd/MM/yyyy"),
cboDoituongKCB.Text);
sheet[3, idxColSh].SetValue(Convert.ToString(codintion),
HamDungChung.styleStringCenter(book));
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(i + 1), HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToString(dt.Rows[i]["ten_benhnhan"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 2].SetValue(Convert.ToString(dt.Rows[i]["nam_sinh"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToString(dt.Rows[i]["gioi_tinh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToString(dt.Rows[i]["dia_chi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 5].SetValue(Convert.ToString(dt.Rows[i]["IsBHYT"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 7].SetValue(Convert.ToString(dt.Rows[i]["ten_khoaphong"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 8].SetValue(Convert.ToString(dt.Rows[i]["ten_chitietdichvu"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 9].SetValue(Convert.ToString(dt.Rows[i]["ket_qua"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 10].SetValue(Convert.ToString(dt.Rows[i]["nguoi_thuchien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 11].SetValue(Convert.ToString(dt.Rows[i]["ghi_chu"]), HamDungChung.styleStringLeft(book));
idxRow = idxRow + 1;
}
getTime = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss"));
pathDirectory = AppDomain.CurrentDomain.BaseDirectory +
"TemplateExcel\\ExportExcel\\";
if (!Directory.Exists(pathDirectory))
{
Directory.CreateDirectory(pathDirectory);
}
book.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" +
reportcode +
getTime + ".xls");
Process.Start(
new ProcessStartInfo(AppDomain.CurrentDomain.BaseDirectory +
"\\TemplateExcel\\ExportExcel\\" +
reportcode + getTime + ".xls"));
break;
break;
case "DT":
break;
case "NS":
reportcode = "baocao_thongkedanhsach_noisoi";
duongdan = Utility.GetPathExcel(reportcode);
book.Load(duongdan);
book.DefaultFont = new Font("Time New Roman", 11, FontStyle.Regular);
dt = dtDanhsach;
idxRow = 7;
idxColSh = 0;
codintion = string.Format("Từ ngày {0} đến ngày {1}. Đối tượng: {2}",
dtFromDate.Value.ToString("dd/MM/yyyy"), dtToDate.Value.ToString("dd/MM/yyyy"),
cboDoituongKCB.Text);
sheet[3, idxColSh].SetValue(Convert.ToString(codintion),
HamDungChung.styleStringCenter(book));
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(i + 1), HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToString(dt.Rows[i]["ten_benhnhan"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 2].SetValue(Convert.ToString(dt.Rows[i]["nam_sinh"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToString(dt.Rows[i]["gioi_tinh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToString(dt.Rows[i]["dia_chi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 5].SetValue(Convert.ToString(dt.Rows[i]["IsBHYT"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 7].SetValue(Convert.ToString(dt.Rows[i]["ten_khoaphong"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 8].SetValue(Convert.ToString(dt.Rows[i]["ten_chitietdichvu"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 9].SetValue(Convert.ToString(dt.Rows[i]["ket_qua"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 10].SetValue(Convert.ToString(dt.Rows[i]["nguoi_thuchien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 11].SetValue(Convert.ToString(dt.Rows[i]["ghi_chu"]), HamDungChung.styleStringLeft(book));
idxRow = idxRow + 1;
}
getTime = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss"));
pathDirectory = AppDomain.CurrentDomain.BaseDirectory +
"TemplateExcel\\ExportExcel\\";
if (!Directory.Exists(pathDirectory))
{
Directory.CreateDirectory(pathDirectory);
}
book.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" +
reportcode +
getTime + ".xls");
Process.Start(
new ProcessStartInfo(AppDomain.CurrentDomain.BaseDirectory +
"\\TemplateExcel\\ExportExcel\\" +
reportcode + getTime + ".xls"));
break;
case "PT":
reportcode = "baocao_thongkedanhsach_pttt";
duongdan = Utility.GetPathExcel(reportcode);
book = new C1XLBook();
book.Load(duongdan);
book.DefaultFont = new Font("Time New Roman", 11, FontStyle.Regular);
sheet = book.Sheets[0];
dt = dtDanhsach;
idxRow = 7;
idxColSh = 0;
codintion = string.Format("Từ ngày {0} đến ngày {1}. Đối tượng: {2}",
dtFromDate.Value.ToString("dd/MM/yyyy"), dtToDate.Value.ToString("dd/MM/yyyy"),
cboDoituongKCB.Text);
sheet[3, idxColSh].SetValue(Convert.ToString(codintion),
HamDungChung.styleStringCenter(book));
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(i + 1), HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToString(dt.Rows[i]["ten_benhnhan"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 2].SetValue(Convert.ToString(dt.Rows[i]["nam_sinh"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToString(dt.Rows[i]["gioi_tinh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToString(dt.Rows[i]["dia_chi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 5].SetValue(Convert.ToString(dt.Rows[i]["IsBHYT"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 7].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 8].SetValue(Convert.ToString(dt.Rows[i]["ten_chitietdichvu"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 9].SetValue(Convert.ToString(dt.Rows[i]["phuongphap_vocam"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 10].SetValue(Convert.ToString(dt.Rows[i]["ngay_thuchien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 11].SetValue(Convert.ToString(dt.Rows[i]["loai_phauthuat"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 12].SetValue(Convert.ToString(dt.Rows[i]["nguoi_thuchien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 13].SetValue(Convert.ToString(dt.Rows[i]["bacsy_gayme"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 14].SetValue(Convert.ToString(dt.Rows[i]["ghi_chu"]), HamDungChung.styleStringLeft(book));
idxRow = idxRow + 1;
}
getTime = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss"));
pathDirectory = AppDomain.CurrentDomain.BaseDirectory +
"TemplateExcel\\ExportExcel\\";
if (!Directory.Exists(pathDirectory))
{
Directory.CreateDirectory(pathDirectory);
}
book.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" +
reportcode +
getTime + ".xls");
Process.Start(
new ProcessStartInfo(AppDomain.CurrentDomain.BaseDirectory +
"\\TemplateExcel\\ExportExcel\\" +
reportcode + getTime + ".xls"));
break;
case "TT":
reportcode = "baocao_thongkedanhsach_tt";
duongdan = Utility.GetPathExcel(reportcode);
book = new C1XLBook();
book.Load(duongdan);
book.DefaultFont = new Font("Time New Roman", 11, FontStyle.Regular);
sheet = book.Sheets[0];
dt = dtDanhsach;
idxRow = 7;
idxColSh = 0;
codintion = string.Format("Từ ngày {0} đến ngày {1}. Đối tượng: {2}",
dtFromDate.Value.ToString("dd/MM/yyyy"), dtToDate.Value.ToString("dd/MM/yyyy"),
cboDoituongKCB.Text);
sheet[3, idxColSh].SetValue(Convert.ToString(codintion),
HamDungChung.styleStringCenter(book));
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(i + 1), HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToString(dt.Rows[i]["ten_benhnhan"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 2].SetValue(Convert.ToString(dt.Rows[i]["nam_sinh"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToString(dt.Rows[i]["gioi_tinh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToString(dt.Rows[i]["dia_chi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 5].SetValue(Convert.ToString(dt.Rows[i]["IsBHYT"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 7].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 8].SetValue(Convert.ToString(dt.Rows[i]["ten_chitietdichvu"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 9].SetValue(Convert.ToString(dt.Rows[i]["phuongphap_vocam"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 10].SetValue(Convert.ToString(dt.Rows[i]["ngay_thuchien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 11].SetValue(Convert.ToString(dt.Rows[i]["loai_phauthuat"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 12].SetValue(Convert.ToString(dt.Rows[i]["nguoi_thuchien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 13].SetValue(Convert.ToString(dt.Rows[i]["bacsy_gayme"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 14].SetValue(Convert.ToString(dt.Rows[i]["ghi_chu"]), HamDungChung.styleStringLeft(book));
idxRow = idxRow + 1;
}
getTime = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss"));
pathDirectory = AppDomain.CurrentDomain.BaseDirectory +
"TemplateExcel\\ExportExcel\\";
if (!Directory.Exists(pathDirectory))
{
Directory.CreateDirectory(pathDirectory);
}
book.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" +
reportcode +
getTime + ".xls");
Process.Start(
new ProcessStartInfo(AppDomain.CurrentDomain.BaseDirectory +
"\\TemplateExcel\\ExportExcel\\" +
reportcode + getTime + ".xls"));
break;
default:
reportcode = "baocao_thongkedanhsach_tt";
duongdan = Utility.GetPathExcel(reportcode);
book = new C1XLBook();
book.Load(duongdan);
book.DefaultFont = new Font("Time New Roman", 11, FontStyle.Regular);
sheet = book.Sheets[0];
dt = dtDanhsach;
idxRow = 7;
idxColSh = 0;
codintion = string.Format("Từ ngày {0} đến ngày {1}. Đối tượng: {2}",
dtFromDate.Value.ToString("dd/MM/yyyy"), dtToDate.Value.ToString("dd/MM/yyyy"),
cboDoituongKCB.Text);
sheet[3, idxColSh].SetValue(Convert.ToString(codintion),
HamDungChung.styleStringCenter(book));
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(i + 1), HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToString(dt.Rows[i]["ten_benhnhan"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 2].SetValue(Convert.ToString(dt.Rows[i]["nam_sinh"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToString(dt.Rows[i]["gioi_tinh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToString(dt.Rows[i]["dia_chi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 5].SetValue(Convert.ToString(dt.Rows[i]["IsBHYT"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 7].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 8].SetValue(Convert.ToString(dt.Rows[i]["ten_chitietdichvu"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 9].SetValue(Convert.ToString(dt.Rows[i]["phuongphap_vocam"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 10].SetValue(Convert.ToString(dt.Rows[i]["ngay_thuchien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 11].SetValue(Convert.ToString(dt.Rows[i]["loai_phauthuat"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 12].SetValue(Convert.ToString(dt.Rows[i]["nguoi_thuchien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 13].SetValue(Convert.ToString(dt.Rows[i]["bacsy_gayme"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 14].SetValue(Convert.ToString(dt.Rows[i]["ghi_chu"]), HamDungChung.styleStringLeft(book));
idxRow = idxRow + 1;
}
getTime = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss"));
pathDirectory = AppDomain.CurrentDomain.BaseDirectory +
"TemplateExcel\\ExportExcel\\";
if (!Directory.Exists(pathDirectory))
{
Directory.CreateDirectory(pathDirectory);
}
book.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" +
reportcode +
getTime + ".xls");
Process.Start(
new ProcessStartInfo(AppDomain.CurrentDomain.BaseDirectory +
"\\TemplateExcel\\ExportExcel\\" +
reportcode + getTime + ".xls"));
break;
}
}
else
{
Utility.ShowMsg("Không có dữ liệu để báo cáo!");
}
// ExcelUtlity.ExportGridEx(grdResult);
}
catch (Exception exception)
{
Utility.ShowMsg("Lỗi:" + exception.Message);
}
}
//---------------------------------------------------------------------------------
#region "** object model"
/// <summary>
/// Saves the content of a C1FlexGrid into an XLSheet.
/// </summary>
public static void Save(C1FlexGrid flex, XLSheet sheet)
{
// clear style cache if this is a new book
if (!object.ReferenceEquals(sheet.Book, _lastBook))
{
_cellStyles.Clear();
_excelStyles.Clear();
_lastBook = sheet.Book;
}
// save global parameters
sheet.DefaultRowHeight = PixelsToTwips(flex.Rows.DefaultSize);
sheet.DefaultColumnWidth = PixelsToTwips(flex.Columns.DefaultSize);
sheet.ShowGridLines = flex.GridLinesVisibility != GridLinesVisibility.None;
sheet.ShowHeaders = flex.HeadersVisibility != HeadersVisibility.None;
sheet.OutlinesBelow = flex.GroupRowPosition == GroupRowPosition.BelowData;
// save columns
sheet.Columns.Clear();
foreach (Column col in flex.Columns)
{
dynamic c = sheet.Columns.Add();
if (!col.Width.IsAuto)
{
c.Width = PixelsToTwips(col.ActualWidth);
}
c.Visible = col.Visible;
if (col.CellStyle is ExcelCellStyle)
{
c.Style = GetXLStyle(flex, sheet, (ExcelCellStyle)col.CellStyle);
}
}
sheet.Rows.Clear();
//save column headers
XLStyle headerStyle = default(XLStyle);
headerStyle = new XLStyle(sheet.Book);
headerStyle.Font = new XLFont("Microsoft YaHei", 10, true, false);
headerStyle.BorderLeft = XLLineStyleEnum.Thin;
headerStyle.BorderTop = XLLineStyleEnum.Thin;
headerStyle.BorderRight = XLLineStyleEnum.Thin;
headerStyle.BorderBottom = XLLineStyleEnum.Thin;
//save column headers
foreach (Row row in flex.ColumnHeaders.Rows)
{
dynamic r = sheet.Rows.Add();
if (row.Height > -1)
{
r.Height = PixelsToTwips(row.Height);
}
if (row.CellStyle is ExcelCellStyle)
{
r.Style = GetXLStyle(flex, sheet, (ExcelCellStyle)row.CellStyle);
}
if (row is ExcelRow)
{
r.OutlineLevel = ((ExcelRow)row).Level;
}
for (int c = 0; c <= flex.ColumnHeaders.Columns.Count - 1; c++)
{
// save cell value
dynamic cell = sheet[row.Index, c];
string colHeader = flex.Columns[c].Header;
cell.Value = colHeader;
// make column headers bold
cell.Style = headerStyle;
}
r.Visible = row.Visible;
}
// save rows
foreach (Row row in flex.Rows)
{
dynamic r = sheet.Rows.Add();
if (row.Height > -1)
{
r.Height = PixelsToTwips(row.Height);
}
if (row.CellStyle is ExcelCellStyle)
{
r.Style = GetXLStyle(flex, sheet, (ExcelCellStyle)row.CellStyle);
}
if (row is ExcelRow)
{
r.OutlineLevel = ((ExcelRow)row).Level;
}
r.Visible = row.Visible;
}
XLStyle dateTimeStyle = default(XLStyle);
dateTimeStyle = new XLStyle(sheet.Book);
dateTimeStyle.Font = new XLFont("Microsoft YaHei", 10, false, false);
dateTimeStyle.Format = "yyyy/mm/dd";
dateTimeStyle.BorderLeft = XLLineStyleEnum.Thin;
dateTimeStyle.BorderTop = XLLineStyleEnum.Thin;
dateTimeStyle.BorderRight = XLLineStyleEnum.Thin;
dateTimeStyle.BorderBottom = XLLineStyleEnum.Thin;
XLStyle normalStyle = default(XLStyle);
normalStyle = new XLStyle(sheet.Book);
normalStyle.Font = new XLFont("Microsoft YaHei", 10, false, false);
normalStyle.BorderLeft = XLLineStyleEnum.Thin;
normalStyle.BorderTop = XLLineStyleEnum.Thin;
normalStyle.BorderRight = XLLineStyleEnum.Thin;
normalStyle.BorderBottom = XLLineStyleEnum.Thin;
// save cells
for (int r = flex.ColumnHeaders.Rows.Count; r <= flex.Rows.Count; r++)
{
for (int c = 0; c <= flex.Columns.Count - 1; c++)
{
// save cell value
dynamic cell = sheet[r, c];
dynamic obj = flex[r - 1, c];
if (obj is DateTime)
{
cell.Style = dateTimeStyle;
}
else
{
cell.Style = normalStyle;
}
cell.Value = obj is FrameworkElement ? 0 : obj;
// save cell formula and style
//dynamic row = flex.Rows[r - 1] as ExcelRow;
//if(row != null)
//{
// // save cell formula
// dynamic col = flex.Columns[c];
// // save cell style
// dynamic cs = row.GetCellStyle(col) as ExcelCellStyle;
// if(cs != null)
// {
// cell.Style = GetXLStyle(flex, sheet, cs);
// }
//}
}
}
// save selection
dynamic sel = flex.Selection;
if (sel.IsValid)
{
dynamic xlSel = new XLCellRange(sheet, sel.Row, sel.Row2, sel.Column, sel.Column2);
sheet.SelectedCells.Clear();
sheet.SelectedCells.Add(xlSel);
}
}
//===========================================================================================
#region ** Save a C1FlexGrid into an XLSheet
private void SaveSheet(C1FlexGrid flex, XLSheet sheet, bool fixedCells)
{
// account for fixed cells
int frows = flex.Rows.Fixed;
int fcols = flex.Cols.Fixed;
if (fixedCells)
{
frows = fcols = 0;
}
// copy dimensions
int lastRow = flex.Rows.Count - frows - 1;
int lastCol = flex.Cols.Count - fcols - 1;
if (lastRow < 0 || lastCol < 0)
{
return;
}
XLCell cell = sheet[lastRow, lastCol];
// set default properties
sheet.Book.DefaultFont = flex.Font;
sheet.DefaultRowHeight = C1XLBook.PixelsToTwips(flex.Rows.DefaultSize);
sheet.DefaultColumnWidth = C1XLBook.PixelsToTwips(flex.Cols.DefaultSize);
// prepare to convert styles
_styles = new Hashtable();
// set row/column properties
for (int r = frows; r < flex.Rows.Count; r++)
{
// size/visibility
Row fr = flex.Rows[r];
XLRow xr = sheet.Rows[r - frows];
if (fr.Height >= 0)
{
xr.Height = C1XLBook.PixelsToTwips(fr.Height);
}
xr.Visible = fr.Visible;
// style
XLStyle xs = StyleFromFlex(fr.Style);
if (xs != null)
{
xr.Style = xs;
}
}
for (int c = fcols; c < flex.Cols.Count; c++)
{
// size/visibility
Column fc = flex.Cols[c];
XLColumn xc = sheet.Columns[c - fcols];
if (fc.Width >= 0)
{
xc.Width = C1XLBook.PixelsToTwips(fc.Width);
}
xc.Visible = fc.Visible;
// style
XLStyle xs = StyleFromFlex(fc.Style);
if (xs != null)
{
xc.Style = xs;
}
}
// load cells
for (int r = frows; r < flex.Rows.Count; r++)
{
for (int c = fcols; c < flex.Cols.Count; c++)
{
// get cell
cell = sheet[r - frows, c - fcols];
// apply content
cell.Value = flex[r, c];
// apply style
XLStyle xs = StyleFromFlex(flex.GetCellStyle(r, c));
if (xs != null)
{
cell.Style = xs;
}
}
}
}
public C1XLBook CreateSample()
{
// create C1XLBook
C1XLBook wb = new C1XLBook();
// three methods add images to Excel file
XLPictureShape picture;
// create images
Metafile metafile = (Metafile)Metafile.FromStream(GetManifestResource("meta.emf"));
Image spbImage = Image.FromStream(GetManifestResource("spb.jpg"));
Image canadaImage = Image.FromStream(GetManifestResource("canada.bmp"));
Image googleImage = Image.FromStream(GetManifestResource("google.bmp"));
Image babyImage = Image.FromStream(GetManifestResource("baby.png"));
/////////////////////////////////////////////////////////
// List "Images" -- three methods add images
/////////////////////////////////////////////////////////
XLSheet sheet = wb.Sheets[0];
sheet.Name = "Images";
sheet.PrintSettings.Header = "&LCanada:&G&D&CHeader Center&R&P";
sheet.PrintSettings.Footer = "&C&Z";
// first method
picture = new XLPictureShape(googleImage, 0, 0, 2200, 5000);
picture.DashedLineStyle = XLShapeDashedLineStyleEnum.Solid;
picture.LineStyle = XLShapeLineStyleEnum.Simple;
picture.LineColor = Color.BlueViolet;
picture.Rotation = 90.0f;
picture.LineWidth = 10;
sheet[1, 7].Value = picture;
sheet[1, 1].Value = metafile;
// second method
picture = new XLPictureShape(spbImage, 100, 3000, 8000, 6000);
//picture = new XLPictureShape(canadaImage);
picture.Brightness = .55f;
picture.Contrast = .5f;
sheet.Shapes.Add(picture);
picture = new XLPictureShape(canadaImage);
picture.LineColor = Color.Aqua;
picture.LineWidth = 100;
sheet.PrintSettings.HeaderPictureLeft = picture;
// third method
picture = new XLPictureShape(babyImage);
picture.Rotation = 30.0f;
picture.LineColor = Color.Aqua;
picture.LineWidth = 100;
sheet[15, 2].Value = picture;
// header or footer image
//picture = new XLPictureShape(spbImage);
//picture.Rotation = 45.0f; // for header or footer ignored
//picture.LineColor = Color.Blue;
//picture.LineWidth = 50;
//picture.Brightness = 0.4f;
//sheet.PrintSettings.HeaderPictureCenter = picture;
/////////////////////////////////////////////////////////
// List "Types" -- support image types (bmp, png, jpg, emf)
/////////////////////////////////////////////////////////
sheet = wb.Sheets.Add("Types");
sheet[1, 0].Value = "Bmp:";
sheet[1, 1].Value = googleImage;
sheet[8, 0].Value = "Png:";
sheet[8, 1].Value = babyImage;
sheet[25, 0].Value = "Jpeg:";
sheet[25, 1].Value = spbImage;
sheet[34, 0].Value = "Emf:";
sheet[34, 1].Value = metafile;
/////////////////////////////////////////////////////////
// List "Borders" -- various picture borders
/////////////////////////////////////////////////////////
sheet = wb.Sheets.Add("Borders");
int row = 1, col = 0;
foreach (XLShapeLineStyleEnum style in Enum.GetValues(typeof(XLShapeLineStyleEnum)))
{
col = 1;
sheet.Rows[row].Height = 3700;
foreach (XLShapeDashedLineStyleEnum dashedStyle in Enum.GetValues(typeof(XLShapeDashedLineStyleEnum)))
{
sheet.Columns[col].Width = 2300;
picture = new XLPictureShape(babyImage);
picture.LineWidth = 100;
picture.LineColor = Color.FromArgb(100, 200, Math.Min(col * 12, 255));
picture.DashedLineStyle = dashedStyle;
picture.LineStyle = style;
sheet[row, col].Value = picture;
sheet[row + 1, col].Value = "style: " + style.ToString();
sheet[row + 2, col].Value = "dashed: " + dashedStyle.ToString();
col += 2;
}
row += 4;
}
/////////////////////////////////////////////////////////
// List "Alignment" -- position image using ContentAlignment
/////////////////////////////////////////////////////////
sheet = wb.Sheets.Add("Alignment");
row = 1;
sheet.Columns[1].Width = sheet.Columns[4].Width = 6000;
sheet.Columns[7].Width = sheet.Columns[10].Width = 2000;
foreach (ContentAlignment alignment in Enum.GetValues(typeof(ContentAlignment)))
{
sheet.Rows[row].Height = 2400;
Size cellSize = new Size(sheet.Columns[1].Width, sheet.Rows[row].Height);
picture = new XLPictureShape(googleImage, cellSize, alignment, ImageScaling.Clip);
sheet[row, 1].Value = picture;
sheet[row, 2].Value = "clip: " + alignment.ToString();
picture = new XLPictureShape(googleImage, cellSize, alignment, ImageScaling.Scale);
sheet[row, 4].Value = picture;
sheet[row, 5].Value = "scale: " + alignment.ToString();
row += 4;
}
/////////////////////////////////////////////////////////
// List "Properties" -- various picture properties
/////////////////////////////////////////////////////////
sheet = wb.Sheets.Add("Properties");
// associating hyperlink with the shape
sheet.Rows[1].Height = 2000;
sheet.Columns[1].Width = 3600;
picture = new XLPictureShape(spbImage);
picture.Hyperlink = "http://www.spb.ru/";
sheet[1, 1].Value = picture;
sheet[2, 1].Value = "hyperlink (click on the picture)";
// others view type
col = 1;
sheet.Rows[4].Height = 2000;
foreach (XLPictureViewType viewType in Enum.GetValues(typeof(XLPictureViewType)))
{
sheet.Columns[col].Width = 3600;
picture = new XLPictureShape(spbImage);
picture.ViewType = viewType;
sheet[4, col].Value = picture;
sheet[5, col].Value = "view type: " + viewType.ToString();;
col += 2;
}
// brightness & contrast
col = 1;
sheet.Rows[7].Height = sheet.Rows[10].Height = 2000;
for (int i = 0; i <= 100; i += 10)
{
sheet.Columns[col].Width = 3600;
picture = new XLPictureShape(spbImage);
picture.Brightness = (float)i / 100;
sheet[7, col].Value = picture;
sheet[8, col].Value = string.Format("brightness: {0}", picture.Brightness);
picture = new XLPictureShape(spbImage);
picture.Contrast = (float)i / 100;
sheet[10, col].Value = picture;
sheet[11, col].Value = string.Format("contrast: {0}", picture.Contrast);
col += 2;
}
// done
return(wb);
}
public C1XLBook CreateSample()
{
// create C1XLBook
C1XLBook wb = new C1XLBook();
XLSheet ws = wb.Sheets[0];
// column width in twips
ws.Columns[0].Width = 2000;
ws.Columns[1].Width = 2200;
// string formulas
string s = "String:";
ws[0, 0].Value = s;
ws[1, 0].Value = s;
ws[2, 0].Value = s;
ws[0, 1].Value = "apples";
ws[1, 1].Value = "and";
ws[2, 1].Value = "oranges";
s = "String formula:";
ws[4, 0].Value = s;
ws[5, 0].Value = s;
ws[4, 1].Value = "apples and oranges";
ws[5, 1].Value = "apples an";
ws[4, 1].Formula = "CONCATENATE(B1,\" \",B2, \" \",B3)";
ws[5, 1].Formula = "LEFT(B5,9)";
// simple formulas
ws[7, 0].Value = "Formula: 5!";
ws[7, 1].Value = 120;
ws[7, 1].Formula = "1*2*3*4*5";
ws[8, 0].Value = "Formula: 12/0";
ws[8, 1].Value = 0;
ws[8, 1].Formula = "12/0";
ws[9, 0].Value = "Formula: 1 = 1";
ws[9, 1].Value = true;
ws[9, 1].Formula = "1=1";
ws[10, 0].Value = "Formula: 1 = 2";
ws[10, 1].Value = false;
ws[10, 1].Formula = "1 = 2";
// now function
ws[12, 0].Value = "Formula: Now()";
ws[12, 1].Value = DateTime.Now;
ws[12, 1].Formula = "Now()";
XLStyle style = new XLStyle(wb);
DateTimeFormatInfo dtfi = CultureInfo.CurrentCulture.DateTimeFormat;
style.Format = XLStyle.FormatDotNetToXL(dtfi.ShortDatePattern + " " + dtfi.ShortTimePattern);
ws[12, 1].Style = style;
// done
return(wb);
}
private void _btCreate_Click(object sender, RoutedEventArgs e)
{
// create new workbook
if (_book == null)
{
_book = new C1XLBook();
}
// clear the book
_book.Clear();
// first sheet
XLSheet sheet = _book.Sheets[0];
// column width in twips
sheet.Columns[0].Width = 2000;
sheet.Columns[1].Width = 2200;
// string formulas
string s = "String:";
sheet[0, 0].Value = s;
sheet[1, 0].Value = s;
sheet[2, 0].Value = s;
sheet[0, 1].Value = "apples";
sheet[1, 1].Value = "and";
sheet[2, 1].Value = "oranges";
s = "String formula:";
sheet[4, 0].Value = s;
sheet[5, 0].Value = s;
sheet[4, 1].Value = "apples and oranges";
sheet[5, 1].Value = "apples an";
sheet[4, 1].Formula = "CONCATENATE(B1,\" \",B2, \" \",B3)";
sheet[5, 1].Formula = "LEFT(B5,9)";
// simple formulas
sheet[7, 0].Value = "Formula: 5!";
sheet[7, 1].Value = 120;
sheet[7, 1].Formula = "1*2*3*4*5";
sheet[8, 0].Value = "Formula: 12/0";
sheet[8, 1].Value = 0;
sheet[8, 1].Formula = "12/0";
sheet[9, 0].Value = "Formula: 1 = 1";
sheet[9, 1].Value = true;
sheet[9, 1].Formula = "1=1";
sheet[10, 0].Value = "Formula: 1 = 2";
sheet[10, 1].Value = false;
sheet[10, 1].Formula = "1 = 2";
// now function
sheet[12, 0].Value = "Formula: Now()";
sheet[12, 1].Value = DateTime.Now;
sheet[12, 1].Formula = "Now()";
XLStyle style = new XLStyle(_book);
DateTimeFormatInfo dtfi = CultureInfo.CurrentCulture.DateTimeFormat;
style.Format = XLStyle.FormatDotNetToXL(dtfi.ShortDatePattern + " " + dtfi.ShortTimePattern);
sheet[12, 1].Style = style;
// allow save the file
_lblStatus.Text = "You can save workbook";
_btnSave.IsEnabled = true;
}
private async void ExcelButton_Click(object sender, RoutedEventArgs e)
{
// 現在、FlexGrid に表示されている順のデータ
var currentData = this.flexgrid1.Rows.Select(r => r.DataItem).Cast <Book>();
// Excel データの作成
// https://docs.grapecity.com/help/c1/uwp/uwp_excel/#Step_2_of_4-_Adding_Content_to_a_C1XLBook.html
// 新しい Excel ワークブックを作成
var xlBook = new C1XLBook();
// デフォルトで作成されたシートを取得
XLSheet sheet = xlBook.Sheets[0];
// シートの中身を書き込みます
int rowIndex = 0;
// ヘッダー行
sheet[rowIndex, 0].Value = "書名";
sheet[rowIndex, 1].Value = "ISBN";
sheet[rowIndex, 2].Value = "バーコード";
sheet.Columns[2].Width
= C1XLBook.PixelsToTwips(this.HiddenBarCode.ActualWidth);
sheet[rowIndex, 3].Value = "価格";
// データ行
foreach (var book in currentData)
{
rowIndex++;
// バーコードの画像を作る
this.HiddenBarCode.Text = book.IsbnWithoutCheckDigit;
C1Bitmap bitmap = new C1Bitmap();
using (var ms = new InMemoryRandomAccessStream().AsStream())
{
await this.HiddenBarCode.SaveAsync(ms, ImageFormat.Png);
bitmap.Load(ms);
}
// 行の高さをバーコードの画像に合わせる
sheet.Rows[rowIndex].Height
= C1XLBook.PixelsToTwips(this.HiddenBarCode.ActualHeight);
// 1行分のデータとバーコード画像をセット
sheet[rowIndex, 0].Value = book.Title;
sheet[rowIndex, 1].Value = book.Isbn;
sheet[rowIndex, 2].Value = bitmap;
sheet[rowIndex, 3].Value = book.Price;
}
// Excel ファイルへの書き出し
// https://docs.grapecity.com/help/c1/uwp/uwp_excel/#Step_3_of_4-_Saving_the_XLSX_File.html
var picker = new FileSavePicker()
{
SuggestedStartLocation = PickerLocationId.DocumentsLibrary
};
picker.FileTypeChoices.Add("Open XML Excel ファイル", new string[] { ".xlsx", });
picker.FileTypeChoices.Add("BIFF Excel ファイル", new string[] { ".xls", });
picker.SuggestedFileName = "BarCodeControlSample";
var file = await picker.PickSaveFileAsync();
if (file != null)
{
var fileFormat = Path.GetExtension(file.Path).Equals(".xls") ? FileFormat.OpenXmlTemplate : FileFormat.OpenXml;
await xlBook.SaveAsync(file, fileFormat);
}
}
private void CreateSheet(DataRow dr)
{
//get current category name
string catName = (string)dr["CategoryName"];
//add a new worksheet to the workbook
//('/' is invalid in sheet names, so replace it with '+')
string sheetName = catName.Replace("/", " + ");
XLSheet sheet = _c1xl.Sheets.Add(sheetName);
//add title to worksheet
sheet[0, 0].Value = catName;
sheet.Rows[0].Style = _styTitle;
// set column widths (in twips)
sheet.Columns[0].Width = 300;
sheet.Columns[1].Width = 2200;
sheet.Columns[2].Width = 1000;
sheet.Columns[3].Width = 1600;
sheet.Columns[4].Width = 1000;
sheet.Columns[5].Width = 1000;
sheet.Columns[6].Width = 1000;
//add column headers
int row = 2;
sheet.Rows[row].Style = _styHeader;
sheet[row, 1].Value = "Product Name";
sheet[row, 2].Value = "Unit Price";
sheet[row, 3].Value = "Qty/Unit";
sheet[row, 4].Value = "Stock Units";
sheet[row, 5].Value = "Stock Value";
sheet[row, 6].Value = "Reorder";
//loop through products in this category
DataRow[] products = dr.GetChildRows("Categories_Products");
foreach (DataRow product in products)
{
//move on to next row
row++;
//add row with some data
sheet[row, 1].Value = product["ProductName"];
sheet[row, 2].Value = product["UnitPrice"];
sheet[row, 3].Value = product["QuantityPerUnit"];
sheet[row, 4].Value = product["UnitsInStock"];
//calculate value in stock
double valueInStock =
Convert.ToDouble(product["UnitPrice"]) *
Convert.ToInt32(product["UnitsInStock"]);
sheet[row, 5].Value = valueInStock;
//check reorder level
if (Convert.ToInt32(product["UnitsInStock"]) <=
Convert.ToInt32(product["ReorderLevel"]))
{
sheet[row, 6].Value = "<<<";
sheet[row, 6].Style = _styOrder;
}
//format money cells
sheet[row, 2].Style = _styMoney;
sheet[row, 5].Style = _styMoney;
}
if (products.Length == 0)
{
row++;
sheet[row, 1].Value = "No products in this category";
}
}
//---------------------------------------------------------------------------------
#region "** object model"
/// <summary>
/// Saves the content of a C1FlexGrid into an XLSheet.
/// </summary>
public static void Save(C1FlexGrid flex, XLSheet sheet)
{
// clear style cache if this is a new book
if (!object.ReferenceEquals(sheet.Book, _lastBook))
{
_cellStyles.Clear();
_excelStyles.Clear();
_lastBook = sheet.Book;
}
// save global parameters
sheet.DefaultRowHeight = PixelsToTwips(flex.Rows.DefaultSize);
sheet.DefaultColumnWidth = PixelsToTwips(flex.Columns.DefaultSize);
sheet.Locked = flex.IsReadOnly;
sheet.ShowGridLines = flex.GridLinesVisibility != GridLinesVisibility.None;
sheet.ShowHeaders = flex.HeadersVisibility != HeadersVisibility.None;
sheet.OutlinesBelow = flex.GroupRowPosition == GroupRowPosition.BelowData;
// save columns
sheet.Columns.Clear();
foreach (Column col in flex.Columns)
{
dynamic c = sheet.Columns.Add();
if (!col.Width.IsAuto)
{
c.Width = PixelsToTwips(col.ActualWidth);
}
c.Visible = col.Visible;
if (col.CellStyle is ExcelCellStyle)
{
c.Style = GetXLStyle(flex, sheet, (ExcelCellStyle)col.CellStyle);
}
}
sheet.Rows.Clear();
//save column headers
XLStyle headerStyle = default(XLStyle);
headerStyle = new XLStyle(sheet.Book);
headerStyle.Font = new XLFont("Arial", 10, true, false);
foreach (Row row in flex.ColumnHeaders.Rows)
{
dynamic r = sheet.Rows.Add();
if (row.Height > -1)
{
r.Height = PixelsToTwips(row.Height);
}
if (row.CellStyle is ExcelCellStyle)
{
r.Style = GetXLStyle(flex, sheet, (ExcelCellStyle)row.CellStyle);
}
if (row is ExcelRow)
{
r.OutlineLevel = ((ExcelRow)row).Level;
}
for (int c = 0; c <= flex.ColumnHeaders.Columns.Count - 1; c++)
{
// save cell value
dynamic cell = sheet[row.Index, c];
string colHeader = flex.ColumnHeaders[row.Index, c] != null ? flex.ColumnHeaders[row.Index, c].ToString() : flex.Columns[c].ColumnName;
cell.Value = colHeader;
// make column headers bold
cell.Style = headerStyle;
}
r.Visible = row.Visible;
}
// save rows
foreach (Row row in flex.Rows)
{
dynamic r = sheet.Rows.Add();
if (row.Height > -1)
{
r.Height = PixelsToTwips(row.Height);
}
if (row.CellStyle is ExcelCellStyle)
{
r.Style = GetXLStyle(flex, sheet, (ExcelCellStyle)row.CellStyle);
}
if (row is ExcelRow)
{
r.OutlineLevel = ((ExcelRow)row).Level;
}
r.Visible = row.Visible;
}
int rowStart = flex.ColumnHeaders.Rows.Count;
// save cells
for (int r = 0; r <= flex.Rows.Count - 1; r++, rowStart++)
{
for (int c = 0; c <= flex.Columns.Count - 1; c++)
{
// save cell value
dynamic cell = sheet[rowStart, c];
dynamic obj = flex[r, c];
cell.Value = obj is FrameworkElement ? 0 : obj;
// save cell formula and style
dynamic row = flex.Rows[r] as ExcelRow;
if (row != null)
{
// save cell formula
dynamic col = flex.Columns[c];
// save cell style
dynamic cs = row.GetCellStyle(col) as ExcelCellStyle;
if (cs != null)
{
cell.Style = GetXLStyle(flex, sheet, cs);
}
}
}
}
// save selection
dynamic sel = flex.Selection;
if (sel.IsValid)
{
dynamic xlSel = new XLCellRange(sheet, sel.Row, sel.Row2, sel.Column, sel.Column2);
sheet.SelectedCells.Clear();
sheet.SelectedCells.Add(xlSel);
}
}
// convert grid styles into excel styles
private static XLStyle GetXLStyle(C1FlexGrid flex, XLSheet sheet, ExcelCellStyle s)
{
// look it up in the cache
XLStyle x = default(XLStyle);
if (_excelStyles.TryGetValue(s, out x))
{
return(x);
}
// not found, create style now
x = new XLStyle(sheet.Book);
// alignment
if (s.HorizontalAlignment.HasValue)
{
switch (s.HorizontalAlignment.Value)
{
case HorizontalAlignment.Left:
x.AlignHorz = XLAlignHorzEnum.Left;
break;
case HorizontalAlignment.Center:
x.AlignHorz = XLAlignHorzEnum.Center;
break;
case HorizontalAlignment.Right:
x.AlignHorz = XLAlignHorzEnum.Right;
break;
}
}
if (s.VerticalAlignment.HasValue)
{
switch (s.VerticalAlignment.Value)
{
case VerticalAlignment.Top:
x.AlignVert = XLAlignVertEnum.Top;
break;
case VerticalAlignment.Center:
x.AlignVert = XLAlignVertEnum.Center;
break;
case VerticalAlignment.Bottom:
x.AlignVert = XLAlignVertEnum.Bottom;
break;
}
}
if (s.TextWrapping.HasValue)
{
x.WordWrap = s.TextWrapping.Value;
}
// colors
if (s.Background is SolidColorBrush)
{
x.BackColor = ((SolidColorBrush)s.Background).Color;
x.BackPattern = XLPatternEnum.Solid;
}
if (s.Foreground is SolidColorBrush)
{
x.ForeColor = ((SolidColorBrush)s.Foreground).Color;
}
// font
dynamic fontName = flex.FontFamily.Source;
dynamic fontSize = flex.FontSize;
dynamic bold = false;
dynamic italic = false;
bool underline = false;
bool hasFont = false;
if (s.FontFamily != null)
{
fontName = s.FontFamily.Source;
hasFont = true;
}
if (s.FontSize.HasValue)
{
fontSize = s.FontSize.Value;
hasFont = true;
}
if (s.FontWeight.HasValue)
{
bold = s.FontWeight.Value == FontWeights.Bold || s.FontWeight.Value == FontWeights.ExtraBold || s.FontWeight.Value == FontWeights.SemiBold;
hasFont = true;
}
if (s.FontStyle.HasValue)
{
italic = s.FontStyle.Value == FontStyles.Italic;
hasFont = true;
}
if (s.TextDecorations != null)
{
underline = true;
hasFont = true;
}
if (hasFont)
{
fontSize = PixelsToPoints(fontSize);
if (underline)
{
dynamic color = Colors.Black;
if (flex.Foreground is SolidColorBrush)
{
color = ((SolidColorBrush)flex.Foreground).Color;
}
if (s.Foreground is SolidColorBrush)
{
color = ((SolidColorBrush)s.Foreground).Color;
}
x.Font = new XLFont(fontName, Convert.ToSingle(fontSize), bold, italic, false, XLFontScript.None, XLUnderlineStyle.Single, color);
}
else
{
x.Font = new XLFont(fontName, Convert.ToSingle(fontSize), bold, italic);
}
}
// format
if (!string.IsNullOrEmpty(s.Format))
{
x.Format = XLStyle.FormatDotNetToXL(s.Format);
}
// borders
if (s.CellBorderThickness.Left > 0 && s.CellBorderBrushLeft is SolidColorBrush)
{
x.BorderLeft = GetBorderLineStyle(s.CellBorderThickness.Left);
x.BorderColorLeft = ((SolidColorBrush)s.CellBorderBrushLeft).Color;
}
if (s.CellBorderThickness.Top > 0 && s.CellBorderBrushTop is SolidColorBrush)
{
x.BorderTop = GetBorderLineStyle(s.CellBorderThickness.Top);
x.BorderColorTop = ((SolidColorBrush)s.CellBorderBrushTop).Color;
}
if (s.CellBorderThickness.Right > 0 && s.CellBorderBrushRight is SolidColorBrush)
{
x.BorderRight = GetBorderLineStyle(s.CellBorderThickness.Right);
x.BorderColorRight = ((SolidColorBrush)s.CellBorderBrushRight).Color;
}
if (s.CellBorderThickness.Bottom > 0 && s.CellBorderBrushBottom is SolidColorBrush)
{
x.BorderBottom = GetBorderLineStyle(s.CellBorderThickness.Bottom);
x.BorderColorBottom = ((SolidColorBrush)s.CellBorderBrushBottom).Color;
}
// save in cache and return
_excelStyles[s] = x;
return(x);
}
//---------------------------------------------------------------------------------
#region "** object model"
/// <summary>
/// Saves the content of a C1FlexGrid into an XLSheet.
/// </summary>
public static void Save(C1FlexGrid flex, XLSheet sheet)
{
// clear style cache if this is a new book
if(!object.ReferenceEquals(sheet.Book, _lastBook))
{
_cellStyles.Clear();
_excelStyles.Clear();
_lastBook = sheet.Book;
}
// save global parameters
sheet.DefaultRowHeight = PixelsToTwips(flex.Rows.DefaultSize);
sheet.DefaultColumnWidth = PixelsToTwips(flex.Columns.DefaultSize);
sheet.ShowGridLines = flex.GridLinesVisibility != GridLinesVisibility.None;
sheet.ShowHeaders = flex.HeadersVisibility != HeadersVisibility.None;
sheet.OutlinesBelow = flex.GroupRowPosition == GroupRowPosition.BelowData;
// save columns
sheet.Columns.Clear();
foreach(Column col in flex.Columns)
{
dynamic c = sheet.Columns.Add();
if(!col.Width.IsAuto)
{
c.Width = PixelsToTwips(col.ActualWidth);
}
c.Visible = col.Visible;
if(col.CellStyle is ExcelCellStyle)
{
c.Style = GetXLStyle(flex, sheet, (ExcelCellStyle)col.CellStyle);
}
}
sheet.Rows.Clear();
//save column headers
XLStyle headerStyle = default(XLStyle);
headerStyle = new XLStyle(sheet.Book);
headerStyle.Font = new XLFont("Microsoft YaHei", 10, true, false);
headerStyle.BorderLeft = XLLineStyleEnum.Thin;
headerStyle.BorderTop = XLLineStyleEnum.Thin;
headerStyle.BorderRight = XLLineStyleEnum.Thin;
headerStyle.BorderBottom = XLLineStyleEnum.Thin;
//save column headers
foreach(Row row in flex.ColumnHeaders.Rows)
{
dynamic r = sheet.Rows.Add();
if(row.Height > -1)
{
r.Height = PixelsToTwips(row.Height);
}
if(row.CellStyle is ExcelCellStyle)
{
r.Style = GetXLStyle(flex, sheet, (ExcelCellStyle)row.CellStyle);
}
if(row is ExcelRow)
{
r.OutlineLevel = ((ExcelRow)row).Level;
}
for(int c = 0; c <= flex.ColumnHeaders.Columns.Count - 1; c++)
{
// save cell value
dynamic cell = sheet[row.Index, c];
string colHeader = flex.Columns[c].Header;
cell.Value = colHeader;
// make column headers bold
cell.Style = headerStyle;
}
r.Visible = row.Visible;
}
// save rows
foreach(Row row in flex.Rows)
{
dynamic r = sheet.Rows.Add();
if(row.Height > -1)
{
r.Height = PixelsToTwips(row.Height);
}
if(row.CellStyle is ExcelCellStyle)
{
r.Style = GetXLStyle(flex, sheet, (ExcelCellStyle)row.CellStyle);
}
if(row is ExcelRow)
{
r.OutlineLevel = ((ExcelRow)row).Level;
}
r.Visible = row.Visible;
}
XLStyle dateTimeStyle = default(XLStyle);
dateTimeStyle = new XLStyle(sheet.Book);
dateTimeStyle.Font = new XLFont("Microsoft YaHei", 10, false, false);
dateTimeStyle.Format = "yyyy/mm/dd";
dateTimeStyle.BorderLeft = XLLineStyleEnum.Thin;
dateTimeStyle.BorderTop = XLLineStyleEnum.Thin;
dateTimeStyle.BorderRight = XLLineStyleEnum.Thin;
dateTimeStyle.BorderBottom = XLLineStyleEnum.Thin;
XLStyle normalStyle = default(XLStyle);
normalStyle = new XLStyle(sheet.Book);
normalStyle.Font = new XLFont("Microsoft YaHei", 10, false, false);
normalStyle.BorderLeft = XLLineStyleEnum.Thin;
normalStyle.BorderTop = XLLineStyleEnum.Thin;
normalStyle.BorderRight = XLLineStyleEnum.Thin;
normalStyle.BorderBottom = XLLineStyleEnum.Thin;
// save cells
for(int r = flex.ColumnHeaders.Rows.Count; r <= flex.Rows.Count; r++)
{
for(int c = 0; c <= flex.Columns.Count - 1; c++)
{
// save cell value
dynamic cell = sheet[r, c];
dynamic obj = flex[r - 1, c];
if(obj is DateTime)
{
cell.Style = dateTimeStyle;
}
else
{
cell.Style = normalStyle;
}
cell.Value = obj is FrameworkElement ? 0 : obj;
// save cell formula and style
//dynamic row = flex.Rows[r - 1] as ExcelRow;
//if(row != null)
//{
// // save cell formula
// dynamic col = flex.Columns[c];
// // save cell style
// dynamic cs = row.GetCellStyle(col) as ExcelCellStyle;
// if(cs != null)
// {
// cell.Style = GetXLStyle(flex, sheet, cs);
// }
//}
}
}
// save selection
dynamic sel = flex.Selection;
if(sel.IsValid)
{
dynamic xlSel = new XLCellRange(sheet, sel.Row, sel.Row2, sel.Column, sel.Column2);
sheet.SelectedCells.Clear();
sheet.SelectedCells.Add(xlSel);
}
}
public static Worksheet ToWorksheet(this XLSheet sheet)
{
throw new NotImplementedException();
}
/// <summary>
/// Saves the content of a C1FlexGrid into an XLSheet.
/// </summary>
public static void Save(C1FlexGrid flex, XLSheet sheet)
{
// clear style cache if this is a new book
if (sheet.Book != _lastBook)
{
_cellStyles.Clear();
_excelStyles.Clear();
_lastBook = sheet.Book;
}
// save global parameters
sheet.DefaultRowHeight = PixelsToTwips(flex.Rows.DefaultSize);
sheet.DefaultColumnWidth = PixelsToTwips(flex.Columns.DefaultSize);
sheet.Locked = flex.IsReadOnly;
sheet.ShowGridLines = flex.GridLinesVisibility != GridLinesVisibility.None;
sheet.ShowHeaders = flex.HeadersVisibility != HeadersVisibility.None;
sheet.OutlinesBelow = flex.GroupRowPosition == GroupRowPosition.BelowData;
// save columns
sheet.Columns.Clear();
foreach (Column col in flex.Columns)
{
var c = sheet.Columns.Add();
if (!col.Width.IsAuto)
{
c.Width = PixelsToTwips(col.ActualWidth);
}
c.Visible = col.Visible;
if (col.CellStyle is ExcelCellStyle)
{
c.Style = GetXLStyle(flex, sheet, (ExcelCellStyle)col.CellStyle);
}
}
// save rows
sheet.Rows.Clear();
foreach (Row row in flex.Rows)
{
var r = sheet.Rows.Add();
if (row.Height > -1)
{
r.Height = PixelsToTwips(row.Height);
}
if (row.CellStyle is ExcelCellStyle)
{
r.Style = GetXLStyle(flex, sheet, (ExcelCellStyle)row.CellStyle);
}
if (row is ExcelRow)
{
r.OutlineLevel = ((ExcelRow)row).Level;
}
r.Visible = row.Visible;
}
// save cells
for (int r = 0; r < flex.Rows.Count; r++)
{
for (int c = 0; c < flex.Columns.Count; c++)
{
// save cell value
var cell = sheet[r, c];
var obj = flex[r, c];
cell.Value = obj is FrameworkElement ? 0 : obj;
// save cell formula and style
var row = flex.Rows[r] as ExcelRow;
if (row != null)
{
// save cell formula
var col = flex.Columns[c];
var formula = row.GetDataEditor(col);
if (formula.StartsWith("="))
{
cell.Formula = formula;
}
// save cell style
var cs = row.GetCellStyle(col) as ExcelCellStyle;
if (cs != null)
{
cell.Style = GetXLStyle(flex, sheet, cs);
}
}
}
}
// save selection
var sel = flex.Selection;
if (sel.IsValid)
{
var xlSel = new XLCellRange(sheet, sel.Row, sel.Row2, sel.Column, sel.Column2);
sheet.SelectedCells.Clear();
sheet.SelectedCells.Add(xlSel);
}
// save merged cells
var xmm = flex.MergeManager as ExcelMergeManager;
if (xmm != null)
{
xmm.SetMergedRanges(sheet);
}
}
public static void Create_Sheet(ref XLSheet sheet, DevExpress.XtraGrid.Views.Grid.GridView grid)
{
try
{
if (sheet == null)
{
throw new ArgumentNullException("sheet", "sheet is null.");
}
if (grid.RowCount == 0)
{
return;
}
sheet.Rows[6].Height = 600;
sheet.Columns[0].Width = 1200;
sheet.Columns[1].Width = 1200;
sheet.Columns[2].Width = 1200;
sheet.Columns[3].Width = 1200;
sheet.Columns[4].Width = 1200;
sheet.Columns[5].Width = 1200;
sheet.Columns[6].Width = 1200;
sheet.Columns[7].Width = 1200;
sheet.Columns[8].Width = 1200;
sheet.Columns[9].Width = 1200;
sheet.Columns[10].Width = 1600;
sheet.Columns[11].Width = 1200;
sheet.Columns[12].Width = 1200;
sheet.Columns[13].Width = 1200;
sheet.Columns[14].Width = 1000;
sheet.Columns[15].Width = 1600;
sheet.Columns[16].Width = 1200;
sheet.Columns[17].Width = 1200;
sheet.Columns[18].Width = 1200;
sheet.Columns[19].Width = 1200;
sheet.Columns[20].Width = 1000;
sheet.Columns[21].Width = 1200;
sheet.Columns[22].Width = 1100;
sheet.Columns[23].Width = 1200;
sheet.Columns[24].Width = 1200;
sheet.Columns[25].Width = 1200;
sheet.Columns[26].Width = 1200;
#region Header
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 0, 0, "Export Country");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 1, 1, "SAP Style ID");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 2, 2, "Brand");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 3, 3, "Sub-brand");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 4, 4, "Production period yyyy/ww");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 5, 5, "Vendor ID");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 6, 6, "Factory ID");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 7, 7, "Collection");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 8, 8, "Product Type");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 9, 9, "Legacy Style ID");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 10, 10, "Style Description");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 11, 11, "P.O. No.");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 12, 12, "Commission No.");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 13, 13, "SAP Color");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 14, 14, "SAP Color Desc.");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 15, 15, "Marking");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 16, 16, "Garment size");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 17, 17, "Order Qty.");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 18, 18, "Packing Unit");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 19, 19, "Original ETD dd/mm/yyyy");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 20, 20, "Actual ETD dd/mm/yyyy");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 21, 21, "Shipment ETD dd/mm/yyyy");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 22, 22, "Responsible");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 23, 23, "Buyer Name");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 24, 24, "Advertised");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 25, 25, "Order Type");
MergeCells(ref sheet, ReportNameStyle(sheet.Book), 6, 6, 26, 26, "Shipment destination");
#endregion
int rowSheet = 7;
for (int row = 0; row <= grid.RowCount - 1; row++)
{
#region for
sheet[rowSheet + row, 0].SetValue(grid.GetRowCellValue(row, "Export_Country").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 1].SetValue(grid.GetRowCellValue(row, "SAP_Style_ID").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 2].SetValue(grid.GetRowCellValue(row, "Brand").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 3].SetValue(grid.GetRowCellValue(row, "Sub_brand").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 4].SetValue(grid.GetRowCellValue(row, "Production_period").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 5].SetValue(grid.GetRowCellValue(row, "Vendor_ID").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 6].SetValue(grid.GetRowCellValue(row, "Factory_ID").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 7].SetValue(grid.GetRowCellValue(row, "Collections").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 8].SetValue(grid.GetRowCellValue(row, "Product_Type").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 9].SetValue(grid.GetRowCellValue(row, "Legacy_Style").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 10].SetValue(grid.GetRowCellValue(row, "Style_Description").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 11].SetValue(grid.GetRowCellValue(row, "PO_Number").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 12].SetValue(grid.GetRowCellValue(row, "Commission_No").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 13].SetValue(grid.GetRowCellValue(row, "SAP_Color").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 14].SetValue(grid.GetRowCellValue(row, "SAP_Color_Desc").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 15].SetValue(grid.GetRowCellValue(row, "MARKING").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 16].SetValue(grid.GetRowCellValue(row, "Garment_size").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 17].SetValue(grid.GetRowCellValue(row, "Order_Qty"), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 18].SetValue(grid.GetRowCellValue(row, "Packing_Unit").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 19].SetValue(string.Format("{0:dd/MM/yyyy}", grid.GetRowCellValue(row, "ORI_ETD")), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 20].SetValue(string.Format("{0:dd/MM/yyyy}", grid.GetRowCellValue(row, "Actual_ETD")), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 21].SetValue(string.Format("{0:dd/MM/yyyy}", grid.GetRowCellValue(row, "Shipment_ETD")), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 22].SetValue(grid.GetRowCellValue(row, "Responsible").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 23].SetValue(grid.GetRowCellValue(row, "Buyer_Name").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 24].SetValue(grid.GetRowCellValue(row, "Advertised").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 25].SetValue(grid.GetRowCellValue(row, "Order_Type").ToString(), ReportNameStyle(sheet.Book));
sheet[rowSheet + row, 26].SetValue(grid.GetRowCellValue(row, "Shipment_destination").ToString(), ReportNameStyle(sheet.Book));
#endregion
}
#region "Footer"
#endregion
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// convert grid styles into excel styles
static XLStyle GetXLStyle(C1FlexGrid flex, XLSheet sheet, ExcelCellStyle s)
{
// look it up in the cache
XLStyle x;
if (_excelStyles.TryGetValue(s, out x))
{
return x;
}
// not found, create style now
x = new XLStyle(sheet.Book);
// alignment
if (s.HorizontalAlignment.HasValue)
{
switch (s.HorizontalAlignment.Value)
{
case HorizontalAlignment.Left:
x.AlignHorz = XLAlignHorzEnum.Left;
break;
case HorizontalAlignment.Center:
x.AlignHorz = XLAlignHorzEnum.Center;
break;
case HorizontalAlignment.Right:
x.AlignHorz = XLAlignHorzEnum.Right;
break;
}
}
if (s.VerticalAlignment.HasValue)
{
switch (s.VerticalAlignment.Value)
{
case VerticalAlignment.Top:
x.AlignVert = XLAlignVertEnum.Top;
break;
case VerticalAlignment.Center:
x.AlignVert = XLAlignVertEnum.Center;
break;
case VerticalAlignment.Bottom:
x.AlignVert = XLAlignVertEnum.Bottom;
break;
}
}
if (s.TextWrapping.HasValue)
{
x.WordWrap = s.TextWrapping.Value;
}
// colors
if (s.Background is SolidColorBrush)
{
x.BackColor = ((SolidColorBrush)s.Background).Color;
x.BackPattern = XLPatternEnum.Solid;
}
if (s.Foreground is SolidColorBrush)
{
x.ForeColor = ((SolidColorBrush)s.Foreground).Color;
}
// font
var fontName = flex.FontFamily.Source;
var fontSize = flex.FontSize;
var bold = false;
var italic = false;
bool underline = false;
bool hasFont = false;
if (s.FontFamily != null)
{
fontName = s.FontFamily.Source;
hasFont = true;
}
if (s.FontSize.HasValue)
{
fontSize = s.FontSize.Value;
hasFont = true;
}
if (s.FontWeight.HasValue)
{
bold = s.FontWeight.Value == FontWeights.Bold ||
s.FontWeight.Value == FontWeights.ExtraBold ||
s.FontWeight.Value == FontWeights.SemiBold;
hasFont = true;
}
if (s.FontStyle.HasValue)
{
italic = s.FontStyle.Value == FontStyles.Italic;
hasFont = true;
}
if (s.TextDecorations != null)
{
underline = true;
hasFont = true;
}
if (hasFont)
{
fontSize = PixelsToPoints(fontSize);
if (underline)
{
var color = Colors.Black;
if (flex.Foreground is SolidColorBrush)
{
color = ((SolidColorBrush)flex.Foreground).Color;
}
if (s.Foreground is SolidColorBrush)
{
color = ((SolidColorBrush)s.Foreground).Color;
}
x.Font = new XLFont(fontName, (float)fontSize, bold, italic, false, XLFontScript.None, XLUnderlineStyle.Single, color);
}
else
{
x.Font = new XLFont(fontName, (float)fontSize, bold, italic);
}
}
// format
if (!string.IsNullOrEmpty(s.Format))
{
x.Format = XLStyle.FormatDotNetToXL(s.Format);
}
// borders
if (s.CellBorderThickness.Left > 0 && s.CellBorderBrushLeft is SolidColorBrush)
{
x.BorderLeft = GetBorderLineStyle(s.CellBorderThickness.Left);
x.BorderColorLeft = ((SolidColorBrush)s.CellBorderBrushLeft).Color;
}
if (s.CellBorderThickness.Top > 0 && s.CellBorderBrushTop is SolidColorBrush)
{
x.BorderTop = GetBorderLineStyle(s.CellBorderThickness.Top);
x.BorderColorTop = ((SolidColorBrush)s.CellBorderBrushTop).Color;
}
if (s.CellBorderThickness.Right > 0 && s.CellBorderBrushRight is SolidColorBrush)
{
x.BorderRight = GetBorderLineStyle(s.CellBorderThickness.Right);
x.BorderColorRight = ((SolidColorBrush)s.CellBorderBrushRight).Color;
}
if (s.CellBorderThickness.Bottom > 0 && s.CellBorderBrushBottom is SolidColorBrush)
{
x.BorderBottom = GetBorderLineStyle(s.CellBorderThickness.Bottom);
x.BorderColorBottom = ((SolidColorBrush)s.CellBorderBrushBottom).Color;
}
// save in cache and return
_excelStyles[s] = x;
return x;
}
private bool CheckDate(XLSheet sheet)
{
listBox1.Items.Clear();
bool HasNullRow = false;
listBox1.Items.Add("�п�����ʱ����ע��Դ�����ļ��������Ƿ���ȷ��");
for (int i = 0; i < sheet.Rows.Count; i++)
{
listBox1.Items.Add(" �е�" + i + "�п��������£�");
for (int j = 0; j < sheet.Columns.Count; j++)
{
if (IsNull(sheet[i, j].Value))
{
sheet[i, j].Value = string.Empty;
listBox1.Items.Add(" ����" + i + "�У���" + j + "�У��ǿ����ݣ�");
HasNullRow = true;
}
}
listBox1.Items.Add(" ����" + i + "�п����ݣ�");
listBox1.Items.Add(" ");
// return false;
}
if (HasNullRow)
{
return false;
}
else
{
return true;
}
}
/*
Sheet[i, 1] = "电路代号;
Sheet[i, 1] = "故障处理人员;
Sheet[i, 1] = "故障受理人员;
Sheet[i, 1] = "关键字;
Sheet[i, 1] = "恢复时间;
Sheet[i, 1] = "客户等级;
Sheet[i, 1] = "客户名称;
Sheet[i, 1] = "联系电话;
Sheet[i, 1] = "派障时间;
Sheet[i, 1] = "是否处理成功;
Sheet[i, 1] = "是否预约;
Sheet[i, 1] = "受理时间;
Sheet[i, 1] = "响应及时性;
Sheet[i, 1] = "障碍现象;
Sheet[i, 1] = "障碍详细地址;
Sheet[i, 1] = "障碍原因及处理结果;
Sheet[i, 1] = "中心编号;
Sheet[i, 1] = "总历时;
*/
private bool WriteDataToSheet(XLSheet Sheet)
{
if (dic故障管理 != null && dic故障管理.Count > 0)
{
Sheet[0, 0].Value = "中心编号";
Sheet[0, 1].Value = "电路代号";
Sheet[0, 2].Value = "故障处理人员";
Sheet[0, 3].Value = "故障受理人员";
Sheet[0, 4].Value = "总历时";
Sheet[0, 5].Value = "恢复时间";
Sheet[0, 6].Value = "客户等级";
Sheet[0, 7].Value = "客户名称";
Sheet[0, 8].Value = "联系电话";
Sheet[0, 9].Value = "派障时间";
Sheet[0, 10].Value = "是否处理成功";
Sheet[0, 11].Value = "是否预约";
Sheet[0, 12].Value = "受理时间";
Sheet[0, 13].Value = "响应及时性";
Sheet[0, 14].Value = "障碍现象";
Sheet[0, 15].Value = "障碍详细地址";
Sheet[0, 16].Value = "障碍原因及处理结果";
XLStyle DateTime = new XLStyle(Sheet.Book);
DateTime.Format = "YYYY-mm-DD HH:MM:SS";
int Index = 1;
foreach (int i in dic故障管理.Keys)
{
Sheet[Index, 0].Value = dic故障管理[i].BbasedateID;
Sheet[Index, 1].Value = dic故障管理[i].B电路代号;
Sheet[Index, 2].Value = dic故障管理[i].B故障处理人员;
Sheet[Index, 3].Value = dic故障管理[i].B故障受理人员;
Sheet[Index, 4].Value = dic故障管理[i].B总历时;
Sheet[Index, 5].Value = dic故障管理[i].B恢复时间;
Sheet[Index, 5].Style = DateTime;
Sheet[Index, 6].Value = dic故障管理[i].B客户等级;
Sheet[Index, 7].Value = dic故障管理[i].B客户名称;
Sheet[Index, 8].Value = dic故障管理[i].B联系电话;
Sheet[Index, 9].Value = dic故障管理[i].B派障时间;
Sheet[Index, 9].Style = DateTime;
Sheet[Index, 10].Value = dic故障管理[i].B是否处理成功;
Sheet[Index, 11].Value = dic故障管理[i].B是否预约;
Sheet[Index, 12].Value = dic故障管理[i].B受理时间;
Sheet[Index, 12].Style = DateTime;
Sheet[Index, 13].Value = dic故障管理[i].B响应及时性;
Sheet[Index, 14].Value = dic故障管理[i].B障碍现象;
Sheet[Index, 15].Value = dic故障管理[i].B障碍详细地址;
Sheet[Index, 16].Value = dic故障管理[i].B障碍原因及处理结果;
Index++;
}
}
else
{
MessageBox.Show("请先查询故障后，再进行导出！", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return true;
}
/// <summary>
/// ��������
/// </summary>
/// <param name="sheet"></param>
private void ImportDate(XLSheet sheet)
{
toolStripProgressBar1.Value = 0;
int count = sheet.Rows.Count;
int oneDate = count % toolStripProgressBar1.Maximum == 0 ? count / toolStripProgressBar1.Maximum : count / toolStripProgressBar1.Maximum + 1;
//int ProBarValue = 0;
//ds.Tables["E_Points"].Clear();
//Save();
try
{
for (int i = 1; i < count; i++)
{
if (sheet[i, 5].Value == null)
{
continue;
}
DataRow dr = ds.Tables["E_Points"].NewRow();
//����
if (ds.Tables["E_Points"].Rows.Contains(sheet[i, 5].Value.ToString()))
{
dr = ds.Tables["E_Points"].Rows.Find(sheet[i, 5].Value.ToString());
dr["PointName"] = sheet[i, 7].Value.ToString();
dr["AreaCode"] = 0;//sheet[i, 3].Value.ToString();//AreaCode
double temp = 0d;
if (Double.TryParse(sheet[i, 12].Value.ToString(), out temp))
{
dr["X"] = temp;
}
if (Double.TryParse(sheet[i, 13].Value.ToString(), out temp))
{
dr["Y"] = temp; ;
}
dr["FromTo"] = 0;
dr["Deleted"] = 0;
}
else //������
{
dr["PointCode"] = sheet[i, 5].Value.ToString();
dr["PointName"] = sheet[i, 7].Value.ToString();
dr["AreaCode"] = 0;
double temp = 0d;
if (Double.TryParse(sheet[i, 12].Value.ToString(), out temp))
{
dr["X"] = temp;
}
if (Double.TryParse(sheet[i, 13].Value.ToString(), out temp))
{
dr["Y"] = temp; ;
}
dr["FromTo"] = 0;
dr["Deleted"] = 0;
ds.Tables["E_Points"].Rows.Add(dr);
}
//sheet[i, 0].Value.ToString();
//sheet[i, 1].Value.ToString();
//sheet[i, 2].Value.ToString();
//sheet[i, 3].Value.ToString();
//sheet[i, 4].Value.ToString();
//sheet[i, 5].Value.ToString();
//sheet[i, 6].Value.ToString();
//sheet[i, 7].Value.ToString();
//sheet[i, 8].Value.ToString();
//sheet[i, 9].Value.ToString();
//sheet[i, 10].Value.ToString();
//sheet[i, 11].Value.ToString();
//sheet[i, 12].Value.ToString();
//sheet[i, 13].Value.ToString();
//sheet[i, 14].Value.ToString();
//sheet[i, 15].Value.ToString();
//sheet[i, 16].Value.ToString();
//sheet[i, 17].Value.ToString();
//sheet[i, 18].Value.ToString();
//sheet[i, 19].Value.ToString();
//sheet[i, 20].Value.ToString();
//sheet[i, 21].Value.ToString();
//sheet[i, 22].Value.ToString();
//sheet[i, 23].Value.ToString();
//sheet[i, 24].Value.ToString();
//sheet[i, 25].Value.ToString();
//sheet[i, 26].Value.ToString();
//sheet[i, 27].Value.ToString();
//sheet[i, 28].Value.ToString();
//sheet[i, 29].Value.ToString();
//sheet[i, 30].Value.ToString();
//sheet[i, 31].Value.ToString();
//sheet[i, 32].Value.ToString();
//sheet[i, 33].Value.ToString();
//sheet[i, 34].Value.ToString();
//sheet[i, 35].Value.ToString();
//sheet[i, 36].Value.ToString();
//sheet[i, 37].Value.ToString();
//sheet[i, 38].Value.ToString();
//sheet[i, 39].Value.ToString();
//sheet[i, 40].Value.ToString();
//sheet[i, 41].Value.ToString();
//sheet[i, 42].Value.ToString();
//sheet[i, 43].Value.ToString();
//sheet[i, 44].Value.ToString();
//sheet[i, 45].Value.ToString();
//sheet[i, 46].Value.ToString();
//sheet[i, 47].Value.ToString();
//sheet[i, 48].Value.ToString();
//sheet[i, 49].Value.ToString();
//sheet[i, 50].Value.ToString();
//sheet[i, 51].Value.ToString();
//sheet[i, 52].Value.ToString();
//sheet[i, 53].Value.ToString();
//sheet[i, 54].Value.ToString();
//sheet[i, 55].Value.ToString();
//sheet[i, 56].Value.ToString();
//sheet[i, 57].Value.ToString();
/*
*
0 ������
1 �����
2 ������
3 �Ϲ���
4 ���ÿ�����
5 ������������
6 ����
*/
//����������
if (count > toolStripProgressBar1.Maximum)
{
if (i % oneDate == 0)
{
toolStripProgressBar1.Value += 1;
}
}
else
{
toolStripProgressBar1.Value += toolStripProgressBar1.Maximum / count;
}
if (i == count)
{
toolStripProgressBar1.Value = 100;
}
// this.Text = toolStripProgressBar1.Value.ToString();
}
Save();
button2.Enabled = false;
SQLExecute.Operate = "�û�" + DBUser.CURR_USERID + "�����վ���ݡ�";
SQLExecute.SaveOperate();
MessageBox.Show("�������", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Save();
}
}
public void FullNewViewMultiBoth(string lsYearmonth)
{
string[] strTmpYearMonth = lsYearmonth.Split(',');
string maxYearMonth = "";
for (int l = 0; l < strTmpYearMonth.Length; l++)
{
string tmp = strTmpYearMonth[l];
if (maxYearMonth == "")
maxYearMonth = tmp;
if (maxYearMonth.CompareTo(tmp) < 0)
maxYearMonth = tmp;
}
if (String.IsNullOrEmpty(lsYearmonth))
{
mvMessage.AddError("Phải chọn ít nhất 1 tháng");
return;
}
int rBillNo = 0;
int cBillNo = 1;
int rBillDate = 0;
int cBillDate = 10;
int rBillMonth = 2;
int cBillMonth = 0;
int rContact = 5;
int cContact = 3;
int rCustomer = 5;
int cCustomer = 7;
int rContract = 7;
int cContract = 1;
//int rRate = 11;
//int cRate = 9;
//int rRateDate = 11;
//int cRateDate = 12;
int rRent = 15;
int rManager = 23;
int rParking = 31;
int rExtra = 39;
int rElec = 47;
int rWater = 55;
int rService = 63;
int rPaid = 70;
int rDept = 77;
int rOffice = 88;
int cOffice = 1;
int rPhone = 89;
int cPhone = 1;
int rBank = 88;
int cBank = 7;
int rAccountName = 89;
int cAccountName = 7;
int rAccount = 90;
int cAccount = 7;
int rSum = 81;
int cSum = 12;
int rSumVND = 80;
int cSumVND = 12;
int rSumRead = 82;
ArrayList removeRow = new ArrayList();
ArrayList hideRow = new ArrayList();
int check = DbHelper.GetCount("Select count(*) from PaymentBillInfo Where BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and CustomerId = '" + lblCustomerId.Text + "' and YearMonth = '" + drpYear.SelectedValue + drpMonth.SelectedValue + "'");
if (check == 0)
{
mvMessage.AddError("Xin vui lòng tạo hóa đơn trước khi xem");
return;
}
mvMessage.CheckRequired(txtBillDate, "Ngày xuất Hóa đơn là danh mục bắt buộc");
mvMessage.CheckRequired(txtBillNo, "Số Hóa đơn là danh mục bắt buộc");
mvMessage.CheckRequired(txtUsdExchange, "Tỉ giá USD-VN là danh mục bắt buộc");
mvMessage.CheckRequired(txtUsdExchangeDate, "Ngày tỉ giá là danh mục bắt buộc");
xlbBook = new C1XLBook();
string fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\NewBillTongQuat.xlsx");
if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])));
}
XLStyle xlstStyleLeftCH = new XLStyle(xlbBook);
xlstStyleLeftCH.AlignHorz = XLAlignHorzEnum.Left;
xlstStyleLeftCH.AlignVert = XLAlignVertEnum.Center;
xlstStyleLeftCH.Font = new Font("Times New Roman", 10, FontStyle.Bold);
xlstStyleLeftCH.SetBorderColor(Color.Black);
xlstStyleLeftCH.BorderTop = XLLineStyleEnum.Thin;
xlstStyleLeftCH.WordWrap = false;
XLStyle xlstStyleC = new XLStyle(xlbBook);
xlstStyleC.AlignHorz = XLAlignHorzEnum.Center;
xlstStyleC.AlignVert = XLAlignVertEnum.Center;
xlstStyleC.WordWrap = true;
xlstStyleC.Font = new Font("Times New Roman", 10, FontStyle.Regular);
xlstStyleC.SetBorderColor(Color.Black);
xlstStyleC.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleC.BorderTop = XLLineStyleEnum.Thin;
xlstStyleC.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleC.BorderRight = XLLineStyleEnum.Thin;
xlstStyleC.Format = "#,##0.00_);(#,##0.00)";
XLStyle xlstStyleC2 = new XLStyle(xlbBook);
xlstStyleC2.AlignHorz = XLAlignHorzEnum.Center;
xlstStyleC2.AlignVert = XLAlignVertEnum.Center;
xlstStyleC2.WordWrap = true;
xlstStyleC2.Font = new Font("Times New Roman", 10, FontStyle.Regular);
xlstStyleC2.SetBorderColor(Color.Black);
xlstStyleC2.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleC2.BorderTop = XLLineStyleEnum.Thin;
xlstStyleC2.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleC2.BorderRight = XLLineStyleEnum.Thin;
xlstStyleC2.Format = "#,##0.00_);(#,##0.00)";
XLStyle xlstStyleC1 = new XLStyle(xlbBook);
xlstStyleC1.AlignHorz = XLAlignHorzEnum.Center;
xlstStyleC1.AlignVert = XLAlignVertEnum.Center;
xlstStyleC1.WordWrap = true;
xlstStyleC1.Font = new Font("Times New Roman", 10, FontStyle.Regular);
xlstStyleC1.SetBorderColor(Color.Black);
xlstStyleC1.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleC1.BorderTop = XLLineStyleEnum.Thin;
xlstStyleC1.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleC1.BorderRight = XLLineStyleEnum.Thin;
xlstStyleC1.Format = "#,##0.0_);(#,##0.0)";
XLStyle xlstStyleC0 = new XLStyle(xlbBook);
xlstStyleC0.AlignHorz = XLAlignHorzEnum.Center;
xlstStyleC0.AlignVert = XLAlignVertEnum.Center;
xlstStyleC0.WordWrap = true;
xlstStyleC0.Font = new Font("Times New Roman", 10, FontStyle.Regular);
xlstStyleC0.SetBorderColor(Color.Black);
xlstStyleC0.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleC0.BorderTop = XLLineStyleEnum.Thin;
xlstStyleC0.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleC0.BorderRight = XLLineStyleEnum.Thin;
xlstStyleC0.Format = "#,##0_);(#,##0)";
XLStyle xlstStyleCH = new XLStyle(xlbBook);
xlstStyleCH.AlignHorz = XLAlignHorzEnum.Center;
xlstStyleCH.AlignVert = XLAlignVertEnum.Center;
xlstStyleCH.Font = new Font("Times New Roman", 10, FontStyle.Bold);
xlstStyleCH.SetBorderColor(Color.Black);
xlstStyleCH.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleCH.BorderTop = XLLineStyleEnum.Thin;
xlstStyleCH.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleCH.BorderRight = XLLineStyleEnum.Thin;
xlstStyleCH.WordWrap = true;
XLStyle xlstStyleCSum2 = new XLStyle(xlbBook);
xlstStyleCSum2.AlignHorz = XLAlignHorzEnum.Center;
xlstStyleCSum2.AlignVert = XLAlignVertEnum.Center;
xlstStyleCSum2.Font = new Font("Times New Roman", 10, FontStyle.Bold);
xlstStyleCSum2.SetBorderColor(Color.Black);
xlstStyleCSum2.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleCSum2.BorderTop = XLLineStyleEnum.Thin;
xlstStyleCSum2.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleCSum2.BorderRight = XLLineStyleEnum.Thin;
xlstStyleCSum2.WordWrap = true;
xlstStyleCSum2.Format = "#,##0.00_);(#,##0.00)";
XLStyle xlstStyleCSum1 = new XLStyle(xlbBook);
xlstStyleCSum1.AlignHorz = XLAlignHorzEnum.Center;
xlstStyleCSum1.AlignVert = XLAlignVertEnum.Center;
xlstStyleCSum1.Font = new Font("Times New Roman", 10, FontStyle.Bold);
xlstStyleCSum1.SetBorderColor(Color.Black);
xlstStyleCSum1.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleCSum1.BorderTop = XLLineStyleEnum.Thin;
xlstStyleCSum1.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleCSum1.BorderRight = XLLineStyleEnum.Thin;
xlstStyleCSum1.WordWrap = true;
xlstStyleCSum1.Format = "#,##0_);(#,##0)";
XLStyle xlstStyleCSum0 = new XLStyle(xlbBook);
xlstStyleCSum0.AlignHorz = XLAlignHorzEnum.Center;
xlstStyleCSum0.AlignVert = XLAlignVertEnum.Center;
xlstStyleCSum0.Font = new Font("Times New Roman", 10, FontStyle.Bold);
xlstStyleCSum0.SetBorderColor(Color.Black);
xlstStyleCSum0.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleCSum0.BorderTop = XLLineStyleEnum.Thin;
xlstStyleCSum0.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleCSum0.BorderRight = XLLineStyleEnum.Thin;
xlstStyleCSum0.WordWrap = true;
xlstStyleCSum0.Format = "#,##0_);(#,##0)";
XLStyle xlstStyle = new XLStyle(xlbBook);
xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
xlstStyle.AlignVert = XLAlignVertEnum.Center;
xlstStyle.WordWrap = true;
xlstStyle.Font = new Font("Times New Roman", 10, FontStyle.Regular);
xlstStyle.SetBorderColor(Color.Black);
xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
xlstStyle.BorderTop = XLLineStyleEnum.Thin;
xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
xlstStyle.BorderRight = XLLineStyleEnum.Thin;
xlstStyle.Format = "#,##0.00_);(#,##0.00)";
XLStyle xlstStyle2 = new XLStyle(xlbBook);
xlstStyle2.AlignHorz = XLAlignHorzEnum.Right;
xlstStyle2.AlignVert = XLAlignVertEnum.Center;
xlstStyle2.WordWrap = true;
xlstStyle2.Font = new Font("Times New Roman", 10, FontStyle.Regular);
xlstStyle2.SetBorderColor(Color.Black);
xlstStyle2.BorderBottom = XLLineStyleEnum.Thin;
xlstStyle2.BorderTop = XLLineStyleEnum.Thin;
xlstStyle2.BorderLeft = XLLineStyleEnum.Thin;
xlstStyle2.BorderRight = XLLineStyleEnum.Thin;
xlstStyle2.Format = "#,##0.00_);(#,##0.00)";
XLStyle xlstStyle1 = new XLStyle(xlbBook);
xlstStyle1.AlignHorz = XLAlignHorzEnum.Right;
xlstStyle1.AlignVert = XLAlignVertEnum.Center;
xlstStyle1.WordWrap = true;
xlstStyle1.Font = new Font("Times New Roman", 10, FontStyle.Regular);
xlstStyle1.SetBorderColor(Color.Black);
xlstStyle1.BorderBottom = XLLineStyleEnum.Thin;
xlstStyle1.BorderTop = XLLineStyleEnum.Thin;
xlstStyle1.BorderLeft = XLLineStyleEnum.Thin;
xlstStyle1.BorderRight = XLLineStyleEnum.Thin;
xlstStyle1.Format = "#,##0.0_);(#,##0.0)";
XLStyle xlstStyle0 = new XLStyle(xlbBook);
xlstStyle0.AlignHorz = XLAlignHorzEnum.Right;
xlstStyle0.AlignVert = XLAlignVertEnum.Center;
xlstStyle0.WordWrap = true;
xlstStyle0.Font = new Font("Times New Roman", 10, FontStyle.Regular);
xlstStyle0.SetBorderColor(Color.Black);
xlstStyle0.BorderBottom = XLLineStyleEnum.Thin;
xlstStyle0.BorderTop = XLLineStyleEnum.Thin;
xlstStyle0.BorderLeft = XLLineStyleEnum.Thin;
xlstStyle0.BorderRight = XLLineStyleEnum.Thin;
xlstStyle0.Format = "#,##0_);(#,##0)";
XLStyle xlstStyleH = new XLStyle(xlbBook);
xlstStyleH.AlignHorz = XLAlignHorzEnum.Center;
xlstStyleH.AlignVert = XLAlignVertEnum.Center;
xlstStyleH.Font = new Font("Times New Roman", 10, FontStyle.Bold);
xlstStyleH.SetBorderColor(Color.Black);
xlstStyleH.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleH.BorderTop = XLLineStyleEnum.Thin;
xlstStyleH.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleH.BorderRight = XLLineStyleEnum.Thin;
xlstStyleH.WordWrap = true;
XLStyle xlstStyleSum2 = new XLStyle(xlbBook);
xlstStyleSum2.AlignHorz = XLAlignHorzEnum.Right;
xlstStyleSum2.AlignVert = XLAlignVertEnum.Center;
xlstStyleSum2.Font = new Font("Times New Roman", 10, FontStyle.Bold);
xlstStyleSum2.SetBorderColor(Color.Black);
xlstStyleSum2.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleSum2.BorderTop = XLLineStyleEnum.Thin;
xlstStyleSum2.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleSum2.BorderRight = XLLineStyleEnum.Thin;
xlstStyleSum2.WordWrap = true;
xlstStyleSum2.Format = "#,##0.00_);(#,##0.00)";
XLStyle xlstStyleSum1 = new XLStyle(xlbBook);
xlstStyleSum1.AlignHorz = XLAlignHorzEnum.Right;
xlstStyleSum1.AlignVert = XLAlignVertEnum.Center;
xlstStyleSum1.Font = new Font("Times New Roman", 10, FontStyle.Bold);
xlstStyleSum1.SetBorderColor(Color.Black);
xlstStyleSum1.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleSum1.BorderTop = XLLineStyleEnum.Thin;
xlstStyleSum1.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleSum1.BorderRight = XLLineStyleEnum.Thin;
xlstStyleSum1.WordWrap = true;
xlstStyleSum1.Format = "#,##0_);(#,##0)";
XLStyle xlstStyleSum0 = new XLStyle(xlbBook);
xlstStyleSum0.AlignHorz = XLAlignHorzEnum.Right;
xlstStyleSum0.AlignVert = XLAlignVertEnum.Center;
xlstStyleSum0.Font = new Font("Times New Roman", 10, FontStyle.Bold);
xlstStyleSum0.SetBorderColor(Color.Black);
xlstStyleSum0.BorderBottom = XLLineStyleEnum.Thin;
xlstStyleSum0.BorderTop = XLLineStyleEnum.Thin;
xlstStyleSum0.BorderLeft = XLLineStyleEnum.Thin;
xlstStyleSum0.BorderRight = XLLineStyleEnum.Thin;
xlstStyleSum0.WordWrap = true;
xlstStyleSum0.Format = "#,##0_);(#,##0)";
if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\Bill"))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\Bill"));
}
string strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
string strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\Bill\Bill_" + lblCustomerId.Text + "_" + strDT + ".xlsx";
string strFilePathExport = @"../../Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + @"/Bill/Bill_" + lblCustomerId.Text + "_" + strDT + ".xlsx";
string fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);
File.Copy(fileName, fileNameDes);
xlbBook.Load(fileNameDes);
xlsSheet = xlbBook.Sheets["TongHop"];
xlsSheetEn = xlbBook.Sheets["TongHop_En"];
//Bill No
setValReplace(rBillNo, cBillNo, "{%BILL_NO%}", txtBillNo.Text);
//Ngay Thang Nam
DateTime dtime = DateTime.Today;
setValReplace(rBillDate, cBillDate, "{%NGAY%}", dtime.ToString("dd"));
setValReplace(rBillDate, cBillDate, "{%THANG%}", dtime.ToString("MM"));
setValReplace(rBillDate, cBillDate, "{%NAM%}", dtime.ToString("yyyy"));
//Nam
setValReplace(rBillMonth, cBillMonth, "{%NAM_THANG%}", drpMonth.SelectedValue + "/" + drpYear.SelectedValue);
using (SqlDatabase db = new SqlDatabase())
{
DataSet ds = new DataSet();
string sql = string.Empty;
sql = " SELECT Name, ContactName";
sql += " FROM Customer";
sql += " WHERE CustomerId = '" + lblCustomerId.Text + "' ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
string Name = rowType[0].ToString();
string ContactName = rowType[1].ToString();
//Customer
setValReplace(rCustomer, cCustomer, "{%TEN_CONG_TY%}", Name);
//Contact
setValReplace(rContact, cContact, "{%NGUOI_DAI_DIEN%}", ContactName);
}
}
}
Hashtable contractIdLst = new Hashtable();
string contract = "";
ds = new DataSet();
sql = " SELECT Bank,Account,AccountName,Office,OfficeAddress,OfficePhone";
sql += " FROM Mst_Building";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
if (ds != null)
{
DataTable dt = ds.Tables[0];
foreach (DataRow rowType in dt.Rows)
{
string Bank = rowType["Bank"].ToString();
string Account = rowType["Account"].ToString();
string AccountName = rowType["AccountName"].ToString();
string Office = rowType["Office"].ToString();
string OfficeAddress = rowType["OfficeAddress"].ToString();
string OfficePhone = rowType["OfficePhone"].ToString();
setVal(rOffice, 2, Office);
setVal(rPhone, 2, OfficePhone);
setVal(rBank, 10, Bank);
setVal(rAccountName, 10, AccountName);
setVal(rAccount, 10, Account);
}
}
}
//setVal(rRate, cRate, xlsSheet[rRate, cRate].Value.ToString().Replace("{%TI_GIA%}", Func.FormatNumber_New(txtUsdExchange.Text)));
//setVal(rRateDate, cRateDate, xlsSheet[rRateDate, cRateDate].Value.ToString().Replace("{%NGAY_AP_DUNG%}", txtUsdExchangeDate.Text));
//Thue phong
ds = new DataSet();
sql = " Select A.*, B.ContractDate";
sql += " FROM PaymentRoom A, RentContract B";
sql += " WHERE A.ContractId = B.ContractId and A.BuildingId = B.BuildingId and A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and A.CustomerId = '" + hidId.Value + "' and A.YearMonth in (" + lsYearmonth + ")";
sql += " and A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and A.CustomerId = '" + hidId.Value + "' and A.YearMonth in (" + lsYearmonth + ")";
int sumRow = 0;
int j = 0;
decimal[] LastSumPriceVND = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
decimal[] LastSumPriceUSD = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
decimal PaidPriceVND = 0;
decimal PaidPriceUSD = 0;
int viewNumber = 1;
string strSum = "";
int line = 0;
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rRent - 3 + j;
removeRow.Add(line + 2);
mergeCell(line, line + 1, 1, 3);
mergeCell(line, line, 4, 5);
mergeCell(line + 1, line + 1, 4, 5);
mergeCell(line + 2, line + 2, 4, 5);
mergeCell(line, line, 6, 7);
mergeCell(line + 1, line + 1, 6, 7);
mergeCell(line, line, 8, 9);
mergeCell(line + 1, line + 1, 8, 9);
mergeCell(line, line, 10, 11);
mergeCell(line + 1, line + 1, 10, 11);
mergeCell(line, line, 12, 13);
mergeCell(line + 1, line + 1, 12, 13);
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
int k = 0;
if (dt.Rows.Count <= 0)
{
for (int rHideLine = 0; rHideLine < 13; rHideLine++)
{
//setHideRow(rHideLine + line - 1);
}
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
}
else
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
foreach (DataRow rowType in dt.Rows)
{
decimal tmp02 = Convert.ToDecimal(rowType["LastRentSumVND"]);
string ContractId = Func.ParseString(rowType["ContractId"]);
string ContractNo = Func.ParseString(rowType["ContractNo"]);
string YearMonth = Func.ParseString(rowType["YearMonth"]);
string Area = Func.ParseString(rowType["Area"]);
string Name = Func.ParseString(rowType["Name"]);
string Regional = Func.ParseString(rowType["Regional"]);
string Floor = Func.ParseString(rowType["Floor"]);
string BeginContract = Func.ParseString(rowType["ContractDate"]);
if (!contractIdLst.ContainsKey(ContractId + "(" + Func.FormatDMY(BeginContract) + ")"))
{
contractIdLst.Add(ContractId + "(" + Func.FormatDMY(BeginContract) + ")", ContractNo + "(" + Func.FormatDMY(BeginContract) + ")");
contract += ";" + ContractNo + "(" + Func.FormatDMY(BeginContract) + ")";
}
if (tmp02 > 0)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rRent + 1 + j);
xlsSheetEn.Rows.Insert(rRent + 1 + j);
j++;
}
count++;
int tmp = rRent + j;
setVal(tmp, 1, YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4));
setVal(tmp, 4, rowType["Area"]);
setVal(tmp, 6, rowType["MonthRentPriceVND"]);
setVal(tmp, 8, rowType["MonthRentSumVND"]);
setVal(tmp, 10, rowType["VatRentPriceVND"]);
setVal(tmp, 12, rowType["LastRentSumVND"]);
mergeCell(tmp, tmp, 1, 3);
mergeCell(tmp, tmp, 4, 5);
mergeCell(tmp, tmp, 6, 7);
mergeCell(tmp, tmp, 8, 9);
mergeCell(tmp, tmp, 10, 11);
mergeCell(tmp, tmp, 12, 13);
mergeCell(tmp, tmp, 1, 3);
mergeCell(tmp, tmp, 4, 5);
for (int col = 1; col <= 13; col++)
{
setStyle(tmp, col, xlstStyle);
}
setStyle(tmp, 4, xlstStyleC1);
setStyle(tmp, 6, xlstStyle0);
setStyle(tmp, 8, xlstStyle0);
setStyle(tmp, 10, xlstStyle0);
setStyle(tmp, 12, xlstStyle0);
LastSumPriceVND[0] += Convert.ToDecimal(rowType["LastRentSumVND"]);
}
else
{
k++;
}
}
mergeCell(rRent + 1 + j, rRent + 1 + j, 1, 11);
setVal(rRent + 1 + j, 12, LastSumPriceVND[0]);
mergeCell(rRent + 1 + j, rRent + 1 + j, 12, 13);
//Format
setStyle(rRent + 1 + j, 12, xlstStyleSum1);
setStyle(rRent + 1 + j, 13, xlstStyleSum0);
sumRow += dt.Rows.Count - 1 - k;
//Phi quan ly
line = rManager - 3 + j;
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
removeRow.Add(line + 2);
mergeCell(line, line + 1, 1, 3);
mergeCell(line, line, 4, 5);
mergeCell(line + 1, line + 1, 4, 5);
mergeCell(line + 2, line + 2, 4, 5);
mergeCell(line, line, 6, 7);
mergeCell(line + 1, line + 1, 6, 7);
mergeCell(line, line, 8, 9);
mergeCell(line + 1, line + 1, 8, 9);
mergeCell(line, line, 10, 11);
mergeCell(line + 1, line + 1, 10, 11);
mergeCell(line, line, 12, 13);
mergeCell(line + 1, line + 1, 12, 13);
////En
k = 0;
count = 0;
foreach (DataRow row in dt.Rows)
{
decimal tmp02 = Convert.ToDecimal(row["LastManagerSumVND"]);
if (tmp02 > 0)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rManager + 1 + j);
xlsSheetEn.Rows.Insert(rManager + 1 + j);
j++;
}
count++;
int tmp = rManager + j;
string YearMonth = Func.ParseString(row["YearMonth"]);
string Area = Func.ParseString(row["Area"]);
string Name = Func.ParseString(row["Name"]);
setVal(tmp, 1, YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4));
setVal(tmp, 4, row["Area"]);
setVal(tmp, 6, row["MonthManagerPriceVND"]);
setVal(tmp, 8, row["MonthManagerSumVND"]);
setVal(tmp, 10, row["VatManagerPriceVND"]);
setVal(tmp, 12, row["LastManagerSumVND"]);
mergeCell(tmp, tmp, 1, 3);
mergeCell(tmp, tmp, 4, 5);
for (int col = 1; col <= 13; col++)
{
setStyle(tmp, col, xlstStyle);
}
setStyle(tmp, 4, xlstStyleC1);
setStyle(tmp, 6, xlstStyle0);
setStyle(tmp, 8, xlstStyle0);
setStyle(tmp, 10, xlstStyle0);
setStyle(tmp, 12, xlstStyle0);
mergeCell(tmp, tmp, 1, 3);
mergeCell(tmp, tmp, 4, 5);
mergeCell(tmp, tmp, 6, 7);
mergeCell(tmp, tmp, 8, 9);
mergeCell(tmp, tmp, 10, 11);
mergeCell(tmp, tmp, 12, 13);
LastSumPriceVND[1] += Convert.ToDecimal(row["LastManagerSumVND"]);
}
else { k++; }
}
mergeCell(rManager + 1 + j, rManager + 1 + j, 1, 11);
setVal(rManager + 1 + j, 12, LastSumPriceVND[1]);
mergeCell(rManager + 1 + j, rManager + 1 + j, 12, 13);
setStyle(rManager + 1 + j, 12, xlstStyleSum0);
sumRow += dt.Rows.Count - 1 - k;
}
}
else
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
for (int rRentLine = 0; rRentLine < 15; rRentLine++)
{
//setHideRow(rRentLine + rRent - 4 + line);
}
}
}
ds = new DataSet();
//Xuất ra toàn bộ nội dung theo Trang
sql = " SELECT COUNT(*) AS Num, YearMonth, TariffsParkingName, PriceVND, PriceUSD, SUM(VatVND) AS VatVND,SUM(VatUSD) AS VatUSD, SUM(SumVND) AS SumVND, SUM(SumUSD) AS SumUSD, SUM(LastPriceVND) AS LastPriceVND";
sql += " , SUM(LastPriceUSD) AS LastPriceUSD";
sql += " FROM dbo.PaymentParking";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and CustomerId = '" + hidId.Value + "' and YearMonth in (" + lsYearmonth + ")";
sql += " GROUP BY YearMonth, TariffsParkingName, PriceVND, PriceUSD, Vat, daysParking";
sql += " HAVING (SUM(LastPriceVND) >0 or SUM(LastPriceUSD) >0)";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rParking - 3 + j;
removeRow.Add(line + 2);
mergeCell(line, line + 1, 1, 3);
mergeCell(line, line, 4, 5);
mergeCell(line + 1, line + 1, 4, 5);
mergeCell(line + 2, line + 2, 4, 5);
mergeCell(line, line, 6, 7);
mergeCell(line + 1, line + 1, 6, 7);
mergeCell(line, line, 8, 9);
mergeCell(line + 1, line + 1, 8, 9);
mergeCell(line, line, 10, 11);
mergeCell(line + 1, line + 1, 10, 11);
mergeCell(line, line, 12, 13);
mergeCell(line + 1, line + 1, 12, 13);
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
foreach (DataRow row in dt.Rows)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rParking + 1 + j);
xlsSheetEn.Rows.Insert(rParking + 1 + j);
j++;
}
count++;
int tmp = rParking + j;
string Num = Func.ParseString(row["Num"]);
string TariffsParkingName = Func.ParseString(row["TariffsParkingName"]);
// Phi gui xe
setVal(tmp, 1, TariffsParkingName);
setVal(tmp, 4, Num);
setVal(tmp, 6, row["PriceVND"]);
setVal(tmp, 8, row["SumVND"]);
setVal(tmp, 10, row["VatVND"]);
setVal(tmp, 12, row["LastPriceVND"]);
mergeCell(tmp, tmp, 1, 3);
mergeCell(tmp, tmp, 4, 5);
for (int col = 1; col <= 13; col++)
{
setStyle(tmp, col, xlstStyle);
}
setStyle(tmp, 4, xlstStyleC1);
setStyle(tmp, 5, xlstStyleC1);
setStyle(tmp, 6, xlstStyle0);
setStyle(tmp, 8, xlstStyle0);
setStyle(tmp, 10, xlstStyle0);
setStyle(tmp, 12, xlstStyle0);
mergeCell(tmp, tmp, 1, 3);
mergeCell(tmp, tmp, 4, 5);
mergeCell(tmp, tmp, 6, 7);
mergeCell(tmp, tmp, 8, 9);
mergeCell(tmp, tmp, 10, 11);
mergeCell(tmp, tmp, 12, 13);
LastSumPriceVND[2] += Convert.ToDecimal(row["LastPriceVND"]);
}
setVal(rParking + 1 + j, 12, LastSumPriceVND[2]);
mergeCell(rParking + 1 + j, rParking + 1 + j, 1, 11);
mergeCell(rParking + 1 + j, rParking + 1 + j, 12, 13);
setStyle(rParking + 1 + j, 12, xlstStyleSum0);
mergeCell(rParking + 1 + j, rParking + 1 + j, 1, 11);
sumRow += dt.Rows.Count - 1;
}
else
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
for (int rHideLine = 0; rHideLine < 6; rHideLine++)
{
//setHideRow(rHideLine + line - 1);
}
}
}
}
ds = new DataSet();
sql = "SELECT id";
sql += " ,YearMonth,BuildingId,CustomerId,RoomId,ExtraHour,VAT,OtherFee01,OtherFee02";
sql += " ,PriceUSD,PriceVND,VatUSD,VatVND,SumUSD,SumVND,IsNull(LastPriceUSD,0) LastPriceUSD ,IsNull(LastPriceVND,0) LastPriceVND ";
sql += " ,RentArea,dbo.fnDateTime(FromWD) BeginDate,dbo.fnDateTime(EndWD) EndDate,ExtratimeType";
sql += " FROM PaymentExtraTimeMonth";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and CustomerId = '" + hidId.Value + "' and YearMonth in (" + lsYearmonth + ")";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rExtra - 3 + j;
removeRow.Add(line + 2);
//Phi ngoai gio
mergeCell(line, line + 1, 1, 2);
mergeCell(line, line + 1, 4, 4);
mergeCell(line, line, 6, 7);
mergeCell(line + 1, line + 1, 6, 7);
mergeCell(line, line, 8, 9);
mergeCell(line + 1, line + 1, 8, 9);
mergeCell(line, line, 10, 11);
mergeCell(line + 1, line + 1, 10, 11);
mergeCell(line, line, 12, 13);
mergeCell(line + 1, line + 1, 12, 13);
/////En
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
foreach (DataRow row in dt.Rows)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rExtra + 1 + j);
xlsSheetEn.Rows.Insert(rExtra + 1 + j);
j++;
}
count++;
int tmp = rExtra + j;
string ExtraHour = Func.ParseString(row["ExtraHour"]);
string BeginDate = Func.ParseString(row["BeginDate"]);
string EndDate = Func.ParseString(row["EndDate"]);
string ExtratimeType = Func.ParseString(row["ExtratimeType"]);
setVal(tmp, 1, BeginDate + "~" + EndDate);
setVal(tmp, 3, row["RentArea"]);
setVal(tmp, 5, ExtraHour);
setVal(tmp, 4, "Diện tích");
if ("0".Equals(ExtratimeType))
{
setVal(tmp, 4, "m2*h");
}
setVal(tmp, 6, row["PriceVND"]);
setVal(tmp, 8, row["SumVND"]);
setVal(tmp, 10, row["VatVND"]);
setVal(tmp, 12, row["LastPriceVND"]);
LastSumPriceVND[3] += Convert.ToDecimal(row["LastPriceVND"]);
mergeCell(tmp, tmp, 1, 2);
for (int col = 1; col <= 13; col++)
{
setStyle(tmp, col, xlstStyle);
}
/////////////////////
setStyle(tmp, 4, xlstStyleC1);
setStyle(tmp, 5, xlstStyleC1);
setStyle(tmp, 6, xlstStyle0);
setStyle(tmp, 7, xlstStyle0);
setStyle(tmp, 8, xlstStyle0);
setStyle(tmp, 9, xlstStyle0);
setStyle(tmp, 10, xlstStyle0);
setStyle(tmp, 11, xlstStyle0);
setStyle(tmp, 12, xlstStyle0);
setStyle(tmp, 13, xlstStyle0);
mergeCell(tmp, tmp, 1, 3);
mergeCell(tmp, tmp, 6, 7);
mergeCell(tmp, tmp, 8, 9);
mergeCell(tmp, tmp, 10, 11);
mergeCell(tmp, tmp, 12, 13);
}
}
else
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
for (int rHideLine = 0; rHideLine < 6; rHideLine++)
{
//setHideRow(rHideLine + line - 1);
}
}
mergeCell(rExtra + 1 + j, rExtra + 1 + j, 1, 11);
setVal(rExtra + 1 + j, 12, LastSumPriceVND[3]);
mergeCell(rExtra + 1 + j, rExtra + 1 + j, 12, 13);
setStyle(rExtra + 1 + j, 12, xlstStyleSum1);
setStyle(rExtra + 1 + j, 13, xlstStyleSum0);
sumRow += dt.Rows.Count - 1;
}
}
ds = new DataSet();
//Dien
//Xuất ra toàn bộ nội dung theo Trang
sql = " SELECT dbo.fnDateTime(A.DateFrom) DateFrom, dbo.fnDateTime(A.DateTo) DateTo, A.Vat, B.id, B.UsedElecWaterId, B.FromIndex, B.ToIndex, B.OtherFee01, B.OtherFee02, B.Mount, B.PriceVND, isnull(B.Mount,0) * isnull(B.PriceVND,0) as Sum, (isnull(B.Mount,0) * isnull(B.PriceVND,0))*isnull(A.Vat, 0)/100 as SumVAT ,B.PriceUSD, B.SumVND, B.SumUSD, ";
sql += " B.VatVND, B.VatUSD ,IsNull(B.LastPriceUSD,0) LastPriceUSD ,IsNull(B.LastPriceVND,0) LastPriceVND , B.Name, B.WaterPricePercent,B.ElecPricePercent ";
sql += " FROM PaymentElecWater AS A INNER JOIN ";
sql += " PaymentElecWaterDetail AS B ON A.UsedElecWaterId = B.UsedElecWaterId";
sql += " WHERE A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and A.CustomerId = '" + hidId.Value + "' and TarrifsOfWaterId = 0 and A.YearMonth in (" + lsYearmonth + ") and B.DetailType = 1";
sql += " Order by A.DateFrom, B.FromIndex";
bool rElecVat = true;
bool rWaterVat = true;
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rElec - 3 + j;
removeRow.Add(line + 2);
//Phi dien
mergeCell(line, line + 1, 1, 1);
mergeCell(line, line + 1, 2, 2);
mergeCell(line, line, 12, 13);
mergeCell(line + 1, line + 1, 12, 13);
/////En
for (int col = 1; col < 13; col++)
{
setStyle(line, col, xlstStyleH);
setStyle(line + 1, col, xlstStyleH);
setStyle(line + 2, col, xlstStyleH);
}
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
foreach (DataRow row in dt.Rows)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rElec + 1 + j);
xlsSheetEn.Rows.Insert(rElec + 1 + j);
j++;
}
count++;
int tmp = rElec + j;
string DateFrom = Func.ParseString(row["DateFrom"]);
string DateTo = Func.ParseString(row["DateTo"]);
string FromIndex = Func.ParseString(row["FromIndex"]);
string ToIndex = Func.ParseString(row["ToIndex"]);
string OtherFee01 = Func.ParseString(row["OtherFee01"]);
string OtherFee02 = Func.ParseString(row["OtherFee02"]);
string Mount = Func.ParseString(row["Mount"]);
string ElecPricePercent = Func.ParseString(row["ElecPricePercent"]);
string vat = Func.ParseString(row["vat"]);
if (rElecVat)
{
setValReplace(line, 9, "(%VAT)", "(" + vat.Replace(",00", "") + "%VAT)");
rElecVat = false;
}
setVal(tmp, 1, DateFrom);
setVal(tmp, 2, DateTo);
setVal(tmp, 3, FromIndex);
setVal(tmp, 4, ToIndex);
setVal(tmp, 5, row["OtherFee01"]);
setVal(tmp, 6, Mount);
setVal(tmp, 7, row["PriceVND"]);
setVal(tmp, 8, row["Sum"]);
setVal(tmp, 9, row["SumVAT"]);
setVal(tmp, 10, row["OtherFee02"]);
setVal(tmp, 11, row["ElecPricePercent"]);
setVal(tmp, 12, row["LastPriceVND"]);
mergeCell(tmp, tmp, 12, 13);
for (int col = 1; col <= 13; col++)
{
setStyle(tmp, col, xlstStyle);
}
/////////////////
setStyle(tmp, 3, xlstStyleC2);
setStyle(tmp, 4, xlstStyleC2);
setStyle(tmp, 5, xlstStyleC0);
setStyle(tmp, 6, xlstStyle2);
setStyle(tmp, 7, xlstStyle0);
setStyle(tmp, 8, xlstStyle0);
setStyle(tmp, 9, xlstStyle0);
setStyle(tmp, 10, xlstStyle0);
setStyle(tmp, 11, xlstStyle2);
setStyle(tmp, 12, xlstStyle0);
}
DataSet dsSum = new DataSet();
//Dien
//Xuất ra toàn bộ nội dung theo Trang
string sqlSum = " SELECT IsNull(A.LastPriceUSD,0) LastPriceUSD ,IsNull(A.LastPriceVND,0) LastPriceVND ";
sqlSum += " FROM PaymentElecWater AS A ";
sqlSum += " WHERE A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and A.CustomerId = '" + hidId.Value + "' and TarrifsOfElecId > 0 and A.YearMonth in (" + lsYearmonth + ") and A.DetailType = 1";
using (SqlCommand cmSum = db.CreateCommand(sqlSum))
{
SqlDataAdapter daSum = new SqlDataAdapter(cmSum);
daSum.Fill(dsSum);
if (daSum != null)
{
DataTable dtSum = dsSum.Tables[0];
if (dtSum.Rows.Count > 0)
{
foreach (DataRow row in dtSum.Rows)
{
LastSumPriceUSD[4] += Convert.ToDecimal(row["LastPriceUSD"]);
LastSumPriceVND[4] += Convert.ToDecimal(row["LastPriceVND"]);
}
}
}
}
///////////////////////////
setVal(rElec + 1 + j, 12, Decimal.Round(LastSumPriceVND[4], 0));
mergeCell(rElec + 1 + j, rElec + 1 + j, 1, 11);
mergeCell(rElec + 1 + j, rElec + 1 + j, 12, 13);
////
setStyle(rElec + 1 + j, 12, xlstStyleSum0);
mergeCell(rElec + 1 + j, rElec + 1 + j, 1, 11);
mergeCell(rElec + 1 + j, rElec + 1 + j, 12, 13);
sumRow += dt.Rows.Count - 1;
}
else
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
for (int rHideLine = 0; rHideLine < 6; rHideLine++)
{
//setHideRow(rHideLine + line - 1);
}
}
}
}
ds = new DataSet();
//Phi Nuoc
//Xuất ra toàn bộ nội dung theo Trang
sql = " SELECT dbo.fnDateTime(A.DateFrom) DateFrom, dbo.fnDateTime(A.DateTo) DateTo, A.Vat, B.id, B.UsedElecWaterId, B.FromIndex, B.ToIndex, B.OtherFee01, B.OtherFee02, B.Mount, B.PriceVND, isnull(B.Mount,0) * isnull(B.PriceVND,0) as Sum, (isnull(B.Mount,0) * isnull(B.PriceVND,0))*isnull(A.Vat, 0)/100 as SumVAT , B.PriceUSD, B.SumVND, B.SumUSD, ";
sql += " B.VatVND, B.VatUSD,IsNull(B.LastPriceUSD,0) LastPriceUSD,IsNull(B.LastPriceVND,0) LastPriceVND, B.Name, B.WaterPricePercent,B.ElecPricePercent ";
sql += " FROM PaymentElecWater AS A INNER JOIN ";
sql += " PaymentElecWaterDetail AS B ON A.UsedElecWaterId = B.UsedElecWaterId";
sql += " WHERE A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and A.CustomerId = '" + hidId.Value + "' and TarrifsOfElecId = 0 and A.YearMonth in (" + lsYearmonth + ") and B.DetailType = 2";
sql += " Order by A.DateFrom, B.FromIndex";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rWater - 3 + j;
removeRow.Add(line + 2);
//Phi nuoc
mergeCell(line, line + 1, 1, 1);
mergeCell(line, line + 1, 2, 2);
mergeCell(line, line, 12, 13);
mergeCell(line + 1, line + 1, 12, 13);
for (int col = 1; col < 13; col++)
{
setStyle(line, col, xlstStyleH);
setStyle(line + 1, col, xlstStyleH);
setStyle(line + 2, col, xlstStyleH);
}
for (int col = 1; col < 13; col++)
{
setStyle(line, col, xlstStyleH);
setStyle(line + 1, col, xlstStyleH);
setStyle(line + 2, col, xlstStyleH);
}
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
foreach (DataRow row in dt.Rows)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rWater + 1 + j);
xlsSheetEn.Rows.Insert(rWater + 1 + j);
j++;
}
count++;
int tmp = rWater + j;
string DateFrom = Func.ParseString(row["DateFrom"]);
string DateTo = Func.ParseString(row["DateTo"]);
string FromIndex = Func.ParseString(row["FromIndex"]);
string ToIndex = Func.ParseString(row["ToIndex"]);
string OtherFee01 = Func.ParseString(row["OtherFee01"]);
string OtherFee02 = Func.ParseString(row["OtherFee02"]);
string Mount = Func.ParseString(row["Mount"]);
string vat = Func.ParseString(row["vat"]);
if (rWaterVat)
{
setValReplace(line, 9, "(%VAT)", "(" + vat.Replace(",00", "") + "%VAT)");
rWaterVat = false;
}
setVal(tmp, 1, DateFrom);
setVal(tmp, 2, DateTo);
setVal(tmp, 3, FromIndex);
setVal(tmp, 4, ToIndex);
setVal(tmp, 5, Mount);
setVal(tmp, 6, row["PriceVND"]);
setVal(tmp, 8, row["OtherFee01"]);
setVal(tmp, 7, row["Sum"]);
setVal(tmp, 9, row["SumVAT"]);
setVal(tmp, 10, row["OtherFee02"]);
setVal(tmp, 11, row["WaterPricePercent"]);
setVal(tmp, 12, row["LastPriceVND"]);
for (int col = 1; col <= 13; col++)
{
setStyle(tmp, col, xlstStyle);
}
setStyle(tmp, 3, xlstStyleC2);
setStyle(tmp, 4, xlstStyleC2);
setStyle(tmp, 5, xlstStyleC2);
setStyle(tmp, 6, xlstStyle0);
setStyle(tmp, 7, xlstStyle0);
setStyle(tmp, 8, xlstStyle0);
setStyle(tmp, 9, xlstStyle0);
setStyle(tmp, 10, xlstStyle2);
setStyle(tmp, 11, xlstStyle2);
setStyle(tmp, 12, xlstStyle0);
mergeCell(tmp, tmp, 12, 13);
}
DataSet dsSum = new DataSet();
//Nuoc
//Xuất ra toàn bộ nội dung theo Trang
string sqlSum = " SELECT IsNull(A.LastPriceUSD,0) LastPriceUSD ,IsNull(A.LastPriceVND,0) LastPriceVND ";
sqlSum += " FROM PaymentElecWater AS A ";
sqlSum += " WHERE A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and A.CustomerId = '" + hidId.Value + "' and TarrifsOfWaterId > 0 and A.YearMonth in (" + lsYearmonth + ") and A.DetailType = 2";
using (SqlCommand cmSum = db.CreateCommand(sqlSum))
{
SqlDataAdapter daSum = new SqlDataAdapter(cmSum);
daSum.Fill(dsSum);
if (daSum != null)
{
DataTable dtSum = dsSum.Tables[0];
if (dtSum.Rows.Count > 0)
{
foreach (DataRow row in dtSum.Rows)
{
LastSumPriceVND[5] += Convert.ToDecimal(row["LastPriceVND"]);
}
}
}
}
setVal(rWater + 1 + j, 12, Decimal.Round(LastSumPriceVND[5], 0));
mergeCell(rWater + 1 + j, rWater + 1 + j, 1, 11);
mergeCell(rWater + 1 + j, rWater + 1 + j, 12, 13);
setStyle(rWater + 1 + j, 12, xlstStyleSum0);
sumRow += dt.Rows.Count - 1;
}
else
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
for (int rHideLine = 0; rHideLine < 6; rHideLine++)
{
//setHideRow(rHideLine + line - 1);
}
}
}
}
//Service
ds = new DataSet();
sql = string.Empty;
sql = " SELECT Service,dbo.fnDateTime(ServiceDateFrom) ServiceDateFrom,dbo.fnDateTime(ServiceDateTo) ServiceDateTo,PriceVND,PriceUSD,VatUSD,VatVND,Mount,Unit,SumVND,SumUSD,isnull(LastPriceVND,0) LastPriceVND,isnull(LastPriceUSD,0) LastPriceUSD";
sql += " FROM PaymentService";
sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and CustomerId = '" + hidId.Value + "' and YearMonth in (" + lsYearmonth + ")";
sql += " Order By ServiceDate ";
using (SqlCommand cm = db.CreateCommand(sql))
{
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
line = rService - 3 + j;
removeRow.Add(line + 2);
//Phi khác
mergeCell(line, line + 1, 1, 1);
mergeCell(line, line + 1, 2, 2);
mergeCell(line, line + 1, 3, 3);
mergeCell(line, line + 1, 4, 4);
for (int col = 1; col < 13; col++)
{
setStyle(line, col, xlstStyleH);
setStyle(line + 1, col, xlstStyleH);
setStyle(line + 2, col, xlstStyleH);
}
mergeCell(line, line, 12, 13);
mergeCell(line + 1, line + 1, 12, 13);
if (ds != null)
{
int count = 0;
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
foreach (DataRow row in dt.Rows)
{
if (count >= 1)
{
xlsSheet.Rows.Insert(rService + 1 + j);
xlsSheetEn.Rows.Insert(rService + 1 + j);
j++;
}
count++;
int tmp = rService + j;
string Service = Func.ParseString(row["Service"]);
string ServiceDateFrom = Func.ParseString(row["ServiceDateFrom"]);
string ServiceDateTo = Func.ParseString(row["ServiceDateTo"]);
string Mount = Func.ParseString(row["Mount"]);
setVal(tmp, 1, Service);
setVal(tmp, 2, Func.ParseString(row["Unit"]));
setVal(tmp, 3, ServiceDateFrom);
setVal(tmp, 4, ServiceDateTo);
setVal(tmp, 5, Mount);
setVal(tmp, 6, row["PriceVND"]);
setVal(tmp, 8, row["SumVND"]);
setVal(tmp, 10, row["VatVND"]);
setVal(tmp, 12, row["LastPriceVND"]);
mergeCell(tmp, tmp, 6, 7);
mergeCell(tmp, tmp, 8, 9);
mergeCell(tmp, tmp, 10, 11);
mergeCell(tmp, tmp, 12, 13);
for (int col = 1; col <= 13; col++)
{
setStyle(tmp, col, xlstStyle);
}
setStyle(tmp, 5, xlstStyleC2);
setStyle(tmp, 6, xlstStyle0);
setStyle(tmp, 8, xlstStyle0);
setStyle(tmp, 10, xlstStyle0);
setStyle(tmp, 12, xlstStyle0);
LastSumPriceVND[6] += Convert.ToDecimal(row["LastPriceVND"]);
}
setVal(rService + 1 + j, 12, LastSumPriceVND[6]);
setStyle(rService + 1 + j, 12, xlstStyleSum0);
mergeCell(rService + 1 + j, rService + 1 + j, 1, 11);
mergeCell(rService + 1 + j, rService + 1 + j, 12, 13);
sumRow += dt.Rows.Count - 1;
}
else
{
setVal(line - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
for (int rHideLine = 0; rHideLine < 6; rHideLine++)
{
//setHideRow(rHideLine + line - 1);
}
}
}
}
//Paid
sql = "Select PaymentType,isnull(MoneyUSD,0) MoneyUSD,isnull(MoneyVND,0) MoneyVND,isnull(PaidUSD,0) PaidUSD,isnull(PaidVND,0) PaidVND,isnull(ExchangeType,0) ExchangeType,isnull(UsdExchange,0) UsdExchange,isnull(YearMonth,0) YearMonth";
sql += " From PaymentBillDetail";
sql += " Where BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and CustomerId = '" + hidId.Value + "' and YearMonth in (" + lsYearmonth + ") and YearMonth <= " + maxYearMonth + "";
sql += " and isnull(PaidVND,0) > 0";
string strYearMonth = "";
int lineTmp = rPaid - 2 + j;
removeRow.Add(lineTmp + 1);
/////En
mergeCell(lineTmp, lineTmp + 1, 1, 1);
mergeCell(lineTmp, lineTmp, 2, 3);
mergeCell(lineTmp, lineTmp, 4, 5);
mergeCell(lineTmp, lineTmp, 6, 7);
mergeCell(lineTmp, lineTmp, 8, 9);
mergeCell(lineTmp, lineTmp, 12, 13);
/////En
Hashtable rowNo = new Hashtable();
decimal[] PaidSumVND = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
decimal[] PaidSumUSD = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
int iPaidNo = 0;
DataTable dtPaid = DbHelper.GetDataTable(sql);
if (dtPaid.Rows.Count > 0)
{
setVal(lineTmp - 1, 0, viewNumber + ".");
iPaidNo = viewNumber;
viewNumber++;
for (int i = 0; i < dtPaid.Rows.Count; i++)
{
string PaymentType = Func.ParseString(dtPaid.Rows[i]["PaymentType"]);
string MoneyVND = Func.ParseString(dtPaid.Rows[i]["MoneyVND"]);
string PaidVND = Func.ParseString(dtPaid.Rows[i]["PaidVND"]);
string ExchangeType = Func.ParseString(dtPaid.Rows[i]["ExchangeType"]);
string UsdExchange = Func.ParseString(dtPaid.Rows[i]["UsdExchange"]);
string YearMonth = Func.ParseString(dtPaid.Rows[i]["YearMonth"]);
if (!rowNo.Contains(YearMonth))
{
if (rowNo.Count != 0)
{
xlsSheet.Rows.Insert(rPaid + j + 1);
xlsSheetEn.Rows.Insert(rPaid + j + 1);
j++;
}
rowNo.Add(YearMonth, j);
}
int m = Func.ParseInt(rowNo[YearMonth]);
strYearMonth = YearMonth;
decimal tmpVND = Convert.ToDecimal(MoneyVND) - Convert.ToDecimal(PaidVND);
PaidPriceVND += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
setVal(rPaid + m, 1, YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4));
switch (PaymentType)
{
case "1":
//Rent
setVal(rPaid + m, 2, dtPaid.Rows[i]["PaidVND"]);
PaidSumVND[0] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "2":
//Manager
setVal(rPaid + m, 4, dtPaid.Rows[i]["PaidVND"]);
PaidSumVND[1] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "3":
//Parking
setVal(rPaid + m, 6, dtPaid.Rows[i]["PaidVND"]);
PaidSumVND[2] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "4":
//Extra
setVal(rPaid + m, 8, dtPaid.Rows[i]["PaidVND"]);
PaidSumVND[3] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "5":
setVal(rPaid + m, 10, dtPaid.Rows[i]["PaidVND"]);
PaidSumVND[4] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "6":
setVal(rPaid + m, 11, dtPaid.Rows[i]["PaidVND"]);
PaidSumVND[5] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
case "7":
setVal(rPaid + m, 12, dtPaid.Rows[i]["PaidVND"]);
PaidSumVND[6] += Convert.ToDecimal(dtPaid.Rows[i]["PaidVND"]);
break;
default:
break;
}
/////////////////////////////////////////////////
for (int col = 1; col <= 13; col++)
{
setStyle(rPaid + m, col, xlstStyle);
}
//Rent
setStyle(rPaid + m, 2, xlstStyle0);
setStyle(rPaid + m, 3, xlstStyle0);
//Manager
setStyle(rPaid + m, 4, xlstStyle0);
setStyle(rPaid + m, 5, xlstStyle0);
//Parking
setStyle(rPaid + m, 6, xlstStyle0);
setStyle(rPaid + m, 7, xlstStyle0);
//Extra
setStyle(rPaid + m, 8, xlstStyle0);
setStyle(rPaid + m, 9, xlstStyle0);
setStyle(rPaid + m, 10, xlstStyle0);
setStyle(rPaid + m, 11, xlstStyle0);
setStyle(rPaid + m, 12, xlstStyle0);
setStyle(rPaid + m, 13, xlstStyle0);
mergeCell(rPaid + m, rPaid + m, 2, 3);
mergeCell(rPaid + m, rPaid + m, 4, 5);
mergeCell(rPaid + m, rPaid + m, 6, 7);
mergeCell(rPaid + m, rPaid + m, 8, 9);
mergeCell(rPaid + m, rPaid + m, 12, 13);
}
}
else
{
setVal(lineTmp - 1, 0, viewNumber + ".");
iPaidNo = viewNumber;
viewNumber++;
for (int rHideLine = 0; rHideLine < 6; rHideLine++)
{
//setHideRow(rHideLine + lineTmp - 1);
}
}
lineTmp = rPaid - 2 + j;
setVal(lineTmp + 3, 2, PaidSumVND[0]);
setVal(lineTmp + 3, 4, PaidSumVND[1]);
setVal(lineTmp + 3, 6, PaidSumVND[2]);
setVal(lineTmp + 3, 8, PaidSumVND[3]);
setVal(lineTmp + 3, 10, PaidSumVND[4]);
setVal(lineTmp + 3, 11, PaidSumVND[5]);
setVal(lineTmp + 3, 12, PaidSumVND[6]);
mergeCell(lineTmp + 3, lineTmp + 3, 2, 3);
mergeCell(lineTmp + 3, lineTmp + 3, 4, 5);
mergeCell(lineTmp + 3, lineTmp + 3, 6, 7);
mergeCell(lineTmp + 3, lineTmp + 3, 8, 9);
mergeCell(lineTmp + 3, lineTmp + 3, 12, 13);
///////////////
setStyle(lineTmp + 3, 2, xlstStyleSum1);
setStyle(lineTmp + 3, 3, xlstStyleSum0);
setStyle(lineTmp + 3, 4, xlstStyleSum0);
setStyle(lineTmp + 3, 5, xlstStyleSum0);
setStyle(lineTmp + 3, 6, xlstStyleSum0);
setStyle(lineTmp + 3, 7, xlstStyleSum0);
setStyle(lineTmp + 3, 8, xlstStyleSum0);
setStyle(lineTmp + 3, 9, xlstStyleSum0);
setStyle(lineTmp + 3, 10, xlstStyleSum0);
setStyle(lineTmp + 3, 11, xlstStyleSum0);
setStyle(lineTmp + 3, 12, xlstStyleSum0);
setStyle(lineTmp + 3, 13, xlstStyleSum0);
///////////////DEPT
sql = " Select *";
sql += " From v_DeptBill";
sql += " Where BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and CustomerId = '" + hidId.Value + "' and YearMonth not in (" + lsYearmonth + ") and YearMonth < " + maxYearMonth + "";
sql += " And (DeptUsd <> 0 or DeptVnd <> 0)";
strYearMonth = "";
lineTmp = rDept - 2 + j;
removeRow.Add(lineTmp + 1);
//////En
mergeCell(lineTmp, lineTmp + 1, 1, 1);
mergeCell(lineTmp, lineTmp, 2, 3);
mergeCell(lineTmp, lineTmp, 4, 5);
mergeCell(lineTmp, lineTmp, 6, 7);
mergeCell(lineTmp, lineTmp, 8, 9);
mergeCell(lineTmp, lineTmp, 12, 13);
//////En
rowNo = new Hashtable();
decimal DeptPriceVND = 0;
decimal DeptPriceUSD = 0;
decimal[] DeptSumVND = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
decimal[] DeptSumUSD = new decimal[7] { 0, 0, 0, 0, 0, 0, 0 };
DataTable dtDept = DbHelper.GetDataTable(sql);
if (dtDept.Rows.Count > 0)
{
setVal(lineTmp - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
for (int i = 0; i < dtDept.Rows.Count; i++)
{
string PaymentType = Func.ParseString(dtDept.Rows[i]["PaymentType"]);
string DeptVND = Func.ParseString(dtDept.Rows[i]["DeptVND"]);
string YearMonth = Func.ParseString(dtDept.Rows[i]["YearMonth"]);
if (!rowNo.Contains(YearMonth))
{
if (rowNo.Count != 0)
{
xlsSheet.Rows.Insert(rDept + j + 1);
xlsSheetEn.Rows.Insert(rDept + j + 1);
j++;
}
rowNo.Add(YearMonth, j);
}
int m = Func.ParseInt(rowNo[YearMonth]);
strYearMonth = YearMonth;
DeptPriceVND += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
setVal(rDept + m, 1, YearMonth.Substring(4, 2) + "/" + YearMonth.Substring(0, 4));
switch (PaymentType)
{
case "1":
//Rent
setVal(rDept + m, 3, dtDept.Rows[i]["DeptVND"]);
DeptSumVND[0] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "2":
//Manager
setVal(rDept + m, 5, dtDept.Rows[i]["DeptVND"]);
DeptSumVND[1] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "3":
//Parking
setVal(rDept + m, 7, dtDept.Rows[i]["DeptVND"]);
DeptSumVND[2] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "4":
//Extra
setVal(rDept + m, 9, dtDept.Rows[i]["DeptVND"]);
DeptSumVND[3] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "5":
setVal(rDept + m, 10, dtDept.Rows[i]["DeptVND"]);
DeptSumVND[4] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "6":
setVal(rDept + m, 11, dtDept.Rows[i]["DeptVND"]);
DeptSumVND[5] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
case "7":
setVal(rDept + m, 13, dtDept.Rows[i]["DeptVND"]);
DeptSumVND[6] += Convert.ToDecimal(dtDept.Rows[i]["DeptVND"]);
break;
default:
break;
}
for (int col = 1; col <= 13; col++)
{
setStyle(rDept + m, col, xlstStyle);
}
setStyle(rDept + m, 2, xlstStyle1);
setStyle(rDept + m, 3, xlstStyle0);
//Manager
setStyle(rDept + m, 4, xlstStyle1);
setStyle(rDept + m, 5, xlstStyle0);
//Parking
setStyle(rDept + m, 6, xlstStyle1);
setStyle(rDept + m, 7, xlstStyle0);
//Extra
setStyle(rDept + m, 8, xlstStyle1);
setStyle(rDept + m, 9, xlstStyle0);
setStyle(rDept + m, 10, xlstStyle0);
setStyle(rDept + m, 11, xlstStyle0);
setStyle(rDept + m, 12, xlstStyle1);
setStyle(rDept + m, 13, xlstStyle0);
mergeCell(rDept + m, rDept + m, 2, 3);
mergeCell(rDept + m, rDept + m, 4, 5);
mergeCell(rDept + m, rDept + m, 6, 7);
mergeCell(rDept + m, rDept + m, 8, 9);
mergeCell(rDept + m, rDept + m, 10, 11);
mergeCell(rDept + m, rDept + m, 12, 13);
}
}
else
{
setVal(lineTmp - 1, 0, viewNumber + ".");
strSum += viewNumber + " + ";
viewNumber++;
for (int rHideLine = 0; rHideLine < 5; rHideLine++)
{
//setHideRow(rHideLine + lineTmp - 1);
}
}
lineTmp = rDept - 2 + j;
setVal(lineTmp + 3, 3, DeptSumVND[0]);
setVal(lineTmp + 3, 5, DeptSumVND[1]);
setVal(lineTmp + 3, 7, DeptSumVND[2]);
setVal(lineTmp + 3, 9, DeptSumVND[3]);
setVal(lineTmp + 3, 10, DeptSumVND[4]);
setVal(lineTmp + 3, 11, DeptSumVND[5]);
setVal(lineTmp + 3, 13, DeptSumVND[6]);
setStyle(lineTmp + 3, 2, xlstStyleSum1);
setStyle(lineTmp + 3, 3, xlstStyleSum0);
setStyle(lineTmp + 3, 4, xlstStyleSum0);
setStyle(lineTmp + 3, 5, xlstStyleSum0);
setStyle(lineTmp + 3, 6, xlstStyleSum0);
setStyle(lineTmp + 3, 7, xlstStyleSum0);
setStyle(lineTmp + 3, 8, xlstStyleSum0);
setStyle(lineTmp + 3, 9, xlstStyleSum0);
setStyle(lineTmp + 3, 10, xlstStyleSum0);
setStyle(lineTmp + 3, 11, xlstStyleSum0);
setStyle(lineTmp + 3, 12, xlstStyleSum0);
setStyle(lineTmp + 3, 13, xlstStyleSum0);
mergeCell(lineTmp + 3, lineTmp + 3, 2, 3);
mergeCell(lineTmp + 3, lineTmp + 3, 4, 5);
mergeCell(lineTmp + 3, lineTmp + 3, 6, 7);
mergeCell(lineTmp + 3, lineTmp + 3, 8, 9);
mergeCell(lineTmp + 3, lineTmp + 3, 10, 11);
mergeCell(lineTmp + 3, lineTmp + 3, 12, 13);
strSum = strSum.Substring(0, strSum.Length - 2) + (iPaidNo > 0 ? " - " + iPaidNo : "") + ":";
decimal AllSumVND = 0;
decimal AllSumUSD = 0;
for (int i = 0; i < 7; i++)
{
AllSumVND += LastSumPriceVND[i];
AllSumUSD += LastSumPriceUSD[i];
}
AllSumVND -= PaidPriceVND;
AllSumUSD -= PaidPriceUSD;
AllSumVND += DeptPriceVND;
AllSumUSD += DeptPriceUSD;
//setVal(rSumVND + j, cSumVND - 2, Func.FormatNumber_New(AllSumUSD));
setVal(rSumVND + j, cSumVND, Func.FormatNumber_New(Math.Truncate(Convert.ToDecimal(AllSumVND))).Replace(",00", ""));
mergeCell(rSumVND + j, rSumVND + j, 10, 13);
//format
//setStyle(rSumVND + j, cSumVND - 2, xlstStyleCH);
setStyle(rSumVND + j, cSumVND, xlstStyleCH);
//AllSumVND += Convert.ToDecimal(AllSumUSD * Convert.ToDecimal(txtUsdExchange.Text));
string strMoney = Func.docso(Convert.ToDecimal(AllSumVND));
string strMoneyEn = Func.DocSo_En(Convert.ToDecimal(AllSumVND));
//Hop Dong
setValReplace(rContract, cContract, "{%HOP_DONG%}", String.IsNullOrEmpty(contract) ? "" : contract.Substring(1));
//Sum 1 + 2...
setVal(rSum + j - 1, 7, strSum);
//Sum số tiền
setVal(rSum + j - 1, 10, Func.FormatNumber_New(Math.Truncate(Convert.ToDecimal(AllSumVND))).Replace(",00", ""));
mergeCell(rSum + j - 1, rSum + j - 1, 10, 13);
setStyle(rSum + j - 1, 10, xlstStyleCSum0);
//Chữ
//mergeCell(rSumRead + j, rSumRead + j, 7, 13);
setVal(rSum + j + 1, 7, strMoney.ToUpper());
xlsSheetEn[rSum + j + 1, 7].Value = strMoneyEn.ToUpper();
setStyle(rSum + j + 1, 7, xlstStyleLeftCH);
removeRow.Sort();
removeRow.Reverse();
for (int r = 0; r < removeRow.Count; r++)
{
setHideRow(Func.ParseInt(removeRow[r]));
}
xlbBook.Save(fileNameDes);
ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('" + strFilePathExport + "'," + PopupWidth + "," + PopupHeight + ",'EditReport', true);", true);
}
}
public void RPT2(C1XLBook book, C1.Win.C1FlexGrid.C1FlexGrid vs, string title, string filename)
{
//string ID="RPT1";
book = new C1XLBook();
XLSheet sheet = book.Sheets[0];
FormatExcel.Init_Excel(sheet);
sheet.Name = "Sheet1";
XLStyle Title = FormatExcel.Get_Style(book, 16, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, false, "");
XLStyle Caption = FormatExcel.Get_Style(book, 10, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, true, "");
XLStyle StringN = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, false, "");
XLStyle String = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "");
XLStyle Number = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,##0.00");
sheet.Columns[0].Width = 500;
int r = 0, cols = 0;
ArrayList a = new ArrayList();
for (int i = 0; i < vs.Cols.Count; i++)
{
if (vs.Cols[i].Visible == true)
{
cols++;
a.Add(i);
}
}
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0],
T_String.GetDataFromSQL("COM_N1", "FILA01A"), Title);
r++;
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0], title, Title);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 6], PublicFunction.L_Get_RPT("RptTa", 1) + ":" + PublicFunction.A_UserID, StringN);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 6], PublicFunction.L_Get_RPT("RptTa", 2) + ":" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm"), StringN);
r++;
for (int j = 0; j < vs.Rows.Count; j++)
{
for (int i = 0; i < a.Count; i++)
{
if (j == 0)
{
FormatExcel.Set_Cell(sheet[r, i], vs.Rows[j][(int)a[i]] + "", Caption);
}
else
{
if (vs.Cols[(int)a[i]].DataType == typeof(DateTime))
{
try
{
FormatExcel.Set_Cell(sheet[r, i], DateTime.Parse(vs.Rows[j][(int)a[i]] + "").ToString(vs.Cols[(int)a[i]].Format), String);
sheet.Columns[i].Width = 1500;
}
catch { FormatExcel.Set_Cell(sheet[r, i], "", String); }
}
else
{
if (vs.Cols[(int)a[i]].DataType == typeof(Boolean))
{
if (vs.GetDataDisplay(j, (int)a[i]) + "" != "True")
{
FormatExcel.Set_Cell(sheet[r, i], "False", String);
}
else
{
FormatExcel.Set_Cell(sheet[r, i], "True", String);
}
}
else
{
if (vs.Cols[(int)a[i]].DataType == typeof(String))
{
FormatExcel.Set_Cell(sheet[r, i], vs.GetDataDisplay(j, (int)a[i]) + "", String);
}
else
{
// FormatExcel.Set_Cell(sheet[r,i],"",String);
// sheet[r,i].Value= T_String.IsNullTo00(vs.GetDataDisplay(j,(int)a[i]));
FormatExcel.Set_Cell(sheet[r, i], vs.GetDataDisplay(j, (int)a[i]) + "", String);
}
//FormatExcel.Set_Cell(sheet[r,i], vs.GetDataDisplay(j,(int)a[i])+"",Number);
}
}
}
}
r++;
}
sheet.Columns[1].Width = 1200;
sheet.Columns[2].Width = 1000;
sheet.Columns[3].Width = 3000;
try
{
string fileName = Application.StartupPath + @"\\Reports\\" + filename + ".xls";
book.Save(fileName);
System.Diagnostics.Process.Start(fileName);
}
catch
{
MessageBox.Show("You must close " + filename + ".xls file first!!!!!");
return;
}
}
private void cmdExportToExcel_Click(object sender, EventArgs e)
{
try
{
_dtData =
SPs.BaocaoThongkeSonoitru(dtFromDate.Value, dtToDate.Value,
Utility.Int16Dbnull(cboDoituongKCB.SelectedValue, -1),
Utility.Int16Dbnull(txtNhanvientiepdon.txtMyID, -1),
Utility.sDbnull(cboKhoa.SelectedValue, "KKB"), thamso, Utility.Int16Dbnull(cboTinhTrang.SelectedValue, -1)).GetDataSet().
Tables[0];
if (_dtData.Rows.Count > 0)
{
Utility.SetDataSourceForDataGridEx(grdList, _dtData, true, true, "1=1", "");
const string reportcode = "baocao_thongke_sokhambenh_noitru";
string duongdan = Utility.GetPathExcel(reportcode);
var book = new C1XLBook();
book.Load(duongdan);
book.DefaultFont = new Font("Time New Roman", 11, FontStyle.Regular);
XLSheet sheet = book.Sheets[0];
DataTable dt = _dtData;
int idxRow = 9;
int idxColSh = 0;
string condition =
string.Format("Từ ngày {0} đến {1} - Đối tượng : {2} - Tình trạng :{3}",
dtFromDate.Text, dtToDate.Text,
cboDoituongKCB.SelectedIndex >= 0
? Utility.sDbnull(cboDoituongKCB.Text)
: "Tất cả",
cboTinhTrang.SelectedIndex > 0
? Utility.sDbnull(cboKhoa.Text)
: "Tất cả");
sheet[4, idxColSh].SetValue(Convert.ToString(condition), HamDungChung.styleStringCenter(book));
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(i + 1), HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToString(dt.Rows[i]["ten_benhnhan"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 2].SetValue(Convert.ToString(dt.Rows[i]["tuoinam"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToString(dt.Rows[i]["tuoinu"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToString(dt.Rows[i]["IsCongNhanVienChuc"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 5].SetValue(Convert.ToString(dt.Rows[i]["Isbhyt"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToString(dt.Rows[i]["thanh_thi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 7].SetValue(Convert.ToString(dt.Rows[i]["nong_thon"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 8].SetValue(Convert.ToString(dt.Rows[i]["Nho12thang"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 9].SetValue(Convert.ToString(dt.Rows[i]["tu1den15tuoi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 10].SetValue(Convert.ToString(dt.Rows[i]["nghe_nghiep"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 11].SetValue(Convert.ToString(dt.Rows[i]["dia_chi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 12].SetValue(Convert.ToString(dt.Rows[i]["noi_gioithieu"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 13].SetValue(Convert.ToString(dt.Rows[i]["ngay_vaovien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 14].SetValue(Convert.ToString(dt.Rows[i]["ngay_chuyenvien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 15].SetValue(Convert.ToString(dt.Rows[i]["ngay_ravien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 16].SetValue(Convert.ToString(dt.Rows[i]["chandoantuyenduoi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 17].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 18].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 19].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 20].SetValue(Convert.ToString(dt.Rows[i]["khoi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 21].SetValue(Convert.ToString(dt.Rows[i]["do_giam"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 22].SetValue(Convert.ToString(dt.Rows[i]["nang_hon"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 23].SetValue(Convert.ToString(dt.Rows[i]["khong_thay_doi"]), HamDungChung.styleStringLeft(book));
idxRow = idxRow + 1;
}
// vị trí dòng dữ liệu của table tiếp theo, vị trí cột bắt đầu t? 0
string getTime = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss"));
string pathDirectory = AppDomain.CurrentDomain.BaseDirectory + "TemplateExcel\\ExportExcel\\";
if (!Directory.Exists(pathDirectory))
{
Directory.CreateDirectory(pathDirectory);
}
book.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" + reportcode +
getTime + ".xls");
Process.Start(
new ProcessStartInfo(AppDomain.CurrentDomain.BaseDirectory +
"\\TemplateExcel\\ExportExcel\\" +
reportcode + getTime + ".xls"));
}
else
{
Utility.ShowMsg("Không có dữ liệu để báo cáo!");
}
}
catch (Exception ex)
{
Utility.ShowMsg("Lỗi: " + ex.Message);
}
}
public static Boolean ArticleDataExportListToExcel(ArticleDataExportModel articleDataExportModel, string FilePathName)
{
Boolean result = false;
//Application ExcelApp = new Application();
//Workbook ExcelWorkBook = null;
//Worksheet ExcelWorkSheet = null;
//ExcelApp.Visible = false;
//// Get a new workbook.
//ExcelWorkBook = ExcelApp.Workbooks.Add(Missing.Value);
////ExcelWorkBook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//try
//{
// IList<PurchaseDetails> listPurchaseDetails = new List<PurchaseDetails>();
// listPurchaseDetails = articleDataExportModel.PurchaseDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// ExcelWorkSheet.Name = "PurchaseDetails";
// ListsToExcel(listPurchaseDetails, ref ExcelWorkSheet);
// IList<SalesDetails> listSalesDetails = new List<SalesDetails>();
// listSalesDetails = articleDataExportModel.SalesDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// ExcelWorkSheet.Name = "SalesDetails";
// ListsToExcel(listSalesDetails, ref ExcelWorkSheet);
// IList<CharDetails> listCharDetails = new List<CharDetails>();
// listCharDetails = articleDataExportModel.CharDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// ExcelWorkSheet.Name = "CharDetails";
// ListsToExcel(listCharDetails, ref ExcelWorkSheet);
// IList<TaxDetails> listTaxDetails = new List<TaxDetails>();
// listTaxDetails = articleDataExportModel.TaxDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// ExcelWorkSheet.Name = "TaxDetails";
// ListsToExcel(listTaxDetails, ref ExcelWorkSheet);
// IList<ArticleDetails> listArticleDetails = new List<ArticleDetails>();
// listArticleDetails = articleDataExportModel.ArticleDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// ExcelWorkSheet.Name = "ArticleDetails";
// ListsToExcel(listArticleDetails, ref ExcelWorkSheet);
// ExcelWorkBook.SaveAs(FilePathName);
// ExcelWorkBook.Close();
// ExcelApp.Quit();
// Marshal.ReleaseComObject(ExcelWorkSheet);
// Marshal.ReleaseComObject(ExcelWorkBook);
// Marshal.ReleaseComObject(ExcelApp);
// result = true;
C1XLBook ExcelWorkBook = new C1XLBook(); //vipin on 02-04-2016
try
{
IList <ArticleDetails> listArticleDetails = new List <ArticleDetails>();
listArticleDetails = articleDataExportModel.ArticleDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
XLSheet ExcelWorkSheet4 = ExcelWorkBook.Sheets[0];
// ExcelWorkSheet4.Name = "ArticleDetails";Article Data
ExcelWorkSheet4.Name = "Article Data";
ListsToExcelUsingC1(listArticleDetails, ref ExcelWorkSheet4, "Article Data");
IList <TaxDetails> listTaxDetails = new List <TaxDetails>();
listTaxDetails = articleDataExportModel.TaxDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
XLSheet ExcelWorkSheet3 = ExcelWorkBook.Sheets.Add();
ExcelWorkSheet3.Name = "Article Tax";
ListsToExcelUsingC1(listTaxDetails, ref ExcelWorkSheet3, "Article Tax");
IList <CharDetails> listCharDetails = new List <CharDetails>();
listCharDetails = articleDataExportModel.CharDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
XLSheet ExcelWorkSheet2 = ExcelWorkBook.Sheets.Add();
ExcelWorkSheet2.Name = "Article Char";
ListsToExcelUsingC1(listCharDetails, ref ExcelWorkSheet2, "Article Char");
IList <SalesDetails> listSalesDetails = new List <SalesDetails>();
listSalesDetails = articleDataExportModel.SalesDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
XLSheet ExcelWorkSheet1 = ExcelWorkBook.Sheets.Add();
ExcelWorkSheet1.Name = "Barcode Pricing";
ListsToExcelUsingC1(listSalesDetails, ref ExcelWorkSheet1, "Barcode Pricing");
IList <PurchaseDetails> listPurchaseDetails = new List <PurchaseDetails>();
listPurchaseDetails = articleDataExportModel.PurchaseDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// Worksheet ExcelWorkSheet = ExcelWorkBook.Worksheets[1];
XLSheet ExcelWorkSheet0 = ExcelWorkBook.Sheets.Add();
ExcelWorkSheet0.Name = "Add. Purchase UOMs";
ListsToExcelUsingC1(listPurchaseDetails, ref ExcelWorkSheet0, "Add. Purchase UOMs");
ExcelWorkBook.Save(FilePathName);
// ExcelWorkBook.Close();
// xlApp.Quit();
// Marshal.ReleaseComObject(ExcelWorkSheet);
// Marshal.ReleaseComObject(ExcelWorkBook);
// Marshal.ReleaseComObject(xlApp);
result = true;
}
catch (Exception exHandle)
{
Logging.Logger.Log(exHandle, Logging.Logger.LogingLevel.Error);
throw exHandle;
}
finally
{
foreach (Process process in Process.GetProcessesByName("Excel"))
{
process.Kill();
}
}
return(result);
}
// load sheet into grid
private void LoadSheet(C1FlexGrid flex, XLSheet sheet, bool fixedCells)
{
// account for fixed cells
int frows = flex.Rows.Fixed;
int fcols = flex.Cols.Fixed;
// copy dimensions
flex.Rows.Count = sheet.Rows.Count + frows;
flex.Cols.Count = sheet.Columns.Count + fcols;
// initialize fixed cells
if (fixedCells && frows > 0 && fcols > 0)
{
flex.Styles.Fixed.TextAlign = TextAlignEnum.CenterCenter;
for (int r = 1; r < flex.Rows.Count; r++)
{
flex[r, 0] = r;
}
for (int c = 1; c < flex.Cols.Count; c++)
{
// string hdr = string.Format("{0}", (char)('A' + c - 1));
// flex[0, c] = hdr;
flex[0, c] = c;
}
}
// set default properties
flex.Font = sheet.Book.DefaultFont;
flex.Rows.DefaultSize = C1XLBook.TwipsToPixels(sheet.DefaultRowHeight);
flex.Cols.DefaultSize = C1XLBook.TwipsToPixels(sheet.DefaultColumnWidth);
// prepare to convert styles
_styles = new Hashtable();
// set row/column properties
for (int r = 0; r < sheet.Rows.Count; r++)
{
// size/visibility
Row fr = flex.Rows[r + frows];
XLRow xr = sheet.Rows[r];
if (xr.Height >= 0)
{
fr.Height = C1XLBook.TwipsToPixels(xr.Height);
}
fr.Visible = xr.Visible;
// style
CellStyle cs = StyleFromExcel(flex, xr.Style);
if (cs != null)
{
//cs.DefinedElements &= ~StyleElementFlags.TextAlign; // << need to fix the grid
fr.Style = cs;
}
}
for (int c = 0; c < sheet.Columns.Count; c++)
{
// size/visibility
Column fc = flex.Cols[c + fcols];
XLColumn xc = sheet.Columns[c];
if (xc.Width >= 0)
{
fc.Width = C1XLBook.TwipsToPixels(xc.Width);
}
fc.Visible = xc.Visible;
// style
CellStyle cs = StyleFromExcel(flex, xc.Style);
if (cs != null)
{
//cs.DefinedElements &= ~StyleElementFlags.TextAlign; // << need to fix the grid
fc.Style = cs;
}
}
// load cells
for (int r = 0; r < sheet.Rows.Count; r++)
{
for (int c = 0; c < sheet.Columns.Count; c++)
{
// get cell
XLCell cell = sheet.GetCell(r, c);
if (cell == null)
{
continue;
}
// apply content
flex[r + frows, c + fcols] = cell.Value;
// apply style
CellStyle cs = StyleFromExcel(flex, cell.Style);
if (cs != null)
{
flex.SetCellStyle(r + frows, c + fcols, cs);
}
}
}
}
private void cboXuatTrenLuoi_Click(object sender, EventArgs e)
{
try
{
_dtData =
SPs.BaoCaoChiDinhHangNgay(dtFromDate.Value, dtToDate.Value,
Utility.Int16Dbnull(cboDoituongKCB.SelectedValue, -1),
Utility.Int16Dbnull(cboTinhTrang.SelectedValue, -1)).GetDataSet().
Tables[0];
if (_dtData.Rows.Count > 0)
{
Utility.SetDataSourceForDataGridEx(grdList, _dtData, true, true, "1=1", "");
const string reportcode = "BAOCAO_HANGNGAY";
string duongdan = Utility.GetPathExcel(reportcode);
var book = new C1XLBook();
book.Load(duongdan);
book.DefaultFont = new Font("Time New Roman", 11, FontStyle.Regular);
XLSheet sheet = book.Sheets[0];
DataTable dt = _dtData;
int idxRow = 5;
int idxColSh = 0;
string condition =
string.Format("Từ ngày {0} đến {1} - Đối tượng : {2} - Trạng thái :{3} ",
dtFromDate.Text, dtToDate.Text,
cboDoituongKCB.SelectedIndex >= 0
? Utility.sDbnull(cboDoituongKCB.Text)
: "Tất cả",
cboTinhTrang.SelectedIndex > 0
? Utility.sDbnull(cboTinhTrang.Text)
: "Tất cả");
sheet[3, idxColSh].SetValue(Convert.ToString(condition), HamDungChung.styleStringCenter(book));
int idx = 0;
foreach (GridEXRow grdExRow in grdList.GetDataRows())
{
if (idx == 0)
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(grdExRow.Cells["DoiTuong"]), HamDungChung.styleStringLeft_Bold(book));
idxRow = idxRow + 1;
}
else
{
if (dt.Rows[idx]["DoiTuong"].ToString() != dt.Rows[idx - 1]["DoiTuong"].ToString())
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(grdExRow.Cells["DoiTuong"]), HamDungChung.styleStringLeft_Bold(book));
idxRow = idxRow + 1;
}
//if (dt.Rows[i]["DoiTuong"].ToString() == dt.Rows[i - 1]["DoiTuong"].ToString() && dt.Rows[i]["Ten_nhombaocao_dichvu"].ToString() != dt.Rows[i - 1]["Ten_nhombaocao_dichvu"].ToString())
//{
// sheet[idxRow, idxColSh].SetValue(string.Format("{0}.{1}", sttloaidichvu, Convert.ToString(dt.Rows[i]["Ten_nhombaocao_dichvu"])), HamDungChung.styleStringLeft_Bold(book));
// sttloaidichvu = sttloaidichvu + 1;
// idxRow = idxRow + 1;
//}
}
sheet[idxRow, idxColSh].SetValue(Convert.ToString(idx + 1), HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToInt64(grdExRow.Cells["id_benhnhan"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 2].SetValue(Convert.ToString(grdExRow.Cells["ma_luotkham"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToString(grdExRow.Cells["ten_benhnhan"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToString(grdExRow.Cells["nam_sinh"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 5].SetValue(Convert.ToString(grdExRow.Cells["gioitinh"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToString(grdExRow.Cells["mathe_bhyt"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 7].SetValue(Convert.ToString(grdExRow.Cells["gt_the_tu"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 8].SetValue(Convert.ToString(grdExRow.Cells["gt_the_den"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 9].SetValue(Convert.ToString(grdExRow.Cells["dia_chi"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 10].SetValue(Convert.ToString(grdExRow.Cells["mabenh_chinh"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 11].SetValue(Convert.ToString(grdExRow.Cells["mabenh_phu"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 12].SetValue(Convert.ToString(grdExRow.Cells["ma_kcbbd"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 13].SetValue(Convert.ToString(grdExRow.Cells["ngay_vao"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 14].SetValue(Convert.ToString(grdExRow.Cells["ngay_ra"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 15].SetValue(Convert.ToDecimal(grdExRow.Cells["tong_tien"].Value), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 16].SetValue(Convert.ToDecimal(grdExRow.Cells["t_bhyt_chitra"].Value), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 17].SetValue(Convert.ToDecimal(grdExRow.Cells["t_bnhan_chitra"].Value), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 18].SetValue(Convert.ToString(grdExRow.Cells["loai_kcb"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 19].SetValue(Convert.ToString(grdExRow.Cells["loai_dichvu"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 20].SetValue(Convert.ToString(grdExRow.Cells["ma_dichvu"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 21].SetValue(Convert.ToString(grdExRow.Cells["ten_dichvu"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 22].SetValue(Convert.ToDecimal(grdExRow.Cells["so_luong"].Value), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 23].SetValue(Convert.ToDecimal(grdExRow.Cells["don_gia"].Value), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 24].SetValue(Convert.ToDecimal(grdExRow.Cells["thanh_tien"].Value), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 25].SetValue(Convert.ToDecimal(grdExRow.Cells["bhyt_chitra"].Value), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 26].SetValue(Convert.ToDecimal(grdExRow.Cells["bnhan_chitra"].Value), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 27].SetValue(Convert.ToString(grdExRow.Cells["ngay_thanhtoan"].Value), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 28].SetValue(Convert.ToString(grdExRow.Cells["ten_dung_tuyen"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 29].SetValue(Convert.ToString(grdExRow.Cells["DoiTuong"]), HamDungChung.styleStringLeft(book));
idx++;
idxRow = idxRow + 1;
}
// vị trí dòng dữ liệu của table tiếp theo, vị trí cột bắt đầu t? 0
string getTime = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss"));
string pathDirectory = AppDomain.CurrentDomain.BaseDirectory + "TemplateExcel\\ExportExcel\\";
if (!Directory.Exists(pathDirectory))
{
Directory.CreateDirectory(pathDirectory);
}
book.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" + reportcode +
getTime + ".xls");
Process.Start(
new ProcessStartInfo(AppDomain.CurrentDomain.BaseDirectory +
"\\TemplateExcel\\ExportExcel\\" +
reportcode + getTime + ".xls"));
}
else
{
Utility.ShowMsg("Không có dữ liệu để báo cáo!");
}
}
catch (Exception ex)
{
Utility.ShowMsg("Lỗi: " + ex.Message);
}
}
