public void SaveDataBySheetIndex(string fullFilePath, string fileNameWithoutExtension, string month, int sheetIndex)
{
NPOIDAO dao = new NPOIDAO();
List <List <string> > retData = new List <List <string> >();
using (FileStream fs = File.Open(fullFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
retData = dao.getData(fs, sheetIndex);
}
List <string> sqlString = new List <string>();
string sqlTemplate = @" INSERT INTO A_BD02([EntityCode],[Month],[SerialNumber],[InvoiceType] ,[InvoiceCode],[InvoiceNo],[BuyerName],[BuyerName1],[Remark],[IssuedDateTime],[Amount],[TaxAmount],[TaxRate],[FileName])
VALUES ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13});
";
foreach (var item in retData)
{
if (item[0] == "Entity")
{
continue;
}
OutputInvoiceIssuedRecord record = this.GetOutputInvoiceIssuedRecord(item, month);
string temp = string.Format(sqlTemplate, getStr(record.EntityCode),
getStr(record.Month), // month
getStr(record.SerialNumber), // SerialNumber
getStr(record.InvoiceType), //InvoiceType
getStr(record.InvoiceCode), //InvoiceCode
getStr(record.InvoiceNo), //InvoiceNo
getStr(record.BuyerName), //BuyerName
getStr(record.BuyerName1), //BuyerName1
getStr(record.Remark), //Remark
getStr(record.IssuedDateTime), //IssuedDateTime
getOther(record.Amount), //Amount
getOther(record.TaxAmount), //TaxAmount
getOther(record.TaxRate), //TaxRate,
getStr(fileNameWithoutExtension)
);
sqlString.Add(temp);
}
List <string> saveSql = sqlString.Take(10000).ToList <string>();
this.saveFile(saveSql, 0, fileNameWithoutExtension);
int index = 1;
while (saveSql.Count == 10000)
{
saveSql = sqlString.Skip(index * 10000).Take(10000).ToList <string>();
this.saveFile(saveSql, index, fileNameWithoutExtension);
index++;
}
}
/// <summary>
/// GD 发票 2014 年 到 2015年数据导入
/// </summary>
/// <param name="fullFilePath"></param>
/// <param name="fileNameWithoutExtension"></param>
public void TestReadData2014TO2015(string fullFilePath, string fileNameWithoutExtension)
{
NPOIDAO dao = new NPOIDAO();
List <List <string> > retData = new List <List <string> >();
using (FileStream fs = File.Open(fullFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
retData = dao.getData(fs, 0);
}
List <string> sqlString = new List <string>();
string sqlTemplate = @" INSERT INTO AAA([EntityCode],[Month],[SerialNumber],[InvoiceType] ,[InvoiceCode],[InvoiceNo],[BuyerName],[BuyerName1],[Remark],[IssuedDateTime],[Amount],[TaxAmount],[TaxRate],[FileName])
VALUES ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13});
";
foreach (var item in retData)
{
if (item[0] == "Year")
{
continue;
}
string temp = string.Format(sqlTemplate, getStr(null),
getStr(item[12]), // month
getStr(null), // SerialNumber
getStr("增值税专用票"), //InvoiceType
getStr(null), //InvoiceCode
getStr(null), //InvoiceNo
getStr(item[2]), //BuyerName
getStr(item[11]), //BuyerName1
getStr(item[5]), //Remark
getStr(item[12]), //IssuedDateTime
getOther(item[9]), //Amount
getOther(item[8]), //TaxAmount
getOther(item[7]), //TaxRate,
getStr(fileNameWithoutExtension)
);
sqlString.Add(temp);
}
List <string> saveSql = sqlString.Take(10000).ToList <string>();
this.saveFile(saveSql, 0, fileNameWithoutExtension);
int index = 1;
while (saveSql.Count == 10000)
{
saveSql = sqlString.Skip(index * 10000).Take(10000).ToList <string>();
this.saveFile(saveSql, index, fileNameWithoutExtension);
index++;
}
}
public void TestGenerateSQL(string fullFilePath, string fileName, string period)
{
NPOIDAO dao = new NPOIDAO();
List <List <string> > retData = new List <List <string> >();
using (FileStream fs = File.Open(fullFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
retData = dao.getData(fs, 0);
}
List <string> sqlString = new List <string>();
string sqlTemplate = @" INSERT INTO AAA([EntityCode],[Month],[SerialNumber],[InvoiceType] ,[InvoiceCode],[InvoiceNo],[BuyerName],[BuyerName1],[Remark],[IssuedDateTime],[Amount],[TaxAmount],[TaxRate])
VALUES ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12});
";
foreach (var item in retData)
{
if (item[0] == "entity")
{
continue;
}
string temp = string.Format(sqlTemplate, getStr(item[0]),
getStr(item[11]),
null,
getStr("机动车销售发票"),
getStr(item[5]),
getStr(item[6]),
getStr(item[7]),
getStr(item[8]),
getStr(item[9]),
getStr(item[10]),
getOther(item[11]),
getOther(item[13]),
getOther(item[12])
);
sqlString.Add(temp);
}
List <string> saveSql = sqlString.Take(10000).ToList <string>();
this.saveFile(saveSql, 0);
int index = 1;
while (saveSql.Count == 10000)
{
saveSql = sqlString.Skip(index * 10000).Take(10000).ToList <string>();
this.saveFileName(fileName, saveSql, index);
index++;
}
}
public void TestReadData201803(string fullFilePath, string fileNameWithoutExtension, string month)
{
NPOIDAO dao = new NPOIDAO();
List <List <string> > retData = new List <List <string> >();
using (FileStream fs = File.Open(fullFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
retData = dao.getData(fs, 0);
}
List <string> sqlString = new List <string>();
string sqlTemplate = @" INSERT INTO OutputInvoiceIssuedRecord201803([EntityCode],[Month],[SerialNumber],[InvoiceType] ,[InvoiceCode],[InvoiceNo],[BuyerName],[BuyerName1],[Remark],[IssuedDateTime],[Amount],[TaxAmount],[TaxRate])
VALUES ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12});
";
foreach (var item in retData)
{
if (item[0] != null && item[0].ToLower() == "entity")
{
continue;
}
string temp = string.Format(sqlTemplate, getStr(item[0]),
getStr(month), //月份
getStr(null), // 序列号
getStr(item[2]), //发票类型
getStr(item[3]), // fapiao code
getStr(item[4]), // fapiao number
getStr(item[5]), //buyer
getStr(item[6]), //buyer1
getStr(item[1]), //remark
getStr(item[8]), //IssuedDateTime
getOther(item[9]), //Amount
getOther(item[11]), //TaxAmount
getOther(item[10]) //TaxRate
);
sqlString.Add(temp);
}
List <string> saveSql = sqlString.Take(10000).ToList <string>();
this.saveFile(saveSql, 0, fileNameWithoutExtension);
int index = 1;
while (saveSql.Count == 10000)
{
saveSql = sqlString.Skip(index * 10000).Take(10000).ToList <string>();
this.saveFile(saveSql, index, fileNameWithoutExtension);
index++;
}
}
/// <summary>
/// 测试读取数据的大小An unhandled exception of type 'System.OutOfMemoryException' occurred in mscorlib.dll
/// </summary>
public void TestReadData()
{
NPOIDAO dao = new NPOIDAO();
string fullFilePath = @"C:\Julius_J_Zhu\10Tesla\06test\35历史开票记录check\180306导入数据到数据库\20180116 BD Y2017 开票清单-to Celine.xlsx";
List <List <string> > retData = new List <List <string> >();
using (FileStream fs = File.Open(fullFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
retData = dao.getData(fs, 0);
}
List <string> sqlString = new List <string>();
string sqlTemplate = @" INSERT INTO AAA([EntityCode],[Month],[SerialNumber],[InvoiceType] ,[InvoiceCode],[InvoiceNo],[BuyerName],[BuyerName1],[Remark],[IssuedDateTime],[Amount],[TaxAmount],[TaxRate])
VALUES ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12});
";
foreach (var item in retData)
{
if (item[0] == "entity")
{
continue;
}
string temp = string.Format(sqlTemplate, getStr(item[0]),
getStr(item[2]),
getStr(item[3]),
getStr(item[4]),
getStr(item[5]),
getStr(item[6]),
getStr(item[7]),
getStr(item[8]),
getStr(item[9]),
getStr(item[10]),
getOther(item[11]),
getOther(item[13]),
getOther(item[12])
);
sqlString.Add(temp);
}
List <string> saveSql = sqlString.Take(10000).ToList <string>();
this.saveFile(saveSql, 0);
int index = 1;
while (saveSql.Count == 10000)
{
saveSql = sqlString.Skip(index * 10000).Take(10000).ToList <string>();
this.saveFile(saveSql, index);
index++;
}
}
