xls文件一键转csv
都忘记了有多久没有更新了,时间过得好快,难忘的2020已经过去了5天了。2021从新开始
这类型的插件之前就写过的,当时没有保留,突然项目需要。就回去从新找,找不到了。只能从新写 了,害怕以后需要又找不到,于是就记录在这里吧 。
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
namespace ConsoleExcelParserF4
{
class Program
{
static string configPath = null; //配置路径
static public string writeClent = null; //客户端 写入路劲
static public string writeServer = null; //服务器 写入路劲
static public string readPath = null; // 读取路径
static void Main(string[] args)
{
string str = System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName; //当前路径
readPath = str + "/../../../../../../" + "/ExcelConfig/excel";
writeClent = str + "/../../../../../../" + "/ExcelConfig/client_csv";
writeServer = str + "/../../../../../../" + "/ExcelConfig/server_csv";
if (System.IO.Directory.Exists(writeClent) == false)//如果不存在就创建file文件夹
{
System.IO.Directory.CreateDirectory(writeClent);
if (System.IO.Directory.Exists(writeClent) == false)
{
Console.WriteLine("路径错误>>>>> " + writeClent);
Console.ReadKey();
return;
}
}
if (System.IO.Directory.Exists(writeServer) == false)//如果不存在就创建file文件夹
{
System.IO.Directory.CreateDirectory(writeServer);
if (System.IO.Directory.Exists(writeServer) == false)
{
Console.WriteLine("路径错误>>>>> " + writeServer);
Console.ReadKey();
return;
}
}
if (System.IO.Directory.Exists(readPath) == false)//读取配置文件
{
InitPatnCfg();
if (System.IO.Directory.Exists(readPath) == false)
{
Console.WriteLine("路径错误>>>>> " + readPath);
Console.ReadKey();
return;
}
}
startRun();
}
static public void startRun()
{
Console.WriteLine("==================== 开始 ====================");
int successesNum = 0;
var files = Directory.GetFiles(readPath);
foreach (var file in files)
{
var ext = Path.GetExtension(file);
if (file.IndexOf("~$") != -1) // 缓存数据过滤
{
continue;
}
if (ext.Equals(".xls") || ext.Equals(".xlsx") || ext.Equals("xlsm"))
{
IWorkbook workbook = null;
using (var stream = File.OpenRead(file))
{
if (ext.Equals(".xlsx") || ext.Equals(".xlsm"))
workbook = new XSSFWorkbook(stream);
else if (ext.Equals(".xls"))
workbook = new HSSFWorkbook(stream);
var vSheetCount = workbook.NumberOfSheets;
if (vSheetCount == 0)
{
Console.WriteLine("无法正确读取 : {0}", file);
Console.ReadKey();
return;
}
int curTableIndex = 0;
while (curTableIndex < vSheetCount)
{
ISheet worksheet = workbook.GetSheetAt(curTableIndex);
if (worksheet.SheetName.ToLower().Contains("sheet"))
{
curTableIndex++;
continue;
}
DataTable tb = ExlseToObj(worksheet, worksheet.SheetName, 1);
string txtFile = worksheet.SheetName + ".csv";
dataTableToCsv(tb, writeClent + "/" + txtFile);
DataTable tb1 = ExlseToObj(worksheet, worksheet.SheetName, 2);
string txtFil1e = worksheet.SheetName + ".csv";
dataTableToCsv(tb, writeServer + "/" + txtFile);
curTableIndex++;
successesNum++;
}
}
}
}
Console.WriteLine("转化已完成 转化个数{0}", successesNum);
Console.WriteLine("==================== 结束 ====================");
}
// 初始化文件配置
static public bool InitPatnCfg()
{
configPath = System.Environment.CurrentDirectory + "\\config"; //配置路径
string strJson = File.ReadAllText(configPath + "\\pathCfg.txt", Encoding.UTF8);
if (null != strJson)
{
string[] strPaths = strJson.Split(',');
if (strPaths.Length == 4 || strPaths.Length == 5)
{
string[] strs1 = strPaths[0].Split('=');
//writeClent = strs1[1].Replace(" ", "");
string oldReadPath = readPath;
string[] strs2 = strPaths[1].Split('=');
readPath = strs2[1].Replace(" ", ""); ;
Console.WriteLine("路径错误>>>>> " + oldReadPath + " 读取配置路径 >>>>>> " + readPath);
return true
;
}
return false;
}
else
{
return false;
}
}
public static DataTable ExlseToObj(ISheet worksheet, string name, int type)
{
int newColums = worksheet.GetRow(1).LastCellNum;
var columns = worksheet.GetRow(0).LastCellNum;
m_cToS_list.Clear();
IRow row = worksheet.GetRow(3);
for (var j = 0; j < columns; j++)
{
if (row.GetCell(j) != null)
{
string tmp = row.GetCell(j).ToString();
m_cToS_list.Add(tmp.ToLower().Trim());
}
else
{
m_cToS_list.Add("");
}
}
DataTable tb = new DataTable();
tb.TableName = name;
if (newColums != columns)
{
Console.WriteLine("数据表出错!!!! 第一行和第二行不匹配" + name);
Console.ReadKey();
return null;
}
dicsRepeatField.Clear();
row = worksheet.GetRow(1);
for (var j = 0; j < columns; j++)
{
if (row.GetCell(j) != null)
{
var dc = new DataColumn();
string tmp = row.GetCell(j).ToString();
if (string.IsNullOrEmpty(tmp))
{
Console.WriteLine("基础类型不能为空!!!" + tmp + " >>> " + name);
Console.ReadKey();
}
if (IsRepeatField(tmp, name) == false)
{
dc.ColumnName = GetBasicType(tmp.ToLower().Trim());
tb.Columns.Add(dc);
}
}
}
// 增加内容
DataRow dr = tb.NewRow();
row = worksheet.GetRow(0);
for (var j = 0; j < columns; j++)
{
if (IsNeedWruite(j, type) == false)
{
continue;
}
string tmp = row.GetCell(j).ToString();
if (IsBasicType(tmp) == false)
{
Console.WriteLine("数据表出错!!!! 基本类型错误" + tmp + " >>> " + name);
Console.ReadKey();
}
dr[j] = GetBasicType(tmp.ToLower().Trim());
}
tb.Rows.Add(dr);
dr = tb.NewRow();
row = worksheet.GetRow(1);
for (var j = 0; j < columns; j++)
{
if (IsNeedWruite(j, type) == false)
{
continue;
}
string tmp = row.GetCell(j).ToString();
dr[j] = tmp.ToLower().Trim();
}
tb.Rows.Add(dr);
DataExtractionToDataTable(worksheet, type, ref tb);
tb.AcceptChanges();
return tb;
}
static void DataExtractionToDataTable(ISheet sheet, int type, ref DataTable tb)
{
IRow firstRow = sheet.GetRow(0);
int rowMax = firstRow.LastCellNum;
for (int i = 4; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row.GetCell(0) == null)
continue;
string sn = row.GetCell(0).ToString();
if (string.IsNullOrEmpty(sn))
continue;
DataRow dr = tb.NewRow();
for (int j = 0; j < rowMax; j++)
{
if (IsNeedWruite(j, type) == false)
{
continue;
}
string tmp = "";
if (row.GetCell(j) != null)
{
tmp = row.GetCell(j).ToString();
}
dr[j] = tmp;
}
tb.Rows.Add(dr);
}
}
//转Csv
public static void dataTableToCsv(DataTable table, string file)
{
FileStream fs = new FileStream(file, FileMode.OpenOrCreate);
StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);
foreach (DataRow row in table.Rows)
{
string line = "";
for (int i = 0; i < table.Columns.Count; i++)
{
line += row[i].ToString().Trim() + ","; //内容:
}
line = line.Substring(0, line.Length - 1) + "\n";
sw.Write(line);
}
sw.Close();
fs.Close();
}
//------------------------------ 条件判断 -------------------------
// 行列是否相等
public bool IsRowsColumns()
{
return true;
}
public static Dictionary<string, string> dicsRepeatField = new Dictionary<string, string>();
public static List<string> m_cToS_list = new List<string>();
public static bool IsRepeatField(string str, string tabelName)
{
if (dicsRepeatField.ContainsKey(str))
{
Console.WriteLine("数据表出错!!!! 出现重复字段" + str + " >>> " + tabelName);
Console.ReadKey();
return true;
}
else
{
dicsRepeatField.Add(str, str);
}
return false;
}
// 是否基本类型是否正确
public static bool IsBasicType(string type)
{
switch (type)
{
case "tinyint":
case "int":
case "float":
case "bool":
case "vector2":
case "vector3":
case "vector4":
case "string":
case "int32":
case "uint":
case "long":
case "ulong":
case "uint32":
return true;
default:
return false;
}
}
//转换正确的格式
public static string GetBasicType(string tmp)
{
if (tmp == "int[]")
{
tmp = "string";
}
else if (tmp == "vector3" || (tmp == "vector2") || (tmp == "vector4s"))
{
tmp = "string";
}
else if (tmp == "tinyint")
{
tmp = "int";
}
return tmp;
}
// 是否需要写入
private static bool IsNeedWruite(int index, int type)
{
for (int i = 0; i < m_cToS_list.Count; i++)
{
if (i == index && type == 1)
{
string str = m_cToS_list[i].ToLower();
if (string.IsNullOrEmpty(str) == false || str == "c" || str == "cs")
{
return true;
}
}
else if (i == index && type == 2)
{
string str = m_cToS_list[i].ToLower();
if (string.IsNullOrEmpty(str) == false || str == "s" || str == "cs")
{
return true;
}
}
}
return false;
}
}
}
https://mp.csdn.net/console/upDetailed
下载链接