public ActionResult ImportPay()
{
if (Request.Files.Count == 0)
{
Object o = new
{
success = false,
data = "上传失败，请选择需要上传的EXCEL"
};
JsonResult r = Json(o, JsonRequestBehavior.AllowGet);
return(r);
}
string message = string.Empty;
string fileName = System.IO.Path.GetFileNameWithoutExtension(Request.Files[0].FileName);
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = excel.Read(Request.Files[0].InputStream);
DataTable dt = ds.Tables[0];
bool pass = new Manager.PayManager().ImportPaymentData(dt, fileName, ref message);
Object result = new
{
success = pass,
data = message
};
JsonResult jsonResult = Json(result, JsonRequestBehavior.AllowGet);
return(jsonResult);
}
public ActionResult Import()
{
if (Request.Files.Count == 0)
{
Object failResult = new
{
success = false,
data = ""
};
JsonResult failJsonResult = Json(failResult, JsonRequestBehavior.AllowGet);
return(failJsonResult);
}
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = excel.Read(Request.Files[0].InputStream);
DataTable dt = ds.Tables[0];
string message;
bool pass = new EmployeeManager().ImportRecharges(dt, out message);
Object result = new
{
success = pass,
data = message
};
JsonResult jsonResult = Json(result, JsonRequestBehavior.AllowGet);
return(jsonResult);
}
public ActionResult ExportPayDetail(string payMonth)
{
IExcel excel = ExcelFactory.CreateDefault();
PaymentManager payment = new PaymentManager();
DataSet ds = payment.ExportDetail(payMonth);
POIStream stream = new POIStream();
stream.AllowClose = false;
excel.Write(stream, ds, ExcelExtendType.XLSX);
stream.AllowClose = true;
byte[] buffer = new byte[stream.Length];
stream.Position = 0;
stream.Read(buffer, 0, buffer.Length);
stream.Close();
HttpResponse context = System.Web.HttpContext.Current.Response;
try
{
context.ContentType = "application/ms-excel";
context.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(payMonth + "个税信息", System.Text.Encoding.UTF8)));
context.BinaryWrite(buffer);
context.Flush();
context.End();
}
catch (Exception ex)
{
context.ContentType = "text/plain";
context.Write(ex.Message);
}
return(null);
}
public void WriteMoreDataTypeTest()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t3.xlsx";
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = excel.Read(file);
DataTable dt = ds.Tables[0];
Assert.IsNotNull(ds);
}
public void ReadTest2()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t2.XLSX";
IExcel excel = ExcelFactory.CreateDefault();
DataTable dt = excel.Read(file, 0, 2);
int realCount = 10;
int count = dt.Rows.Count;
Assert.IsTrue(count == realCount);
}
public void ReadTest()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t1.xls";
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = excel.Read(file);
int realCount = 7;
int count = ds.Tables[0].Rows.Count;
Assert.IsTrue(count == realCount);
}
public ActionResult ImportorEditor()
{
HttpFileCollection files = System.Web.HttpContext.Current.Request.Files;
string outmsg = string.Empty;
bool success = false;
if (files.Count > 0)
{
for (int i = 0; i < files.Count; i++)
{
HttpPostedFile file1 = files[i];
Stream stream = file1.InputStream;
DataTable dt = new DataTable();
string fileName = System.IO.Path.GetFileNameWithoutExtension(file1.FileName);
string fileType = System.IO.Path.GetExtension(file1.FileName).ToLower();
switch (fileType)
{
case ".csv":
CsvFileParser cfp = new CsvFileParser();
dt = cfp.TryParse(stream, out outmsg);
break;
case ".xls":
case ".xlsx":
success = false;
//outmsg = "文件：" + fileName + "的文件格式接口待开发！<br />";
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = excel.Read(stream);
dt = ds.Tables[0];
break;
default:
success = false;
outmsg += "文件：" + fileName + "格式不支持！<br />";
break;
}
success = new Manager.PayManager().ImportInsurance(dt, fileName, ref outmsg);
}
}
else
{
outmsg += "未获取到文件，请重试！<br />";
}
if (success)
{
outmsg = "上传成功！<br />" + outmsg;
}
else
{
outmsg = "上传失败！<br />" + outmsg;
}
return(Json(outmsg));
}
public void WriteXlsxTest()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t2.xlsx";
IExcel excel = ExcelFactory.CreateDefault();
DataTable dt = excel.Read(file, 0, 2);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
TTemplate template = new TTemplate();
TRow R1 = new TRow();
TCell A = new TCell("A", "A", 0, 0); A.ColSpan = 2;
TCell B = new TCell("", "B", 0, 2);
TCell C = new TCell("C", "C", 0, 3); C.RowSpan = 2;
TCell D = new TCell("D", "D", 0, 4); D.RowSpan = 2;
TCell E = new TCell("", "E", 0, 5); E.ColSpan = 2;
R1.Cells.Add(A);
R1.Cells.Add(B);
R1.Cells.Add(C);
R1.Cells.Add(D);
R1.Cells.Add(E);
TRow R2 = new TRow();
TCell A1 = new TCell("A1", "A1", 1, 0);
TCell A2 = new TCell("A2", "A2", 1, 1);
TCell B1 = new TCell("B1", "B1", 1, 2);
TCell E1 = new TCell("E1", "E1", 1, 5);
TCell E2 = new TCell("E2", "E2", 1, 6);
R2.Cells.Add(A1);
R2.Cells.Add(A2);
R2.Cells.Add(B1);
R2.Cells.Add(E1);
R2.Cells.Add(E2);
TSheet tsheet = new TSheet();
tsheet.Name = "Sheet1";
tsheet.Title = "表格O";
tsheet.Head.Rows.Add(R1);
tsheet.Head.Rows.Add(R2);
template.Sheets.Add(tsheet);
string outputFilePath = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/out2.xlsx";
bool result = excel.WriteFile(outputFilePath, ds, template);
Assert.IsTrue(result);
}
public void WriteXlsxTestByTemplate()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t4.xlsx";
IExcel excel = ExcelFactory.CreateDefault();
DataTable dt = excel.Read(file, 0, 1);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
string templateName = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/template.xml";
string outputFilePath = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/out4.xlsx";
bool result = excel.WriteFile(outputFilePath, ds, templateName);
Assert.IsTrue(result);
}
public void WriteXlsTest()
{
string file = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/t1.xls";
IExcel excel = ExcelFactory.CreateDefault();
DataTable dt = excel.Read(file, 0, 1);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
MemoryStream stream1 = new MemoryStream();
excel.Write(stream1, ds, ExcelExtendType.XLS);
string outputFilePath = AppDomain.CurrentDomain.BaseDirectory + "/ExcelTests/out1.xls";
bool result = excel.WriteFile(outputFilePath, stream1);
Assert.IsNotNull(result);
}
public ActionResult Export()
{
List <CompanyInfo> list = new Manager.EmployeeManager().GetCompanies(string.Empty);
IExcel excel = ExcelFactory.CreateDefault();
DataTable dataTable = Converts.ListToDataTable <CompanyInfo>(list);
dataTable.Columns["Name"].Caption = "单位名称";
dataTable.Columns["Code"].Caption = "组织机构代码";
dataTable.Columns["Representative"].Caption = "法人";
dataTable.Columns["AccountBalance"].Caption = "账户余额";
dataTable.Columns.Remove("CompanyId");
dataTable.Columns.Remove("AccountId");
dataTable.Columns.Remove("Positions");
dataTable.Columns.Remove("Remark");
DataSet dataSet = new DataSet();
dataSet.Tables.Add(dataTable);
POIStream stream = new POIStream();
stream.AllowClose = false;
excel.Write(stream, dataSet, ExcelExtendType.XLSX);
stream.AllowClose = true;
byte[] buffer = new byte[stream.Length];
stream.Position = 0;
stream.Read(buffer, 0, buffer.Length);
stream.Dispose();
HttpResponse context = System.Web.HttpContext.Current.Response;
try
{
context.ContentType = "application/ms-excel";
context.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode("单位信息", System.Text.Encoding.UTF8)));
context.BinaryWrite(buffer);
context.Flush();
context.End();
}
catch (Exception ex)
{
context.ContentType = "text/plain";
context.Write(ex.Message);
}
return(null);
}
// GET: Reports/GetWxyjByYear/
public ActionResult DownWxyjByYear(int year)
{
ReportManager manager = new ReportManager();
DataSet ds = manager.GetWxyjByYear(year);
ds.Tables[0].TableName = "sheet1";
POIStream stream = new POIStream();
IExcel excel = ExcelFactory.CreateDefault();
stream.AllowClose = false;
//excel.Write(stream, ds, ExcelExtendType.XLSX);
string templateName = AppDomain.CurrentDomain.BaseDirectory + @"\Setting\Reports\wxyjYear.xml";
excel.Write(stream, templateName, ds, ExcelExtendType.XLSX);
stream.AllowClose = true;
byte[] buffer = new byte[stream.Length];
stream.Position = 0;
stream.Read(buffer, 0, buffer.Length);
stream.Close();
HttpResponse context = System.Web.HttpContext.Current.Response;
try
{
context.ContentType = "application/ms-excel";
context.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode("单位五险一金统计", System.Text.Encoding.UTF8)));
context.BinaryWrite(buffer);
context.Flush();
context.End();
}
catch (Exception ex)
{
context.ContentType = "text/plain";
context.Write(ex.Message);
}
return(null);
}
public ActionResult Export(int payId)
{
PaymentManager pm = new PaymentManager();
Payment payment = pm.LoadPayment(payId);
#region 动态模板
TTemplate template = new TTemplate();
TRow R1 = new TRow();
TRow R2 = new TRow();
int index1 = 3;
int index2 = 3;
TCell A = new TCell("PersonId", "ID", 0, 0); A.RowSpan = 2; A.Width = 1;
TCell B = new TCell("PersonName", "姓名", 0, 1); B.RowSpan = 2;
TCell C = new TCell("PersonCode", "身份证", 0, 2); C.RowSpan = 2;
R1.Cells.Add(A);
R1.Cells.Add(B);
R1.Cells.Add(C);
foreach (PayItemDO item in payment.Items)
{
TCell cell = new TCell();
cell.Name = item.ItemName;
cell.Caption = item.ItemCaption;
if (item.IsLeaf == true)
{
//cell.Width = 50;
}
if (item.ParentId == 0)
{
cell.RowIndex = 0;
cell.ColumnIndex = index1;
int colspan = 1;
foreach (PayItemDO pi in payment.Items)
{
if (pi.ParentId == item.ItemId)
{
colspan++;
}
}
if (colspan == 1)
{
cell.RowSpan = 2;
index1++;
index2++;
}
else
{
cell.ColSpan = colspan - 1;
index1 = index1 + cell.ColSpan;
}
R1.Cells.Add(cell);
}
else
{
cell.RowIndex = 1;
cell.ColumnIndex = index2;
index2 = index2 + 1;
R2.Cells.Add(cell);
}
}
TSheet tsheet = new TSheet();
tsheet.Name = "Sheet1";
tsheet.Title = "Sheet1";
tsheet.Head.Rows.Add(R1);
tsheet.Head.Rows.Add(R2);
template.Sheets.Add(tsheet);
#endregion
IExcel excel = ExcelFactory.CreateDefault();
DataSet ds = new DataSet();
payment.DataSource.TableName = "Sheet1";
ds.Tables.Add(payment.DataSource);
POIStream stream = new POIStream();
stream.AllowClose = false;
excel.Write(stream, template, ds, ExcelExtendType.XLSX);
stream.AllowClose = true;
byte[] buffer = new byte[stream.Length];
stream.Position = 0;
stream.Read(buffer, 0, buffer.Length);
stream.Close();
HttpResponse context = System.Web.HttpContext.Current.Response;
try
{
context.ContentType = "application/ms-excel";
context.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(payment.PayTitle, System.Text.Encoding.UTF8)));
context.BinaryWrite(buffer);
context.Flush();
context.End();
}
catch (Exception ex)
{
context.ContentType = "text/plain";
context.Write(ex.Message);
}
return(null);
}
