public override int GetHashCode()
{
int hash = 1;
if (Page != 0)
{
hash ^= Page.GetHashCode();
}
if (PageSize != 0)
{
hash ^= PageSize.GetHashCode();
}
if (PageCount != 0)
{
hash ^= PageCount.GetHashCode();
}
if (RowCount != 0)
{
hash ^= RowCount.GetHashCode();
}
if (FirstRow != 0)
{
hash ^= FirstRow.GetHashCode();
}
if (LastRow != 0)
{
hash ^= LastRow.GetHashCode();
}
if (_unknownFields != null)
{
hash ^= _unknownFields.GetHashCode();
}
return(hash);
}
private string ReadTime()
{
return(new StringBuilder()
.AppendLine(TopLamp.ToString())
.AppendLine(FirstRow.ToString())
.AppendLine(SecondRow.ToString())
.AppendLine(ThirdRow.ToString())
.Append(FourthRow.ToString())
.ToString());
}
public void WriteToFile(string file)
{
using (StreamWriter sr = new StreamWriter(File.OpenWrite(file)))
{
sr.WriteLine(FirstRow.ToString());
foreach (CsvRow row in Data)
{
sr.WriteLine(row.ToString());
}
}
}
public Form1()
{
InitializeComponent();
DoubleBuffered = true;
导出处理后的数据();
List <分类表> 读取并分析分类表(string Path = "分类表.xlsx")
{
var 分类列表 = new List <分类表>();
using (var reader =
ExcelReaderFactory.CreateReader(new FileStream(Path, FileMode.Open, FileAccess.Read,
FileShare.ReadWrite)))
{
var TempS = "";
var TempS2 = "";
foreach (DataRow Row in reader.AsDataSet().Tables[0].Rows)
{
if (string.IsNullOrWhiteSpace(Row[0].ToString()))
{
分类列表.Add(new 分类表(TempS, Row[1].ToString(), Row[2].ToString(), TempS2));
}
else
{
TempS = Row[0].ToString();
TempS2 = Row[3].ToString();
分类列表.Add(new 分类表(TempS, Row[1].ToString(), Row[2].ToString(), TempS2));
}
}
}
return(分类列表);
}
Tuple <List <object>, Dictionary <string, List <object[]> > > 读取并分析原始数据(string path = "品号信息20180417.xls")
{
Tuple <List <object>, Dictionary <string, List <object[]> > > Ret;
using (var reader = ExcelReaderFactory.CreateReader(File.Open(path, FileMode.Open)))
{
var Sheel = reader.AsDataSet().Tables[0].Rows;
Ret = new Tuple <List <object>, Dictionary <string, List <object[]> > >(Sheel[0].ItemArray.ToList(),
new Dictionary <string, List <object[]> >());
Sheel.RemoveAt(0);
var 分类号Index = Ret.Item1.FindIndex(x => x.ToString() == "品号");
foreach (DataRow Row in Sheel)
{
var 分类号 = Row[分类号Index].ToString().Split('-')[0];
if (Ret.Item2.ContainsKey(分类号))
{
Ret.Item2[分类号].Add(Row.ItemArray);
}
else
{
Ret.Item2.Add(分类号, new List <object[]> {
Row.ItemArray
});
}
//Interlocked.Increment(ref AllCount);
}
}
return(Ret);
}
void 导出处理后的数据()
{
void InitSheet(ref Worksheet worksheet)
{
worksheet.Cells.ColumnWidth[0] = 3200;
worksheet.Cells.ColumnWidth[1] = 3200;
worksheet.Cells.ColumnWidth[2] = 10000;
worksheet.Cells.ColumnWidth[3] = 10000;
worksheet.Cells.ColumnWidth[4] = 2000;
worksheet.Cells.ColumnWidth[5] = 2000;
worksheet.Cells.ColumnWidth[6] = 10000;
worksheet.Cells.ColumnWidth[7] = 2000;
worksheet.Cells.ColumnWidth[8] = 3200;
worksheet.Cells.ColumnWidth[9] = 2000;
worksheet.Cells.ColumnWidth[10] = 2000;
worksheet.Cells[0, 0] = new Cell("物料编码");
worksheet.Cells[1, 0] = new Cell("Id$");
worksheet.Cells[0, 1] = new Cell("分类（系列号）");
worksheet.Cells[1, 1] = new Cell("classification$<name>");
worksheet.Cells[0, 2] = new Cell("品名（按命名规则）");
worksheet.Cells[1, 2] = new Cell("name$");
worksheet.Cells[0, 3] = new Cell("规格（按命名规则）");
worksheet.Cells[1, 3] = new Cell("Specification");
worksheet.Cells[0, 4] = new Cell("单位");
worksheet.Cells[1, 4] = new Cell("UOM<description>");
worksheet.Cells[0, 5] = new Cell("品号属性");
worksheet.Cells[1, 5] = new Cell("SourceType<name>");
worksheet.Cells[0, 6] = new Cell("快捷码");
worksheet.Cells[1, 6] = new Cell("KJM");
worksheet.Cells[0, 7] = new Cell("主要仓库");
worksheet.Cells[1, 7] = new Cell("ZYCK<name>");
worksheet.Cells[0, 8] = new Cell("会计分类");
worksheet.Cells[1, 8] = new Cell("KJFL<name>");
worksheet.Cells[0, 9] = new Cell("备注");
worksheet.Cells[1, 9] = new Cell("Remark");
worksheet.Cells[0, 10] = new Cell("文件夹(默认即可，不输入）");
worksheet.Cells[1, 10] = new Cell("Folder");
}
/* void 全部数据验证(Workbook book)
* {
* var Count = 0;
* foreach (var Item in book.Worksheets) Count = Count + Item.Cells.Rows.Count - 1;
* Console.WriteLine();
* }*/
var ClassifyList = 读取并分析分类表();
var WorkbookDic = new Dictionary <string, Workbook>();
var OpenXls = new OpenFileDialog();
OpenXls.Filter = @"Excel|*.xls";
OpenXls.ShowDialog();
if (!File.Exists(OpenXls.FileName))
{
return;
}
var OriData = 读取并分析原始数据(OpenXls.FileName);
var HeaderIndex = new int[10];
for (var i = 0; i < HeaderIndex.Length; i++)
{
switch (i)
{
case 0:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "品号");
break;
case 1:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "品名");
break;
case 2:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "规格");
break;
case 3:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "单位");
break;
case 4:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "品号属性");
break;
case 5:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "快捷码");
break;
case 6:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "主要仓库");
break;
case 7:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "会计");
break;
case 8:
HeaderIndex[i] = OriData.Item1.FindIndex(x => x.ToString() == "备注");
break;
}
}
foreach (var Sheets in OriData.Item2)
{
Workbook workbook;
Worksheet worksheet;
var Add = false;
var SheetName = (from Item in ClassifyList
where Sheets.Key.StartsWith(Item.分类号)
select Item).FirstOrDefault();
if (string.IsNullOrWhiteSpace(SheetName.一级))
{
continue;
}
if (SheetName.一级 == "未知分类")
{
// continue;
}
if (WorkbookDic.ContainsKey(SheetName.一级))
{
workbook = WorkbookDic[SheetName.一级];
worksheet = workbook.Worksheets[0];
}
else
{
Add = true;
workbook = new Workbook();
worksheet = new Worksheet(SheetName.页名);
InitSheet(ref worksheet);
}
var DicCount = worksheet.Cells.Rows.Count;
for (var i = 0; i < Sheets.Value.Count; i++, DicCount++)
{
worksheet.Cells[DicCount, 0] = new Cell(Sheets.Value[i][HeaderIndex[0]].ToString()); //品号
worksheet.Cells[DicCount, 1] = new Cell(Sheets.Key); //系列号
worksheet.Cells[DicCount, 2] = new Cell(Sheets.Value[i][HeaderIndex[1]].ToString()); //品名
worksheet.Cells[DicCount, 3] = new Cell(Sheets.Value[i][HeaderIndex[2]].ToString()); //规格
worksheet.Cells[DicCount, 4] = new Cell(Sheets.Value[i][HeaderIndex[3]].ToString()); //库存单位
worksheet.Cells[DicCount, 5] = new Cell(Sheets.Value[i][HeaderIndex[4]].ToString()); //品号属性
worksheet.Cells[DicCount, 6] = new Cell(Sheets.Value[i][HeaderIndex[5]].ToString()); //快捷码
worksheet.Cells[DicCount, 7] = new Cell(Sheets.Value[i][HeaderIndex[6]].ToString()); //主要仓库
worksheet.Cells[DicCount, 8] = new Cell(Sheets.Value[i][HeaderIndex[7]].ToString()); //会计
worksheet.Cells[DicCount, 9] = new Cell(HeaderIndex[8] == -1 ? "" : Sheets.Value[i][HeaderIndex[8]].ToString()); //备注缺失
worksheet.Cells[DicCount, 10] = new Cell(@"L:\"); //文件夹
}
if (Add)
{
workbook.Worksheets.Add(worksheet);
WorkbookDic.Add(SheetName.一级, workbook);
}
}
foreach (var item in WorkbookDic)
{
item.Value.Save($"{item.Key}.xls");
}
//全部数据验证(workbook);
}
void ReadXls(string path)
{
using (var reader = ExcelReaderFactory.CreateReader(File.Open(path, FileMode.Open)))
{
foreach (DataTable item in reader.AsDataSet().Tables)
{
var DataSet = new DataGridView
{
ReadOnly = true,
AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells,
AllowUserToAddRows = false,
Dock = DockStyle.Fill,
Name = item.TableName
};
ShowTab.TabPages.Add(item.TableName, item.TableName);
foreach (var FirstRow in item.Rows[0].ItemArray)
{
DataSet.Columns.Add(FirstRow.ToString(), FirstRow.ToString());
DataSet.Columns[DataSet.Columns.Count - 1].SortMode =
DataGridViewColumnSortMode.NotSortable;
}
item.Rows.RemoveAt(0);
foreach (DataRow DataBase in item.Rows)
{
DataSet.Rows.Add(DataBase.ItemArray);
}
DataSet.Update();
ShowTab.TabPages[item.TableName].Controls.Add(DataSet);
}
}
}
void SaveXls(string path, Dictionary <string, List <BomInfo> > SaveInfo)
{
var workbook = new Workbook();
var count = 0;
foreach (var BomItem in SaveInfo)
{
var S1 = BomItem.Key.Split(new[] { '&' }, StringSplitOptions.RemoveEmptyEntries);
var S2 = S1[0].Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
if (S1.Length != 1)
{
var S3 = S1[1].Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
var worksheet = new Worksheet("Sheet" + count);
worksheet.Cells.ColumnWidth[0] = 3200;
worksheet.Cells.ColumnWidth[1] = 10000;
worksheet.Cells.ColumnWidth[2] = 10000;
worksheet.Cells[0, 0] = new Cell("元件组合");
worksheet.Cells[1, 0] = new Cell(S2[0]);
worksheet.Cells[1, 1] = new Cell(S2[1]);
worksheet.Cells[2, 0] = new Cell(S3[0]);
worksheet.Cells[2, 1] = new Cell(S3[1]);
worksheet.Cells[3, 0] = new Cell("品号");
worksheet.Cells[3, 1] = new Cell("品名");
for (var i = 0; i < BomItem.Value.Count; i++)
{
worksheet.Cells[4 + i, 0] = new Cell(BomItem.Value[i].主件品号);
worksheet.Cells[4 + i, 1] = new Cell(BomItem.Value[i].品名);
}
workbook.Worksheets.Add(worksheet);
}
else
{
var worksheet = new Worksheet("Sheet" + count);
worksheet.Cells.ColumnWidth[0] = 3200;
worksheet.Cells.ColumnWidth[1] = 3200;
worksheet.Cells.ColumnWidth[2] = 3200;
worksheet.Cells[0, 0] = new Cell("元件组合");
worksheet.Cells[1, 0] = new Cell(S2[0]);
worksheet.Cells[1, 1] = new Cell(S2[1]);
worksheet.Cells[2, 0] = new Cell("品号");
worksheet.Cells[2, 1] = new Cell("品名");
for (var i = 0; i < BomItem.Value.Count; i++)
{
worksheet.Cells[3 + i, 0] = new Cell(BomItem.Value[i].主件品号);
worksheet.Cells[3 + i, 1] = new Cell(BomItem.Value[i].品名);
}
workbook.Worksheets.Add(worksheet);
}
count++;
}
workbook.Save(path);
}
LoadXls.Click += delegate
{
var OpenFile = new OpenFileDialog
{
Filter = @" Excel文件 | *.xls;*.xlsx",
Title = @"打开表格"
};
OpenFile.ShowDialog();
if (File.Exists(OpenFile.FileName))
{
ReadXls(OpenFile.FileName);
}
};
Handler.Click += delegate
{
// try
{
var 元件品号序列 = -1;
var 品名序列 = -1;
var 主件品号序列 = -1;
var DataGrid = ShowTab.SelectedTab.Controls[0] as DataGridView;
for (var i = 0; i < DataGrid.ColumnCount; i++)
{
switch (DataGrid.Columns[i].Name)
{
case "元件品号":
元件品号序列 = i;
break;
case "品 名":
品名序列 = i;
break;
case "主件品号":
主件品号序列 = i;
break;
}
}
var 品名 = "";
var TempBomInfo = new BomInfo();
var SaveInfo = new Dictionary <string, List <BomInfo> >();
foreach (DataGridViewRow item in DataGrid.Rows)
{
if (string.IsNullOrWhiteSpace(item.Cells[元件品号序列].Value.ToString()))
{
if (品名 != "")
{
if (SaveInfo.ContainsKey(品名))
{
SaveInfo[品名].Add(TempBomInfo);
}
else
{
SaveInfo.Add(品名, new List <BomInfo> {
TempBomInfo
});
}
}
TempBomInfo = new BomInfo
{
品名 = item.Cells[品名序列].Value.ToString(),
主件品号 = item.Cells[主件品号序列].Value.ToString()
};
品名 = "";
}
else
{
品名 += item.Cells[元件品号序列].Value + "|" + item.Cells[品名序列].Value + "&";
}
}
var SaveFile = new SaveFileDialog
{
Filter = @" Excel文件 | *.xls",
Title = @"保存表格"
};
SaveFile.ShowDialog();
if (!string.IsNullOrWhiteSpace(SaveFile.FileName))
{
SaveXls(SaveFile.FileName, SaveInfo);
}
}
/* catch (Exception)
* {
* }*/
};
// this.Close();
}
static public List <PdfPTable> PdfTableByNameOrMonth(List <IdParking> idParkings, FirstRow firstRow)
{
List <PdfPTable> pdfTablelist = new List <PdfPTable>();
var help = idParkings.OrderBy(x => x.year).ThenBy(x => x.month);
int from = 0;
if (idParkings.Count > 6)
{
from = idParkings.Count - idParkings.Count / 2;
pdfTablelist.Add(new PdfPTable(from));
pdfTablelist.Add(new PdfPTable(idParkings.Count / 2));
}
else
{
pdfTablelist.Add(new PdfPTable(idParkings.Count));
from = idParkings.Count;
}
int counter = 0;
switch (firstRow)
{
case FirstRow.Name:
{
counter = 0;
foreach (var carPark in help)
{
if (counter < from)
{
pdfTablelist[0].AddCell(SetCell(carPark.name));
counter++;
}
else
{
pdfTablelist[1].AddCell(SetCell(carPark.name));
}
}
counter = 0;
foreach (var carPark in help)
{
if (counter < from)
{
pdfTablelist[0].AddCell(SetCell(carPark.count.ToString()));
counter++;
}
else
{
pdfTablelist[1].AddCell(SetCell(carPark.count.ToString()));
}
}
return(pdfTablelist);
}
case FirstRow.Month:
{
counter = 0;
var month = idParkings.OrderBy(x => x.month);
foreach (var carPark in help)
{
if (counter < from)
{
pdfTablelist[0].AddCell(SetCell(Enum.GetName(typeof(EnumMonth), carPark.month)));
counter++;
}
else
{
pdfTablelist[1].AddCell(SetCell(Enum.GetName(typeof(EnumMonth), carPark.month)));
}
}
counter = 0;
foreach (var carPark in help)
{
if (counter < from)
{
pdfTablelist[0].AddCell(SetCell(carPark.count.ToString()));
counter++;
}
else
{
pdfTablelist[1].AddCell(SetCell(carPark.count.ToString()));
}
}
return(pdfTablelist);
}
default:
{
return(null);
}
}
}
protected override IEnumerable <IEnumerable <InlineKeyboardButton> > DefineInlineKeyboard()
{
return(FirstRow.DivideByLength(4).ToList());
}
public override string ToString() => Items.Length == 0 ? "(Empty)" : FirstRow.Trim();
/// <summary>
/// Returns a <see cref="System.String"/> that represents this instance.
/// </summary>
/// <returns>
/// A <see cref="System.String"/> that represents this instance.
/// </returns>
public new string ToString()
{
_stringBuilder.Append(activateCmdShell);
_stringBuilder.Append("'bcp ");
switch (DataSource)
{
case Keys.TAB_SQL:
_stringBuilder.Append(string.Format(@" ""{0}"" queryout ", Regex.Replace(SQLStatment, "(\n|\r)+", string.Empty)));
break;
case Keys.TAB_SP:
StringBuilder storedProcParams = new StringBuilder();
int index = 0;
foreach (var param in (MappingParams)StoredProcedureParameters)
{
storedProcParams.Append(string.Format("{0} {1}",
(index > 0)
? ","
: string.Empty,
(param.Type.ToLower().Contains("char") ||
param.Type.ToLower().Contains("date") ||
param.Type.ToLower().Contains("text"))
? string.Format("'{0}'", EvaluateExpression(param.Value, _variableDispenser))
: EvaluateExpression(param.Value, _variableDispenser)));
index++;
}
_stringBuilder.Append(string.Format(@" ""exec {0}.{1} {2}"" queryout ", Database.Trim(), StoredProcedure.Trim(), storedProcParams));
break;
case Keys.TAB_VIEW:
_stringBuilder.Append(string.Format(@" ""{0}.{1}"" out ", Database.Trim(), View.Trim()));
break;
case Keys.TAB_TABLES:
_stringBuilder.Append(string.Format(@" ""{0}.{1}"" out ", Database.Trim(), Tables.Trim()));
break;
}
_stringBuilder.Append(DestinationByFileConnection.Trim() == Keys.TRUE
? string.Format(@" ""{0}"" ", _connection[DestinationPath].ConnectionString)
: string.Format(@" ""{0}"" ", EvaluateExpression(DestinationPath, _variableDispenser)));
string srvVal = (from srv in _connection[SQLServerInstance].ConnectionString.Split(';')
where srv.Contains("Data Source")
select srv).FirstOrDefault();
if (srvVal != null)
{
_stringBuilder.Append(string.Format(@" -S""{0}""", srvVal.Split('=')[1]));
}
_stringBuilder.Append(TrustedConnection.Trim() == Keys.TRUE
? " -T "
: string.Format(@" -U""{0}"" -P""{1}"" ",
EvaluateExpression(Login, _variableDispenser),
EvaluateExpression(Password, _variableDispenser)));
//if (!string.IsNullOrEmpty(Database))
//{
// _stringBuilder.Append(string.Format(" -d [{0}]", Database));
//}
if (!string.IsNullOrEmpty(FirstRow.Trim()))
{
_stringBuilder.Append(string.Format(" -F{0}", FirstRow));
}
if (!string.IsNullOrEmpty(LastRow.Trim()))
{
_stringBuilder.Append(string.Format(" -L{0}", LastRow));
}
if (!string.IsNullOrEmpty(MaxErrors.Trim()))
{
_stringBuilder.Append(string.Format(" -m{0}", MaxErrors));
}
if (NativeDatabaseDataType == Keys.TRUE)
{
_stringBuilder.Append(" -N ");
}
if (!string.IsNullOrEmpty(FormatFile.Trim()))
{
_stringBuilder.Append(FormatFileByFileConnection == Keys.TRUE
? string.Format(@" -f""{0}"" ", _connection[FormatFile].ConnectionString)
: string.Format(@" -f""{0}"" ", EvaluateExpression(FormatFile, _variableDispenser)));
}
//else
//{
// _stringBuilder.Append(" -c ");
//}
if (!string.IsNullOrEmpty(FieldTermiantor.Trim()))
{
_stringBuilder.Append(string.Format(" -t{0} ", FieldTermiantor));
}
if (!string.IsNullOrEmpty(RowTermiantor.Trim()))
{
_stringBuilder.Append(string.Format(" -r{0} ", RowTermiantor));
}
if (!string.IsNullOrEmpty(CodePage.Trim()))
{
_stringBuilder.Append(string.Format(" -C{0} ", CodePage));
}
if (!string.IsNullOrEmpty(NetworkPacketSize.Trim()))
{
_stringBuilder.Append(string.Format(" -a{0} ", NetworkPacketSize));
}
if (UseRegionalSettings == Keys.TRUE)
{
_stringBuilder.Append(" -R ");
}
if (SET_QUOTED_IDENTIFIERS_ON == Keys.TRUE)
{
_stringBuilder.Append(" -q ");
}
if (UseCharacterDataType == Keys.TRUE)
{
_stringBuilder.Append(" -c ");
}
if (UseUnicodeCharacters == Keys.TRUE)
{
_stringBuilder.Append(" -w ");
}
_stringBuilder.Append("'");
return(_stringBuilder.ToString());
}
