public static string GetMaxNo(string tableName, string prefixStr, int numLength = 6)
{
Dictionary <string, object> paras = new Dictionary <string, object>();
paras.Add("tableName", tableName);
string sql = @"select * from AppAutoNo where tableName='" + tableName + "'";
DataTable dt = AppMember.DbHelper.GetDataSet(sql, paras).Tables[0];
int index = 1;
if (dt.Rows.Count > 0)
{
index = DataConvert.ToInt32(dt.Rows[0]["indexValue"]) + 1;
paras.Add("indexValue", index);
sql = @"update AppAutoNo set indexValue=@indexValue
where tableName=@tableName";
AppMember.DbHelper.ExecuteSql(sql, paras);
numLength = DataConvert.ToInt32(dt.Rows[0]["numLength"]);
//prefixStr = DataConvert.ToString(dt.Rows[0]["prefixStr"]);
}
else
{
paras.Add("autoNoId", Guid.NewGuid());
paras.Add("indexValue", index);
sql = @"insert into AppAutoNo(autoNoId,tableName,indexValue,prefixStr,numLength)
values(@autoNoId,@tableName,@indexValue,'" + prefixStr + "'," + numLength.ToString() + ")";
AppMember.DbHelper.ExecuteSql(sql, paras);
}
string zeroStr = "";
for (int i = 0; i < numLength; i++)
{
zeroStr += "0";
}
return(prefixStr + index.ToString(zeroStr));
}
public static string GetMaxId(string tableName, string special = "")
{
string dateString = GetServerDate().ToString("yyyy-MM-dd");
string sql = @"select * from AppId where tableName='" + tableName + "' and dateValue='" + dateString + "'";
DataTable dt = AppMember.DbHelper.GetDataSet(sql).Tables[0];
int index = 1;
if (dt.Rows.Count > 0)
{
index = DataConvert.ToInt32(dt.Rows[0]["indexValue"]) + 1;
sql = "update AppId set indexValue=" + index +
" where tableName='" + tableName + "' and dateValue='" + dateString + "'";
AppMember.DbHelper.ExecuteSql(sql);
}
else
{
sql = "insert into AppId(id,tableName,indexValue,dateValue)" +
"values('" + Guid.NewGuid() + "','" + tableName + "'," + index + ",'" + dateString + "')";
AppMember.DbHelper.ExecuteSql(sql);
}
dateString = GetServerDate().ToString("yyMMdd");
string prefixStr = "";
if (special == "")
{
if (AppMember.TablePrefix.ContainsKey(tableName.ToLower()))
{
prefixStr = AppMember.TablePrefix[tableName.ToLower()];
}
else
{
prefixStr = tableName.Length > 3 ? tableName.Substring(0, 3) : tableName;
}
}
else
{
if (AppMember.TablePrefix.ContainsKey(special.ToLower()))
{
prefixStr = AppMember.TablePrefix[special.ToLower()];
}
}
int prelen = prefixStr.Length;
string indexformat = "";
for (int i = 0; i < 6 - prelen; i++)
{
indexformat += "0";
}
string strId = prefixStr + dateString + index.ToString(indexformat);
return(strId);
}
/// <summary>
/// 导出到Execl
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="strSheetName">工作部名称</param>
/// <param name="pathloading">保存路径</param>
/// <param name="title">标题名</param>
public static void PrintExcel(System.Data.DataTable dt, AdvanceGridLayout layout, string strSheetName)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //Execl的操作类
Workbook bookDest = (Workbook)excel.Workbooks.Add(Missing.Value);
Worksheet sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Worksheet; //给工作薄添加一个Sheet
sheetDest.Name = strSheetName;
for (int i = bookDest.Worksheets.Count; i > 1; i--)
{
Worksheet wt = (Worksheet)bookDest.Worksheets[i];
if (wt.Name != strSheetName)
{
wt.Delete();
}
}
//Range rngRow = (Microsoft.Office.Interop.Excel.Range)sheetDest.Columns[1, Type.Missing];
////rngRow.UseStandardWidth = 70;
//Range rngA = (Range)sheetDest.Columns["A", Type.Missing];//设置单元格格式
//rngA.NumberFormatLocal = "@";//字符型格式
//Range rngJ = (Range)sheetDest.Columns["J", Type.Missing];
//rngJ.NumberFormatLocal = "@";
//Range rngQ = (Range)sheetDest.Columns["Q", Type.Missing];
//rngQ.NumberFormatLocal = "@";
//Range rngE = (Range)sheetDest.Columns["E", Type.Missing];
//rngE.NumberFormatLocal = @"yyyy-mm-dd";//日期型格式
excel.Application.Workbooks.Add(true);
try
{
int rowIndex = 2;
int colIndex = 1;
if (layout.GridGroupHeader != null)
{
////导入group头
foreach (KeyValuePair <string, GirdGroupHeaderInfo> dic in layout.GridGroupHeader)
{
Range rng;
if (DataConvert.ToString(dic.Value.NumberOfRows) != "")
{
rng = sheetDest.Range[sheetDest.Cells[1, colIndex], sheetDest.Cells[1 + DataConvert.ToInt32(dic.Value.NumberOfRows) - 1, colIndex]];
rng.Merge(rng.MergeCells);
sheetDest.Cells[1, colIndex] = dic.Value.TitleText;
SetHeaderRang(rng);
colIndex++;
}
else
{
rng = sheetDest.Range[sheetDest.Cells[1, colIndex], sheetDest.Cells[1, colIndex + DataConvert.ToInt32(dic.Value.NumberOfColumns) - 1]];
rng.Merge(rng.MergeCells);
sheetDest.Cells[1, colIndex] = dic.Value.TitleText;
SetHeaderRang(rng);
colIndex += DataConvert.ToInt32(dic.Value.NumberOfColumns);
}
}
}
colIndex = 0;
//导入列头
foreach (KeyValuePair <string, GridInfo> dic in layout.GridLayouts)
{
if (dic.Value.Hidden == "false")
{
colIndex++;
Range rng = (Range)sheetDest.Cells[2, colIndex];
sheetDest.Cells[2, colIndex] = dic.Value.Caption;//Execl中的第一列把DataTable的列名先导进去
SetHeaderRang(rng);
rng.ColumnWidth = DataConvert.ToDouble(dic.Value.Width) / 7.647;
}
}
//导入数据行
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (KeyValuePair <string, GridInfo> dic in layout.GridLayouts)
{
if (dic.Value.Hidden == "false")
{
colIndex++;
sheetDest.Cells[rowIndex, colIndex] = row[dic.Key].ToString();
Range rng01 = (Range)sheetDest.Cells[rowIndex, colIndex];
SetDataRang(rng01);
}
}
}
}
catch
{
throw new Exception();
}
bookDest.PrintOutEx();
//excel.Quit();
//excel = null;
//GC.Collect();
}