public static void RenamePdf(TicketItem ticket, String filePath)
{
string filename = String.Format("{0}-{1}-{2}-{3}.pdf", ticket.Date,
ticket.Sum.Substring(1), ticket.Number, ticket.Company);
//保存Excel文件
string newfilepath = AppDomain.CurrentDomain.BaseDirectory + filename;
FileInfo fi = new FileInfo(filePath);
//fi.MoveTo(newfilepath);
if (!System.IO.File.Exists(newfilepath))
{
fi.CopyTo(newfilepath);
}
}
public static void CreateSinglelExcel(TicketItem ticket)
{
using (ExcelPackage excelPackage = new ExcelPackage())
{
//设置Excel文档的某些属性
excelPackage.Workbook.Properties.Author = "QOTOM";
excelPackage.Workbook.Properties.Title = "发票数据";
excelPackage.Workbook.Properties.Created = DateTime.Now;
//创建工作表
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add("发票数据");
workSheet.Cells[1, 1].Value = "发票代码";
workSheet.Cells[1, 2].Value = "发票号码";
workSheet.Cells[1, 3].Value = "开票日期";
workSheet.Cells[1, 4].Value = "货品名称和型号";
workSheet.Cells[1, 5].Value = "总金额";
workSheet.Cells[1, 6].Value = "公司名称";
workSheet.Cells[2, 1].Value = ticket.Code;
workSheet.Cells[2, 2].Value = ticket.Number;
workSheet.Cells[2, 3].Value = ticket.Date;
workSheet.Cells[2, 4].Value = ticket.Project.ToString();
workSheet.Cells[2, 5].Value = ticket.Sum;
workSheet.Cells[2, 6].Value = ticket.Company;
//自适应宽度
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
//workSheet.Row(1).CustomHeight = true;//自动换行
workSheet.Column(4).Style.WrapText = true;
string filename = String.Format("{0}-{1}-{2}-{3}.xlsx", ticket.Date,
ticket.Sum.Substring(1), ticket.Number, ticket.Company);
//保存Excel文件
string filePath = AppPath + filename;
Console.WriteLine("save to {0}", filePath);
FileInfo fi = new FileInfo(filePath);
excelPackage.SaveAs(fi);
}
}
public static void AddData2Excel(TicketItem ticket)
{
int row = 1;
int cell = 1;
string filePath = AppPath + ExcelNamed;
//创建一个列表以保存所有值
List <string> excelData = new List <string>();
byte[] bin = new byte[1024];
//读取Excel文件为字节数组
try
{
bin = File.ReadAllBytes(filePath);
}
catch (Exception)
{
Application.Current.Dispatcher.Invoke(DispatcherPriority.Normal, new Action(() =>
{
MessageBox.Show("添加数据失败，请先关闭" + ExcelNamed, "提示",
MessageBoxButton.OK, MessageBoxImage.Information);
}));
return;
}
//byte[] bin = File.ReadAllBytes(Server.MapPath("test.xlsx"));
//在内存流中创建一个新的Excel包
using (MemoryStream stream = new MemoryStream(bin))
using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
//循环所有工作表
foreach (ExcelWorksheet worksheet in excelPackage.Workbook.Worksheets)
{
//循环所有行
for (int i = worksheet.Dimension.Start.Row; i <= worksheet.Dimension.End.Row; i++)
{
//循环每列
for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
{
//将单元格数据添加到列表
if (worksheet.Cells[i, j].Value != null)
{
excelData.Add(worksheet.Cells[i, j].Value.ToString());
}
}
}
}
}
if (excelData.IndexOf(ticket.Number) > 0)
{
Application.Current.Dispatcher.Invoke(DispatcherPriority.Normal, new Action(() =>
{
MessageBox.Show("发票重复", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
}));
return;
}
//添加数据到最后一行
using (MemoryStream stream = new MemoryStream(bin))
using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[0];
row = worksheet.Dimension.End.Row;
cell = worksheet.Dimension.End.Column;
Console.WriteLine("max row:{0} max cell:{1}", row, cell);
//将单元格数据添加到列表
worksheet.Cells[row + 1, 1].Value = ticket.Code;
worksheet.Cells[row + 1, 2].Value = ticket.Number;
worksheet.Cells[row + 1, 3].Value = ticket.Date.Insert(4, "年").Insert(7, "月").Insert(10, "日");
worksheet.Cells[row + 1, 4].Value = ticket.Project.ToString();
worksheet.Cells[row + 1, 5].Value = ticket.Sum;
worksheet.Cells[row + 1, 6].Value = ticket.Company;
//excelPackage.Save();
FileInfo fi = new FileInfo(filePath);
excelPackage.SaveAs(fi);
}
}
private void OpenFileClick(object sender, RoutedEventArgs e)
{
row = 0;
ticket = null;
Array.Clear(arr, 0, arr.Length);
pro_list.Clear();
content.Remove(0, content.Length);
if (!isDrop)
{
var openFileDialog = new Microsoft.Win32.OpenFileDialog
{
Filter = "Pdf Files (*.pdf)|*.pdf"
};
if (openFileDialog.ShowDialog() == true)
{
fileName = openFileDialog.FileName;
this.text_file_name.Text = fileName;
}
else
{
return;
}
}
try
{
content = PdfFormat.GetFdfText(fileName);
//Console.WriteLine(content.ToString());
if (content.Length == 0)
{
MessageBox.Show("...发票!发票!发票!", "提示");
return;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
//StreamWriter sw = File.AppendText(@"C:\\Users\\16838\\Desktop\\test2323.txt");
//sw.Write(content.ToString());
//sw.Flush();
//sw.Close();
String[] contentArry = content.ToString().Split(new string[] { "\r\n" },
StringSplitOptions.RemoveEmptyEntries);
if (contentArry.Length == 0)
{
Console.WriteLine("split failed!");
return;
}
foreach (String str in contentArry)
{
//Console.WriteLine("{0}, {1}", row, str);
if (str.IndexOf("发票代码") > -1)
{
index_code = row;
}
else if (str.IndexOf("发票号码") > -1)
{
index_number = row;
}
else if (str.IndexOf("开票日期") > -1)
{
index_date = row;
}
//这里需要知道货品的行数，用规格型号行和合计行推算出来
else if (str.IndexOf("规格型号") > -1)
{
var start = row;
arr[0] = start;
Console.WriteLine("start----------" + start);
}
//合计行容易冲突，这里用价税合计代替，行数要-1
else if (str.IndexOf("价税合计") > -1)
{
index_sum = row;
var end = row;
arr[1] = end - 1;
Console.WriteLine("end----------" + end);
}
else if (str.LastIndexOf("称:") > -1)
{
index_name = row;
Console.WriteLine("index_name----------" + index_name);
}
row++;
}
for (int i = arr[0] + 1; i < arr[1]; i++)
{
pro_list.Add(i);
}
//Console.WriteLine("index_code:{0} index_number:{1} index_date:{2} index_name:{3}",
// index_code, index_number, index_date, index_name);
ticket = PdfFormat.GetTicketItem(contentArry, arr, pro_list);
Console.WriteLine(ticket.ToString());
PdfFormat.RenamePdf(ticket, fileName);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
//ExcelFormat.CreateSinglelExcel(ticket);
string filePath = AppDomain.CurrentDomain.BaseDirectory + ExcelFormat.ExcelNamed;
if (File.Exists(filePath))
{
ExcelFormat.AddData2Excel(ticket);
}
else
{
ExcelFormat.CreateNewExcel(ticket);
}
isDrop = false;
}
public static TicketItem GetTicketItem(String[] strArr, int[] arr, List <int> pro_list)
{
TicketItem item = new TicketItem();
//第一行发票代码
string code = "发票代码";
string tickets_code = strArr[MainWindow.index_code].Substring(strArr[MainWindow.index_code].IndexOf(code) + code.Length + 1);
//Console.WriteLine(tickets_code);
//第二行发票号码
string number = "发票号码";
string tickets_number = strArr[MainWindow.index_number].Substring(strArr[MainWindow.index_number].IndexOf(number) + number.Length + 1);
//Console.WriteLine(tickets_number);
//第三行开票日期
string date = "开票日期";
string result = strArr[MainWindow.index_date].Substring(strArr[MainWindow.index_date].IndexOf(date) + date.Length + 1);
string date_str = System.Text.RegularExpressions.Regex.Replace(result, @"[^0-9]+", "");
string tickets_date = date_str.Insert(4, "年").Insert(7, "月").Insert(10, "日");
//Console.WriteLine(tickets_date);
item.Code = tickets_code;
item.Number = tickets_number;
item.Date = date_str;
var product = new StringBuilder();
foreach (var index in pro_list)
{
//整行货品数据
String content = strArr[index];
Console.WriteLine("货品：" + content);
String[] project_info = { "货品名称 型号" };
//有时候解析出的行全是空格，这里要做下非空判断
String check = Regex.Replace(content, @"\s", "");
if (check.Length > 0)
{
//用空行切割数据
project_info = content.ToString().Split(new string[] { " " },
StringSplitOptions.RemoveEmptyEntries);
//Console.WriteLine("project Length：" + project_info.Length + " check:"+ check);
var str = string.Join(" ", project_info);
//Console.WriteLine("str---------->"+ str);
try
{
//if (project_info.Length > 1)
//{
// product.Append(project_info[0]).Append(" ").Append(project_info[1]).Append("\r\n");
//}
//else
//{
// product.Append(project_info[0]).Append("\r\n");
//}
//货品数据数据各pdf规格太乱，不再特意去添加规格型号
if (project_info.Length > 0)
{
product.Append(str).Append("\r\n");
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
MessageBox.Show(e.ToString(), "错误提示", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
}
if (product.Length > 2)
{
product.Remove(product.Length - 2, 2);
}
//Console.WriteLine(product);
item.Project = product;
string sum = "小写";
string tickets_sum = strArr[MainWindow.index_sum].Substring(strArr[MainWindow.index_sum].IndexOf(sum));
if (strArr[MainWindow.index_sum].IndexOf("￥") > -1)
{
tickets_sum = strArr[MainWindow.index_sum].Substring(strArr[MainWindow.index_sum].IndexOf("￥"));
}
else if (strArr[MainWindow.index_sum].IndexOf("¥") > -1)
{
tickets_sum = strArr[MainWindow.index_sum].Substring(strArr[MainWindow.index_sum].IndexOf("¥"));
}
//string rex = "名 称";
String temp_company = strArr[MainWindow.index_name].Replace("：", ":");
string tickets_company = temp_company.Split(':')[1].Trim().Split(' ')[0];
//Console.WriteLine(tickets_company);
item.Sum = tickets_sum;
item.Company = tickets_company;
return(item);
}
