/// <summary>
/// 对Mysql数据库进行操作
/// 生成以Json格式的字符串，并生成二维码，存储在mysql文件夹下的json.png中
/// </summary>
/// <param name="str">连接数据库所需字符串</param>
/// <param name="command">对数据库操作的命令</param>
public static void MysqlToJsonQrcode(string str, string command)
{
var conn = new MySqlConnection(str);
MySqlCommand mySql = new MySqlCommand(command, conn);
conn.Open();
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mySql);
DataSet ds = new DataSet();
mySqlDataAdapter.Fill(ds);
if (ds.Tables.Count > 0)
{
int i = 0;
while (i < ds.Tables.Count)
{
System.Data.DataTable st = ds.Tables[i];
String str1 = TxtToQrcode.DataTableToJson(st);
if (!Directory.Exists("mysql"))
{
Directory.CreateDirectory("mysql");
}
String s = string.Format("mysql\\json{0}.png", i);
TxtToQrcode.StringToPng(str1, s);
i++;
}
}
Console.WriteLine("已完成将Mysql数据库查询信息转换为Json字符串并存入{0}文件夹",
System.Environment.CurrentDirectory + "\\mysql");
}
/// <summary>
/// 对Mysql数据库进行操作
/// 生成以Xml格式的字符串，并生成二维码，存储在mysql文件夹下的xml.png中
/// </summary>
/// <param name="str">连接数据库所需字符串</param>
/// <param name="command">对数据库操作的命令</param>
public static void MysqlToxmlQrcode(String str, String command)
{
MySqlConnection sqlConnection = new MySqlConnection(str);
MySqlCommand sqlCommand = new MySqlCommand(command, sqlConnection);
sqlConnection.Open();
MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sqlCommand);
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds);
String xml = ds.GetXml().ToString();
var s1 = new StringBuilder();
for (int i = 0; i < xml.Length - 1; i++)
{
if (xml[i] == '>' && xml[i + 1] == '<')
{
s1.Append('\n');
}
s1.Append(xml[i]);
}
s1.Append(xml[xml.Length - 1]);
s1.Append('\n');
xml = s1.ToString();
String fileName = "mysql";
if (!Directory.Exists(fileName))
{
Directory.CreateDirectory(fileName);
}
TxtToQrcode.StringToPng(xml, fileName + "\\xml.png");
Console.WriteLine("已完成将Mysql数据库查询信息转换为Xml字符串并存入{0}文件夹",
System.Environment.CurrentDirectory + "\\" + fileName);
}
/// <summary>
///将Excel文件中的字符串转化成QRCode,以png文件格式存储
///这种处理Excel文件的方法需要在系统上安装AccessDatabaseEngine.exe文件
///因此，这种写法兼容性不太好
///这是将Excel文件变成XML格式的字符串，然后将其变成Qrcode
/// </summary>
/// <param name="path"></param>
public static void ExcelToXMLToQrcode(String path)
{
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet("dataset");
System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
String tableName = "dh";
tableName = schemaTable.Rows[0][2].ToString().Trim();
string strExcel = "select * from[" + tableName + "]";
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(strExcel, conn);
oleDbDataAdapter.Fill(ds);
StringWriter sr = new StringWriter();
XmlTextWriter xw = new XmlTextWriter(sr);
ds.WriteXml(xw);
String s = sr.ToString();
String str = string.Empty;
for (int i = 0; i < s.Length - 1; i++)
{
str += s[i];
if (s[i] == '>' && s[i + 1] == '<')
{
str += "\n";
}
}
str += s[s.Length - 1];
//摘要：
// 在Excel文件所在目录下，创建excelToPng.png的文件
// 存储Qrcode
String dir = path.Substring(0, path.LastIndexOf('\\')) + @"\excel";
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
String filename = dir + @"\xml.png";
TxtToQrcode.StringToPng(str, filename);
Console.WriteLine("已完成将Excel信息转换为Xml字符串并存入{0}文件夹", dir);
}
// 摘要：
// 将str字符串转化成QRCode,以png文件格式存储
public static void TxtToPNG(string path)
{
StreamReader reader = new StreamReader(path);
string dir = null;
int line = 1;
// 摘要：
// 读入一行，将生成的二维码存储在bmp文件中
while (reader.Peek() > 0)
{
String str = reader.ReadLine();
//摘要：
// 判断字符串是否符合生成二维码的要求
if (str.Length < 4)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("第{0}行字符串长度必须大于四", line);
Console.ResetColor();
continue;
}
else
{
//摘要：
// 保存文件路径
dir = path.Substring(0, path.LastIndexOf('\\')) + @"\txt";
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
String fileName = dir + "\\" + line.ToString("000") + str.Substring(0, 4) + ".png";
TxtToQrcode.StringToPng(str, fileName);
}
line++;
}
Console.WriteLine("已完成将txt文本信息转换为Qrcode并存入{0}文件夹", dir);
}
static void Main(String[] args)
{
try
{
if (args.Length == 0)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("请传入相关参数");
Console.ResetColor();
return;
}
int k = 0;
//摘要:
// method判断将二维码输出到控制台还是保存在文件中
bool method = false;
for (int i = 0; i < args.Length; i++)
{
if (args[i] == "-f")
{
k = i + 1;
method = true;
if (args.Length <= k)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("请输入文件名！");
Console.ResetColor();
return;
}
}
}
//摘要：
// 判断输入的文件是Excel,txt
//
if (method)
{
String[] str = args[k].Split('.');
String last = str[str.Length - 1];
if (last == "txt")
{
try
{
TxtToQrcode.TxtToPNG(args[k]);
}catch (Exception)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("找不到文件！");
Console.ResetColor();
return;
}
}
else if (last == "xlsx")
{
try
{
TxtToQrcode.ExcelToJsonToQrcode(args[k]);
TxtToQrcode.ExcelToXMLToQrcode(args[k]);
}
catch (Exception)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("找不到文件！");
Console.ResetColor();
return;
}
}
else if (args[k] == "mysql")
{
k = k + 2;
if (k >= args.Length)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("请输入连接字符串与查询语句！");
Console.ResetColor();
return;
}
try
{
TxtToQrcode.MysqlToxmlQrcode(args[k - 1], args[k]);
TxtToQrcode.MysqlToJsonQrcode(args[k - 1], args[k]);
}
catch (Exception)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("连接字符串或查询语句错误！");
Console.ResetColor();
return;
}
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("不支持的文件格式！！！");
Console.ResetColor();
return;
}
}
//摘要:
// 输出到控制台中
// 将第二个命令行参数作为要生成二维码的信息
else
{
try
{
TxtToQrcode.StringToConsole(args[1]);
} catch (Exception)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("请输入要生成二维码的字符串");
Console.ResetColor();
return;
}
}
}
catch (Exception)
{
Console.WriteLine("参数错误");
}
}
/// <summary>
/// 将Excel文件中的数据以Json格式转换成字符串，
/// 编码成QrCode
/// 存储在json文件夹下的png图像中
/// 本次使用Microsoft.Office.Interop.Encoding的扩展
/// </summary>
/// <param name="path">Excel文件目录</param>
public static void ExcelToJsonToQrcode(String path)
{
object oMisiog = System.Reflection.Missing.Value;
//摘要：
// 创建Excel应用程序
Application application = new Application();
//摘要：
// 从Excel文件中读取工作簿，并创建对象
Workbook workbook = application.Workbooks.Open(path, oMisiog, oMisiog, oMisiog, oMisiog, oMisiog, oMisiog, oMisiog
, oMisiog, oMisiog, oMisiog, oMisiog, oMisiog, oMisiog, oMisiog);
//摘要：
// 创建工作表集合
Sheets sheets = workbook.Worksheets;
//摘要：
// 获取工作表1
Worksheet worksheet = (Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return;
}
String cellContent;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
Range range;
//摘要：
// 负责列头Start
DataColumn dc;
int columnID = 1;
range = worksheet.Cells[1, 1];
System.Data.DataTable dt = new System.Data.DataTable();
while (range.Text.ToString().Trim() != "")
{
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = range.Text.ToString().Trim();
dt.Columns.Add(dc);
range = worksheet.Cells[1, ++columnID];
}
//摘要：
// 从将每一行的信息写入DataTable中
for (int i = 2; i <= iRowCount; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= iColCount; j++)
{
range = worksheet.Cells[i, j];
cellContent = range.Value2 == null? " ":range.Text.ToString().Trim();
dr[j - 1] = cellContent;
}
dt.Rows.Add(dr);
}
//摘要：
// 释放对象所占内存
workbook.Close(false, oMisiog, oMisiog);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
application.Workbooks.Close();
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
application = null;
//摘要：
// 将DataTable数据转换成Json格式字符串
String s = TxtToQrcode.DataTableToJson(dt);
String dir = path.Substring(0, path.LastIndexOf('\\')) + "\\excel";
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
String fileName = dir + @"\json.png";
TxtToQrcode.StringToPng(s, fileName);
Console.WriteLine("已完成将Excel文件信息转换为Json字符串并存入{0}文件夹", dir);
}
