public void Export(string businessDate, string exportFilePath)
{
System.Data.DataSet ds = GetByBusinessDate(businessDate);
System.Data.DataTable dt1 = ds.Tables[0];
System.Data.DataTable dt2 = ds.Tables[1];
string[,] arrayExport1 = Tools.DataTableToArray(dt1, false);
string[,] arrayExport2 = Tools.DataTableToArray(dt2, false);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet1 = excelProcess.GetSheet(1);
sheet1.Activate();
excelProcess.CopyRowInsert(sheet1, 2, 3, arrayExport1.GetLength(0) + 1);
Range range1 = excelProcess.GetRange(sheet1, 2, 1, arrayExport1.GetLength(0) + 1, arrayExport1.GetLength(1));
range1.Value2 = arrayExport1;
range1.Value2 = range1.Value2;
Worksheet sheet2 = excelProcess.GetSheet(2);
sheet2.Activate();
excelProcess.CopyRowInsert(sheet2, 2, 3, arrayExport2.GetLength(0) + 1);
Range range2 = excelProcess.GetRange(sheet2, 2, 1, arrayExport2.GetLength(0) + 1, arrayExport2.GetLength(1));
range2.Value2 = arrayExport2;
range2.Value2 = range2.Value2;
excelProcess.Save();
}
}
public void Export(string businessDate,
string conditionREPDZABM,
string conditionMonFee,
string conditionNoMaster,
string conditionQQJY,
string conditionPresent,
string conditionAgio,
string exportFilePath)
{
System.Data.DataTable dt = GetByCondition(businessDate,
conditionREPDZABM, conditionMonFee, conditionNoMaster,
conditionQQJY, conditionPresent, conditionAgio
);
string[,] arrayExport = Tools.DataTableToArray(dt, false);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range = excelProcess.GetRange(sheet, 7, 1, 9, 7);
range.Value2 = arrayExport;
range.Value2 = range.Value2;
excelProcess.SetCells(sheet, 3, 2, businessDate);
excelProcess.SetCells(sheet, 15, 7, DateTime.Now.ToString("yyyyMMdd"));
excelProcess.Save();
}
}
public void Export(string businessDate,
string conditionOffset,
string conditionLowestConsumption,
string conditionGroupPrepay,
string conditionUser1,
string conditionUser2,
string exportFilePath)
{
DataSet ds = GetByCondition(businessDate, conditionOffset, conditionLowestConsumption, conditionGroupPrepay, conditionUser1, conditionUser2);
System.Data.DataTable dt1 = ds.Tables[0];
System.Data.DataTable dt2 = ds.Tables[1];
string[,] arrayExport1 = Tools.DataTableToArray(dt1, false);
string[,] arrayExport2 = Tools.DataTableToArray(dt2, false);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range1 = excelProcess.GetRange(sheet, 8, 1, 8, 7);
range1.Value2 = arrayExport1;
range1.Value2 = range1.Value2;
Range range2 = excelProcess.GetRange(sheet, 16, 1, 16, 3);
range2.Value2 = arrayExport2;
range2.Value2 = range2.Value2;
excelProcess.SetCells(sheet, 3, 2, businessDate);
excelProcess.SetCells(sheet, 20, 7, DateTime.Now.ToString("yyyyMMdd"));
excelProcess.Save();
}
}
public void Export(string businessDate, string exportFilePath)
{
System.Data.DataTable dt = GetByBusinessDate(businessDate);
string[,] arrayExport = Tools.DataTableToArray(dt, false);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range = excelProcess.GetRange(sheet, 2, 1, 5, 10);
range.Value2 = arrayExport;
range.Value2 = range.Value2;
Range rangeBusinessDate = excelProcess.GetRange(sheet, 2, 1, 4, 1);
rangeBusinessDate.Merge(rangeBusinessDate.MergeCells);
Range rangeMoneyTotal = excelProcess.GetRange(sheet, 2, 2, 4, 2);
rangeMoneyTotal.Merge(rangeMoneyTotal.MergeCells);
excelProcess.SetCells(sheet, 5, 2, string.Empty);
excelProcess.Save();
}
}
public void Export(string businessDate, string queryCondition, string queryText, string exportFilePath)
{
System.Data.DataTable dtExport = GetByCondition(businessDate, queryCondition, queryText);
bool isAddTitle = true;
int sheetIndex = 1;
int rowIndexStart = 1;
int columnIndexStart = 1;
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
excelProcess.Export(sheetIndex, rowIndexStart, columnIndexStart, dtExport, isAddTitle);
excelProcess.Save();
}
}
public void Export(string businessDate, string queryCondition, string queryText, string exportFilePath)
{
System.Data.DataTable dt = GetByCondition(businessDate, queryCondition, queryText);
string[,] arrayExport = Tools.DataTableToArray(dt, true);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range = excelProcess.GetRange(sheet, 1, 1, arrayExport.GetLength(0), arrayExport.GetLength(1));
range.Value2 = arrayExport;
range.Value2 = range.Value2;
excelProcess.Save();
}
}
public void Export(string businessDate, string exportFilePath)
{
System.Data.DataTable dt = GetByBusinessDate(businessDate);
string[,] arrayExport = Tools.DataTableToArray(dt, false);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range = excelProcess.GetRange(sheet, 2, 1, 5, 2);
range.Value2 = arrayExport;
range.Value2 = range.Value2;
excelProcess.Save();
}
}
public void Export(string businessDate, NM_Export_SynthesizeAnaly synthesizeAnaly, string exportFilePath)
{
System.Data.DataTable dt = GetAllByCondition(businessDate, synthesizeAnaly);
dt.Columns.RemoveAt(0);
string[,] arrayExport = Tools.DataTableToArray(dt, false);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range = excelProcess.GetRange(sheet, 2, 1, arrayExport.GetLength(0) + 1, arrayExport.GetLength(1));
range.Value2 = arrayExport;
range.Value2 = range.Value2;
excelProcess.Save();
}
}
public void Export(string businessDateStart, string businessDateEnd, string exportFilePath)
{
System.Data.DataTable dt = GetByBusinessDate(businessDateStart, businessDateEnd);
string[,] arrayExport = Tools.DataTableToArray(dt, true);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range = excelProcess.GetRange(sheet, 1, 1, arrayExport.GetLength(0), arrayExport.GetLength(1));
range.Value2 = arrayExport;
range.Value2 = range.Value2;
range.Borders.LineStyle = 1;
excelProcess.Save();
}
}
public void Export(string businessDate, string exportFilePath)
{
System.Data.DataTable dt = GetByBusinessDate(businessDate);
string[,] arrayExport = Tools.DataTableToArray(dt, false);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range = excelProcess.GetRange(sheet, 9, 1, 11, 10);
range.Value2 = arrayExport;
range.Value2 = range.Value2;
excelProcess.SetCells(sheet, 3, 2, businessDate);
excelProcess.SetCells(sheet, 18, 6, DateTime.Now.ToString("yyyyMMdd"));
excelProcess.Save();
}
}
public void Export(string businessDate, string exportFilePath)
{
System.Data.DataTable dt = GetByBusinessDate(businessDate);
string[,] arrayExport = Tools.DataTableToArray(dt, false);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
excelProcess.CopyRowInsert(sheet, 7, 8, 6 + arrayExport.GetLength(0));
Range range = excelProcess.GetRange(sheet, 7, 1, 6 + arrayExport.GetLength(0), arrayExport.GetLength(1));
range.Value2 = arrayExport;
range.Value2 = range.Value2;
excelProcess.SetCells(sheet, 3, 2, businessDate);
excelProcess.Save();
}
}
public void NotInFeeClassExport(string exportFilePath)
{
System.Data.DataTable dt = GetNotInFeeClass();
string[,] arrayExport = Tools.DataTableToArray(dt, true);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range = excelProcess.GetRange(sheet, 1, 1, arrayExport.GetLength(0), arrayExport.GetLength(1));
range.Value2 = arrayExport;
range.Value2 = range.Value2;
excelProcess.Save();
}
}
public string Export(string businessDateStart, string businessDateEnd, byte businessType, string businessTypeValue, string exportFilePath)
{
System.Data.DataTable dt = GetAllByBusinessType(businessDateStart, businessDateEnd, businessType, businessTypeValue);
string[,] arrayExport = Tools.DataTableToArray(dt, true);
if (businessDateStart == businessDateEnd)
{
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range = excelProcess.GetRange(sheet, 1, 1, arrayExport.GetLength(0), arrayExport.GetLength(1));
range.Value2 = arrayExport;
range.Value2 = range.Value2;
excelProcess.Save();
}
}
else
{
int fixedColumnCount = 4;
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range = excelProcess.GetRange(sheet, 1, 1, arrayExport.GetLength(0), arrayExport.GetLength(1));
range.Value2 = arrayExport;
range.Value2 = range.Value2;
excelProcess.CopyRowInsert(sheet, 1, 2, 2);
excelProcess.GetRange(sheet, 1, 1, 2, 1).Merge();
excelProcess.GetRange(sheet, 1, 2, 2, 2).Merge();
excelProcess.GetRange(sheet, 1, 3, 2, 3).Merge();
excelProcess.GetRange(sheet, 1, 4, 2, 4).Merge();
for (int colIndex = 0; colIndex < arrayExport.GetLength(1) - fixedColumnCount; colIndex++)
{
excelProcess.SetCells(sheet, 1, colIndex + fixedColumnCount + 1, "金额");
}
excelProcess.GetRange(sheet, 1, fixedColumnCount + 1, 1, arrayExport.GetLength(1)).Merge();
excelProcess.Save();
}
}//end else (businessDateStart != businessDateEnd)
string businessTypeName = string.Empty;
switch (businessType)
{
case (byte)SynthesizeAnalyBusinessType.VoiceClass:
{
businessTypeName = "语单类";
break;
}
case (byte)SynthesizeAnalyBusinessType.DreamClass:
{
businessTypeName = "梦网类";
break;
}
case (byte)SynthesizeAnalyBusinessType.DataClass:
{
businessTypeName = "数据类";
break;
}
case (byte)SynthesizeAnalyBusinessType.GroupClass:
{
businessTypeName = "集团类";
break;
}
}
string saveFileName = string.Empty;
if (businessDateStart != businessDateEnd)
{
saveFileName = string.Format("{0}-{1}无主{2}.xls", businessDateStart, businessDateEnd, businessTypeName);
}
else
{
saveFileName = string.Format("{0}无主{1}.xls", businessDateStart, businessTypeName);
}
return saveFileName;
}
public void Export(string businessDate, string exportFilePath)
{
DataSet ds = GetByBusinessDate(businessDate);
System.Data.DataTable dt1 = ds.Tables[0];
System.Data.DataTable dt2 = ds.Tables[1];
System.Data.DataTable dt3 = ds.Tables[2];
System.Data.DataTable dt4 = ds.Tables[3];
System.Data.DataTable dt5 = ds.Tables[4];
System.Data.DataTable dt6 = ds.Tables[5];
string[,] arrayExport1 = Tools.DataTableToArray(dt1, false);
string[,] arrayExport2 = Tools.DataTableToArray(dt2, false);
string[,] arrayExport3 = Tools.DataTableToArray(dt3, false);
string[,] arrayExport4 = Tools.DataTableToArray(dt4, false);
string[,] arrayExport5 = Tools.DataTableToArray(dt5, false);
string[,] arrayExport6 = Tools.DataTableToArray(dt6, false);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet1 = excelProcess.GetSheet(1);
sheet1.Activate();
excelProcess.CopyRowInsert(sheet1, 2, 3, arrayExport1.GetLength(0) + 1);
Range range1 = excelProcess.GetRange(sheet1, 2, 1, arrayExport1.GetLength(0) + 1, arrayExport1.GetLength(1));
range1.Value2 = arrayExport1;
range1.Value2 = range1.Value2;
Worksheet sheet2 = excelProcess.GetSheet(2);
sheet2.Activate();
excelProcess.CopyRowInsert(sheet2, 2, 3, arrayExport2.GetLength(0) + 1);
Range range2 = excelProcess.GetRange(sheet2, 2, 1, arrayExport2.GetLength(0) + 1, arrayExport2.GetLength(1));
range2.Value2 = arrayExport2;
range2.Value2 = range2.Value2;
Worksheet sheet3 = excelProcess.GetSheet(3);
sheet3.Activate();
excelProcess.CopyRowInsert(sheet3, 2, 3, arrayExport3.GetLength(0) + 1);
Range range3 = excelProcess.GetRange(sheet3, 2, 1, arrayExport3.GetLength(0) + 1, arrayExport3.GetLength(1));
range3.Value2 = arrayExport3;
range3.Value2 = range3.Value2;
Worksheet sheet4 = excelProcess.GetSheet(4);
sheet4.Activate();
excelProcess.CopyRowInsert(sheet4, 2, 3, arrayExport4.GetLength(0) + 1);
Range range4 = excelProcess.GetRange(sheet4, 2, 1, arrayExport4.GetLength(0) + 1, arrayExport4.GetLength(1));
range4.Value2 = arrayExport4;
range4.Value2 = range4.Value2;
Worksheet sheet5 = excelProcess.GetSheet(5);
sheet5.Activate();
excelProcess.CopyRowInsert(sheet5, 2, 3, arrayExport5.GetLength(0) + 1);
Range range5 = excelProcess.GetRange(sheet5, 2, 1, arrayExport5.GetLength(0) + 1, arrayExport5.GetLength(1));
range5.Value2 = arrayExport5;
range5.Value2 = range5.Value2;
Worksheet sheet6 = excelProcess.GetSheet(6);
sheet6.Activate();
excelProcess.CopyRowInsert(sheet6, 2, 3, arrayExport6.GetLength(0) + 1);
Range range6 = excelProcess.GetRange(sheet6, 2, 1, arrayExport6.GetLength(0) + 1, arrayExport6.GetLength(1));
range6.Value2 = arrayExport6;
range6.Value2 = range6.Value2;
excelProcess.Save();
}
}
public ActionResult Upload(HttpPostedFileBase fileData, string businessDate)
{
string zipDirectory = string.Empty;
try
{
if (fileData == null)
{
return Content("失败:上传文件不存在!");
}
string uploadDirectory = Server.MapPath(NMBConfig.UploadDirectory);
BLLFileProcess bllFileProcess = new BLLFileProcess();
NMB.Model.FileInfo fileInfo;
string extenName = Path.GetExtension(fileData.FileName).ToLower();
if (extenName.Contains("zip"))
{
zipDirectory = Path.Combine(uploadDirectory, "TempZip");
if (!Directory.Exists(zipDirectory))
{
Directory.CreateDirectory(zipDirectory);
}
foreach (string file in Directory.GetFiles(zipDirectory))
{
System.IO.File.Delete(file);
}
string zipFileName = Path.Combine(zipDirectory, fileData.FileName);
fileData.SaveAs(zipFileName);
using (ZipFile zipFile = ZipFile.Read(zipFileName))
{
foreach (ZipEntry entry in zipFile)
{
entry.Extract(zipDirectory);
}
}
System.IO.File.Delete(zipFileName);
string unZipFile = Directory.GetFiles(zipDirectory)[0];
string unZipFileName = Path.GetFileName(unZipFile);
if (bllFileProcess.IsCanUpload(businessDate, unZipFileName, out fileInfo))
{
if (fileInfo.Type != FileInfoType.NoMasterBase && fileInfo.Type != FileInfoType.BillBase)
{
uploadDirectory = Path.Combine(uploadDirectory, businessDate);
}
if (!Directory.Exists(uploadDirectory))
{
Directory.CreateDirectory(uploadDirectory);
}
string saveFileName = Path.Combine(uploadDirectory, unZipFileName);
//File.Copy(unZipFile, saveFileName, true);
FTP ftp = new FTP(new Uri(NMBConfig.FtpURL), NMBConfig.FtpUserName, NMBConfig.FtpUserPass);
string projectDirectory = "NoMasterAndBill";
if (!ftp.DirectoryExist(projectDirectory))
{
ftp.MakeDirectory(projectDirectory);
}
ftp.GotoDirectory(projectDirectory);
if (!ftp.DirectoryExist(businessDate))
{
ftp.MakeDirectory(businessDate);
}
ftp.GotoDirectory(businessDate);
ftp.UploadFileBigFile(unZipFile, true);
bllFileProcess.FileUploadRecord(businessDate, fileInfo.Id, saveFileName);
return Content("完成");
}
else
{
return Content("失败：上传压缩包内文件名不符合要求！");
}
}
else if (bllFileProcess.IsCanUpload(businessDate, fileData.FileName, out fileInfo))
{
if (fileInfo.Type != FileInfoType.NoMasterBase && fileInfo.Type != FileInfoType.BillBase)
{
uploadDirectory = Path.Combine(uploadDirectory, businessDate);
}
if (!Directory.Exists(uploadDirectory))
{
Directory.CreateDirectory(uploadDirectory);
}
string saveFileName = Path.Combine(uploadDirectory, fileData.FileName);
fileData.SaveAs(saveFileName);
if (Path.GetExtension(saveFileName).ToLower() == ".xls")
{
using (ExcelProcess excelProcess = new ExcelProcess(saveFileName))
{
excelProcess.SaveAs(saveFileName);
excelProcess.Dispose();
}
}
bllFileProcess.FileUploadRecord(businessDate, fileInfo.Id, saveFileName);
return Content("完成");
}
else
{
return Content("失败：上传压缩包内文件名不符合要求！");
}
}
catch (Exception ex)
{
Tools.LogWrite(ex.ToString());
return Content("异常：" + ex.Message);
}
finally
{
if (Directory.Exists(zipDirectory))
{
foreach (string file in Directory.GetFiles(zipDirectory))
{
System.IO.File.Delete(file);
}
}
}
}