using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
namespace GenerateExcel
{
public partial class Form1 : Form
{
string[] m_bigmoney = { "零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖", "圆", "拾", "佰", "仟", "万", "拾", "佰", "仟", "亿", "角", "分", "整" };
public Form1()
{
InitializeComponent();
this.textBox2.Text = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
//convertTobigMoney("510.5");
}
private void btnOpen_Click(object sender, EventArgs e)
{
if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
{
string t_filename = this.openFileDialog1.FileName;
if (Path.GetExtension(t_filename) == ".xls" || Path.GetExtension(t_filename) == ".xlsx")
{
this.textBox1.Text = t_filename;
}
else {
MessageBox.Show("请导入Excel文件");
}
}
}
private void btnSetPath_Click(object sender, EventArgs e)
{
if (this.folderBrowserDialog1.ShowDialog() == DialogResult.OK)
{
string t_path = this.folderBrowserDialog1.SelectedPath;
this.textBox2.Text = t_path;
}
}
private List<srcModel> getSrcList()
{
List<srcModel> t_lst = new List<srcModel>();
if (!string.IsNullOrEmpty(this.textBox1.Text) &&
!string.IsNullOrEmpty(this.textBox2.Text))
{
object missing = System.Reflection.Missing.Value;
Excel.Application t_excel = new Excel.Application();
if (t_excel == null)
{
MessageBox.Show("创建Excel失败，请先检查是否有安装Excel");
}
else
{
t_excel.Visible = false;
t_excel.UserControl = true;
Excel.Workbook t_book = t_excel.Application.Workbooks.Open(this.textBox1.Text, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
Excel.Worksheet t_sheet = (Excel.Worksheet)t_book.Worksheets.get_Item(1);
int t_rows = t_sheet.UsedRange.Cells.Rows.Count;
Excel.Range t_Arange = t_sheet.Cells.get_Range("A1", "A" + t_rows.ToString());
Excel.Range t_Brange = t_sheet.Cells.get_Range("B1", "B" + t_rows.ToString());
object[,] t_Aarr = (object[,])t_Arange.Value2;
object[,] t_Barr = (object[,])t_Brange.Value2;
for (int i = 1; i <= t_rows; ++i)
{
srcModel t_sm = new srcModel();
t_sm.Message = t_Aarr[i, 1].ToString();
t_sm.Money = t_Barr[i, 1].ToString();
t_lst.Add(t_sm);
}
}
return t_lst;
}
else
{
MessageBox.Show("源文件和目标路径不能为空！");
return t_lst;
}
}
private void btnGener_Click(object sender, EventArgs e)
{
List<srcModel> t_lst = getSrcList();
getWorkSheet(t_lst);
//generDescExcel(t_lst, t_sheet);
}
private void generDescExcel(List<srcModel> p_lst, Excel.Worksheet p_sheet)
{
object missing = System.Reflection.Missing.Value;
Excel.Application t_excel = new Excel.Application();
Excel.Workbook t_book = t_excel.Workbooks.Add(true);
Excel.Range t_range = p_sheet.Cells[1, 1] as Excel.Range;
t_range.Value2 = "xxxxxxxxxx";
//t_book.Worksheets.
//t_book.Worksheets.Add();
Excel.Worksheet t_sheet = t_book.ActiveSheet;
t_sheet.Copy(missing, t_sheet);
t_sheet.Copy(missing, t_sheet);
t_excel.Visible = true;
//t_book.SaveAs(this.textBox2.Text + "\\" + "xxxx.xls");
}
private void getWorkSheet(List<srcModel> p_lst)
{
try
{
Excel.Worksheet t_sheet = null;
if (!string.IsNullOrEmpty(this.textBox3.Text))
{
object missing = System.Reflection.Missing.Value;
Excel.Application t_excel = new Excel.Application();
if (t_excel == null)
{
MessageBox.Show("创建Excel失败，请先检查是否有安装Excel");
}
else
{
t_excel.Visible = false;
t_excel.UserControl = true;
Excel.Workbook t_book = t_excel.Application.Workbooks.Open(this.textBox3.Text, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
for (int i = 0; i < p_lst.Count; ++i)
{
t_sheet = (Excel.Worksheet)t_book.Worksheets.get_Item(i + 1);
t_sheet.Copy(missing, t_sheet);
Excel.Range t_range1 = t_sheet.Cells[6, 1] as Excel.Range;
t_range1.Value2 = p_lst[i].Message;
Excel.Range t_range2 = t_sheet.Cells[3, 1] as Excel.Range;
t_range2.Value2 = "收报单位：" + p_lst[i].Message.Substring(2, p_lst[i].Message.IndexOf("中支"));
Excel.Range t_range3 = t_sheet.Cells[10, 5] as Excel.Range;
t_range3.Value2 = convertTobigMoney(p_lst[i].Money);
//string t_small = p_lst[i].Money.Remove(p_lst[i].Money.IndexOf('.'), 1);
if (p_lst[i].Money.IndexOf('.') == -1)
{
char[] t_sArr = p_lst[i].Money.ToCharArray();
Excel.Range t_range4 = t_sheet.Cells[6, 16] as Excel.Range;
t_range4.Value2 = "0";
t_range4 = t_sheet.Cells[6, 15] as Excel.Range;
t_range4.Value2 = "0";
int j = 14;
for (int k = t_sArr.Length - 1; k >= 0; k--)
{
t_range4 = t_sheet.Cells[6, j] as Excel.Range;
t_range4.Value2 = t_sArr[k].ToString();
j--;
}
}
else
{
string t_orginStr = p_lst[i].Money.Split('.')[1].ToCharArray().Length == 1 ? p_lst[i].Money + "0" : p_lst[i].Money;
char[] t_sArr = t_orginStr.Remove(t_orginStr.IndexOf('.'), 1).ToCharArray();
int j = 16;
for (int k = t_sArr.Length - 1; k >= 0; k--)
{
Excel.Range t_range4 = t_sheet.Cells[6, j] as Excel.Range;
t_range4.Value2 = t_sArr[k].ToString();
j--;
}
}
}
t_excel.Visible = true;
}
//return t_sheet;
}
else
{
MessageBox.Show("模板文件不能为空！");
//return t_sheet;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
private string convertTobigMoney(string p_mon)
{
string t_result = string.Empty;
try
{
string[] t_mArr = p_mon.Split('.');
if (t_mArr.Length > 1 && !string.IsNullOrEmpty(t_mArr[1]))
{
char[] t_afterPoint = t_mArr[1].ToCharArray();
t_result = m_bigmoney[Convert.ToInt32(t_afterPoint[0].ToString())] + m_bigmoney[16];
if (t_afterPoint.Length > 1)
{
t_result = t_result+ m_bigmoney[Convert.ToInt32(t_afterPoint[1].ToString())] + m_bigmoney[17];
}
}
if (t_result.IndexOf("分") == -1)
{
t_result = t_result + "整";
}
//-----------------------------------------------------------------
char[] t_beforePoint = t_mArr[0].ToCharArray();
int t_length = t_beforePoint.Length;
int t_unitIndex = 10;
string t_befResult = string.Empty;
for (int j = t_length - 1; j >= 0; --j )
{
if (t_beforePoint[j] == '0')
{
if (!string.IsNullOrEmpty(t_befResult))
{
t_befResult = "零" + t_befResult;
}
else
{
t_befResult = "圆";
}
}
else {
t_befResult = m_bigmoney[Convert.ToInt32(t_beforePoint[j].ToString())] + m_bigmoney[t_unitIndex] + t_befResult;
}
t_unitIndex++;
}
t_result = t_befResult + t_result;
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//return string.Empty;
}
return t_result;
}
private void button1_Click(object sender, EventArgs e)
{
if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
{
string t_filename = this.openFileDialog1.FileName;
if (Path.GetExtension(t_filename) == ".xls" || Path.GetExtension(t_filename) == ".xlsx")
{
this.textBox3.Text = t_filename;
}
else
{
MessageBox.Show("请导入Excel文件");
}
}
}
}
}
