根据模板打印所有数据动态配置
打印后生成的数据模板
在数据库配置表
表1记录 块项 数据库表如下
表2记录 布局行列中的数据项值，取什么数据，我这里是从TextBox中的取值，所以我记录的是Tag
详细代码如下
public FrmSQ410()
{
InitializeComponent();
gvTemp.OptionsBehavior.Editable = gvSlab.OptionsBehavior.Editable=false;
date1.EditValue = DateTime.Now.Date.AddHours(0).AddMinutes(0).AddSeconds(0);
date2.EditValue = DateTime.Now.Date.AddDays(1).AddHours(23).AddMinutes(59).AddSeconds(59);
txtLen.TextChanged += txtLen_TextChanged;
tsbPrintBindingSource.PositionChanged += tsbPrintBindingSource_PositionChanged;
gvSlab.RowClick += gvSlab_RowClick;
}
void txtLen_TextChanged(object sender, EventArgs e)
{
double wgt = Math.Round((Convert.ToDouble(txtLen.Text.Trim()) / 1000) * WgtMeter, 3);
txtWgt.Text = string.Format("{0}", wgt*1000);
}
void gvSlab_RowClick(object sender, DevExpress.XtraGrid.Views.Grid.RowClickEventArgs e)
{
if (e.RowHandle < 0) return;
var row = gvSlab.GetDataRow(e.RowHandle);
txtStove.Text=row["炉号"].ToString();
txtStlGrd.Text=row["钢种"].ToString();
txtSpec.Text = row["规格"].ToString();
txtStdCode.Text = row["执行标准"].ToString();
txtLen.Text = row["长度"].ToString();
txtNumTotal.Text = row["支数"].ToString();
txtProdDate.Text =Convert.ToDateTime( row["生产日期"]).ToString("yyyy.MM.dd");
txtWgt.Text = string.Format("{0}", Convert.ToDouble(row["重量"]) * 1000);
WgtMeter = Convert.ToDouble(row["米重"]);
}
void tsbPrintBindingSource_PositionChanged(object sender, EventArgs e)
{
if (tsbPrintBindingSource.Current == null) return;
var current = tsbPrintBindingSource.Current as RMES.IBatisEntity.TsbPrint;
if (lst_TsbPrintItemAll != null)
lst_TsbPrintItem = lst_TsbPrintItemAll.Where(m => m.CPid == current.CId).OrderBy(m => m.CRowIdx).ToList();
}
Spire.Xls.Core.IWorksheet worksheet = null;
Workbook workbook = null;
private string Plant = string.Empty;
private string fileSavePath = Path.Combine(Environment.GetEnvironmentVariable("TEMP"), "PrintGP.xls");
private List<RMES.IBatisEntity.TsbPrintItem> lst_TsbPrintItemAll = null;
private List<RMES.IBatisEntity.TsbPrintItem> lst_TsbPrintItem = null;
List<Control> lstText = new List<Control>();
private double WgtMeter = 0;
private void InitLayoutText()
{
foreach(Control ctrl in layoutControl1.Controls)
{
if (ctrl is DevExpress.XtraEditors.BaseEdit&&ctrl.Tag!=null )
{
lstText.Add(ctrl);
}
}
}
private void FrmSQ410_Load(object sender, EventArgs e)
{
txtGroup.Text = RMES.BLL.ShiftInfo.Instance.Team;
InitTemplate();
InitLayoutText();
}
private void InitTemplate()
{
var lst_TsbPrint = RMES.IBatisEntity.TsbPrintList.GetAll();
tsbPrintBindingSource.DataSource = lst_TsbPrint;
gvTemp.BestFitColumns();
//获取所有
lst_TsbPrintItemAll = RMES.IBatisEntity.TsbPrintItemList.GetAll().ToList();
if (lst_TsbPrint != null)
{
var item = lst_TsbPrint.FirstOrDefault();
lst_TsbPrintItem = lst_TsbPrintItemAll.Where(m => m.CPid == item.CId).OrderBy(m => m.CRowIdx).ToList();
}
}
private void QueryPrintSet()
{
}
/// <summary>
/// 字母转阿斯特码
/// </summary>
/// <param name="c"></param>
/// <returns></returns>
private int CharacterToASCI(string c)
{
byte[] array = new byte[1]; //定义一组数组array
array = System.Text.Encoding.ASCII.GetBytes(c); //string转换的字母
int asciicode = (short)(array[0]);
return asciicode;
}
private string ASIToCharacter(int a)
{
string c = string.Empty;
byte[] array = new byte[1];
array[0] = (byte)(Convert.ToInt32(a)); //ASCII码强制转换二进制
c = Convert.ToString(System.Text.Encoding.ASCII.GetString(array));
return c;
}
private int start_Num = 1;
//获取值
private string getVal(int col,int row)
{
//开始支数，总支数,
string rs = string.Empty;
var item = lst_TsbPrintItem.Where(m => m.CRowIdx == row && m.CColIdx == col).FirstOrDefault();
//获取值
if(item !=null)
{
if (item.CFiledName == "支数")
{
rs = string.Format("{0}-{1}",txtNumTotal.Text.Trim(),start_Num++);
}
else
{
var ctrl = lstText.Where(m => m.Tag.ToString() == item.CFiledName).FirstOrDefault();
rs = ctrl.Text.Trim();
}
}
return rs;
}
private void btnPrint_Click(object sender, EventArgs e)
{
if (tsbPrintBindingSource.Current == null) return;
var item_TbPrintSet = tsbPrintBindingSource.Current as RMES.IBatisEntity.TsbPrint;
string fileTempPath = Path.Combine(Environment.CurrentDirectory, item_TbPrintSet.CWorkName);
if (!File.Exists(fileTempPath))
{
DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("模板{0}文件不存在", item_TbPrintSet.CWorkName), "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
workbook = new Workbook();
if (!string.IsNullOrEmpty(item_TbPrintSet.CPwd.Trim()))
{
workbook.OpenPassword = item_TbPrintSet.CPwd;
}
workbook.LoadFromFile(fileTempPath);
worksheet = workbook.Worksheets.Where(m => m.Name == item_TbPrintSet.CSheetName).FirstOrDefault();
if (worksheet == null)
{
DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("模板文件中Sheet{0}不存在", item_TbPrintSet.CSheetName), "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//移除其它数据
List<Spire.Xls.Core.IWorksheet> lstsheet = new List<Spire.Xls.Core.IWorksheet>();
workbook.Worksheets.All(m => { if (m.Name != item_TbPrintSet.CSheetName) { lstsheet.Add(m); } return true; });
lstsheet.All(m => { m.Remove(); return true; });
//判定条件
if (lst_TsbPrintItem == null)
{
DevExpress.XtraEditors.XtraMessageBox.Show("模板数据项目没有配置", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (lst_TsbPrintItem.Count == 0)
{
DevExpress.XtraEditors.XtraMessageBox.Show("模板数据项目没有配置", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (lst_TsbPrintItem.Where(m => string.IsNullOrEmpty(m.CFiledName)).Count() > 0)
{
DevExpress.XtraEditors.XtraMessageBox.Show("模板数据映射中有映射列为空数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("是否打印模板{0} ?", item_TbPrintSet.CSheetName ), "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.No) return;
///初始化开始支数
if (!string.IsNullOrEmpty(txtNumStart.Text.Trim()))
{
start_Num = Convert.ToInt32(txtNumStart.Text.Trim());
}
//生成打印数据
int rows_item = lst_TsbPrintItem.Count;
int rows = 0; //几行几列
int cols = 0;
int rowStart = 0; //从几开开始
int colStart = 0;
int rowSpan = 0; //列间隔
int colSpan = 0;
string lenChar = string.Empty;//规格分隔符
rows = Convert.ToInt32(item_TbPrintSet.CRow);
cols = Convert.ToInt32(item_TbPrintSet.CCol);
rowStart = Convert.ToInt32(item_TbPrintSet.CRowStart);//行开始
colStart = Convert.ToInt32(item_TbPrintSet.CColStart);//列开始
rowSpan = Convert.ToInt32(item_TbPrintSet.CRowSpan); //行间隔
colSpan = Convert.ToInt32(item_TbPrintSet.CColSpan); //列间隔
int n = 65; //从A开始
int index = 1; //从1行开始
n += colStart; //从A B C 开始
index += rowStart;
int nl = n; //临时变量存入列A\B\C
//遍历行
for (int i = 0; i < rows; i++)
{
nl = n;
//遍历列
for (int k = 0; k < cols; k++)
{
//遍历设置单元格
for (int i_row = 0; i_row < item_TbPrintSet.CLrow; i_row++)
{
for (int i_col = 0; i_col < item_TbPrintSet.CLcol; i_col++)
{
string range = string.Format("{0}{1}", ASIToCharacter(nl + i_col), index+i_row);
string val = getVal(i_col, i_row);
if (!string.IsNullOrEmpty(val))
{
worksheet.Range[range].Text = val;
}
}
}
nl += colSpan + (int)item_TbPrintSet.CLcol; //Excel //A B C D
}
index += rowSpan + (int)item_TbPrintSet.CLrow;
}
workbook.SaveToFile(fileSavePath);
worksheet.PageSetup.PaperSize = PaperSizeType.PaperA4;
worksheet.PageSetup.PrintQuality = 2;
PrintDocument pd = workbook.PrintDocument;
pd.Print();
}
private void btnRefush_Click(object sender, EventArgs e)
{
InitTemplate();
}
private void btnQuery_Click(object sender, EventArgs e)
{
string Sql = string.Format(" SELECT TA. C_BATCH_NO 炉号, TA.C_GB_STL_GRD 钢种, TA.C_SPEC 规格, TA.N_LEN 长度, SUM(Ta.N_QUA)OVER(PARTITION BY TA.C_BATCH_NO)支数, TA.C_STD_CODE 执行标准, TA.N_WGT_METER 米重, TA.N_WGT 重量,TA.D_TRANS_DATE 生产日期, TA.C_CUS_NAME 客户 FROM (SELECT T.C_BATCH_NO, SUM(T.N_QUA) N_QUA,MAX(T.D_TRANS_DATE)D_TRANS_DATE, T.C_CUS_NAME, T.C_GB_STL_GRD, T.C_SPEC, T.N_LEN, T.C_STD_CODE, T.N_WGT_METER, T.N_WGT FROM RMES.TSC_SLAB_MAIN T WHERE T.C_SLAB_TYPE = 'S' AND T.C_REFINE = '0' AND T.C_REFINE_NUM = '0' AND T.C_MAT_TYPE IN ('1', '4') AND T.C_STOVE LIKE '%{0}%' AND T.D_TRANS_DATE BETWEEN TO_DATE('{1}','YYYY-MM-DD HH24:MI:SS') AND TO_DATE('{2}','YYYY-MM-DD HH24:MI:SS') GROUP BY T.C_BATCH_NO, T.C_GB_STL_GRD, T.C_SPEC, T.N_LEN, T.C_STD_CODE, T.N_WGT_METER, T.N_WGT, T.C_CUS_NAME) TA ", txtStoveQuery.Text.Trim(), date1.Text.Trim(), date2.Text.Trim());
QueryResult(Sql, gcSlab, gvSlab);
}
private void QueryResult(string Sql, DevExpress.XtraGrid.GridControl gridCtrl, DevExpress.XtraGrid.Views.Grid.GridView grid)
{
using (BackgroundWorker bk = new BackgroundWorker())
{
bk.DoWork += (x, y) =>
{
DataTable tb_Rs = RV.BLL.Base.SqlService.ExecuteQuery(Sql);
y.Result = tb_Rs;
};
bk.RunWorkerCompleted += (m, n) =>
{
if (n.Result != null)
{
var tb_Rs = n.Result as DataTable;
grid.Columns.Clear();
gridCtrl.DataSource = tb_Rs;
gridCtrl.RefreshDataSource();
grid.OptionsBehavior.ReadOnly = true;
//grid.OptionsView.ShowFooter = true;
foreach (GridColumn col in grid.Columns)
{
col.AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Near;
if (col.FieldName.Contains("ID"))
{
col.Visible = false;
}
else if (col.ColumnType == typeof(DateTime))
{
col.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
col.DisplayFormat.FormatString = "yyyy-MM-dd HH:mm:ss";
}
}
grid.BestFitColumns();
}
};
bk.RunWorkerAsync();
}
}
}
