public static BooleanValue <XSSFWorkbook> TwoLeveltoExcelFile(DataTable dtMain, DataTable dtSub, string mainBusiNo, string subBusiNo, bf_Query query, string joinCondition = null) { XSSFWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); IRow head = sheet.CreateRow(0); bf_Busi_Fields fieldsMain = bf_Busis.GetItem(mainBusiNo).Fields; bf_Busi_Fields fieldsSub = bf_Busis.GetItem(subBusiNo).Fields; //新建一个字体样式对象 IFont font = workbook.CreateFont(); ICellStyle headStyle = workbook.CreateCellStyle(); //设置字体加粗样式 font.FontHeightInPoints = 11; font.FontName = "微软雅黑"; font.IsBold = true; //使用SetFont方法将字体样式添加到单元格样式中 headStyle.SetFont(font); ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat dataformat = workbook.CreateDataFormat(); dateStyle.DataFormat = dataformat.GetFormat("yyyy-MM-dd HH:mm:ss"); int idx = 0; int colsCount = 0; foreach (bf_Busi_Field field in fieldsMain) { if (field.VisibleType != 1) { continue; } ICell headCell = head.CreateCell(idx); headCell.SetCellValue(field.Caption); headCell.CellStyle = headStyle; idx++; colsCount++; } idx = 1; string mAlias = ((bf_Query_Item)query.Items.AsEnumerable().FirstOrDefault(s => ((bf_Query_Item)s).BusiNo == mainBusiNo)).Alias; string sAlias = ((bf_Query_Item)query.Items.AsEnumerable().FirstOrDefault(s => ((bf_Query_Item)s).BusiNo == subBusiNo)).Alias; // m.MRP_NO=s1.MRP_NO AND m.ID_NO=s1.BOM_NO string[] joinCd = !string.IsNullOrEmpty(joinCondition)?joinCondition.Replace(".", "_").Split(' '): ((bf_Query_Item)query.Items.AsEnumerable().FirstOrDefault(s => ((bf_Query_Item)s).BusiNo == subBusiNo)).Condition.Replace(".", "_").Split(' '); StringBuilder sbd = new StringBuilder(); List <string> parrentFields = new List <string>(); foreach (var cd in joinCd) { var items = cd.Split('='); if (items.Length > 1) { var pcd = items.FirstOrDefault(f => f.ToUpper().StartsWith(mAlias.ToUpper())); parrentFields.Add(pcd); var ccd = items.FirstOrDefault(f => f.ToUpper().StartsWith(sAlias.ToUpper())); sbd.Append(ccd); sbd.Append("='{"); sbd.Append(parrentFields.Count() - 1); sbd.Append("}'"); } else { sbd.Append(" "); sbd.Append(cd); sbd.Append(" "); } } foreach (DataRow mr in dtMain.Rows) { IRow row = sheet.CreateRow(idx); fillRowData(fieldsMain, mAlias, row, mr, dateStyle); idx++; head = sheet.CreateRow(idx); int colIdx = 1; foreach (bf_Busi_Field field in fieldsSub) { if (field.VisibleType != 1) { continue; } ICell headCell = head.CreateCell(colIdx); headCell.SetCellValue(field.Caption); headCell.CellStyle = headStyle; colIdx++; } idx++; DataRow[] srs = null; switch (parrentFields.Count) { case 1: srs = dtSub.Select(string.Format(sbd.ToString(), mr[parrentFields[0]])); break; case 2: srs = dtSub.Select(string.Format(sbd.ToString(), mr[parrentFields[0]], mr[parrentFields[1]])); break; case 3: srs = dtSub.Select(string.Format(sbd.ToString(), mr[parrentFields[0]], mr[parrentFields[1]], mr[parrentFields[2]])); break; case 4: srs = dtSub.Select(string.Format(sbd.ToString(), mr[parrentFields[0]], mr[parrentFields[1]], mr[parrentFields[2]], mr[parrentFields[3]])); break; } if (srs == null) { continue; } foreach (DataRow sr in srs) { row = sheet.CreateRow(idx); fillRowData(fieldsSub, sAlias, row, sr, dateStyle, 1); idx++; } } EXCELFileTools.SetColumnWidth(sheet, colsCount); return(new BooleanValue <XSSFWorkbook>(workbook)); }
public static void fillRowData(bf_Busi_Fields fields, string fieldAlia, IRow row, DataRow dr, ICellStyle dateStyle, int startCol = 0) { int cellIdx = startCol; foreach (bf_Busi_Field field in fields) { if (field.VisibleType != 1) { continue; } string key = string.Format("{0}_{1}", fieldAlia, field.m_Name); ICell cell = row.CreateCell(cellIdx); cellIdx++; if (field.Editor == bf_FieldEditor.GBF_Combox) { bf_Combox cb = bf_Comboxs.GetItem(field.edit_No); bool flg = false; if (cb != null) { DataRow[] items = bf_ComboxItems.Default.Select(string.Format("MID={0}", cb.ID)); // bf_Lang_Datas langs = new bf_Lang_Datas("_bf_ComboxItem", cb.No); for (int j = 0; j < items.Length; j++) { if (items[j]["ValueText"].Equals(dr[key])) { cell.SetCellValue(LangDatas.Default.GetLangData("_bf_ComboxItem", cb.No, items[j]["ValueText"].ToString(), items[j]["ValueText"].ToString())); flg = true; } } } if (!flg) { double d; if (dr[key] != DBNull.Value && !string.IsNullOrEmpty(dr[key].ToString().Trim()) && double.TryParse(dr[key].ToString().Trim(), out d)) { cell.SetCellValue(d); } else { cell.SetCellValue(dr[key].ToString()); } } } else if (dr.Table.Columns[key].DataType == typeof(DateTime)) { if (dr[key] == DBNull.Value) { cell.SetCellValue(""); } else { cell.SetCellValue((DateTime)dr[key]); } cell.CellStyle = dateStyle; } else if (dr.Table.Columns[key].DataType == typeof(bool)) { cell.SetCellValue(dr[key].ToString() == "1" || dr[key].ToString() == "Y" ? "是" : "否"); } else if (dr.Table.Columns[key].DataType == typeof(Int32) || dr.Table.Columns[key].DataType == typeof(decimal) || dr.Table.Columns[key].DataType == typeof(Int64) || dr.Table.Columns[key].DataType == typeof(double)) { double d; if (dr[key] != DBNull.Value && !string.IsNullOrEmpty(dr[key].ToString().Trim()) && double.TryParse(dr[key].ToString().Trim(), out d)) { cell.SetCellValue(d); } } else { cell.SetCellValue(dr[key].ToString()); } } }