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 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 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);
}
private static DataTable AsDataTable(IExcel self, ExcelDataTableConfiguration configuration)
{
var result = new DataTable {
TableName = self.Name
};
result.ExtendedProperties.Add("visiblestate", self.VisibleState);
var first = true;
var emptyRows = 0;
var columnIndices = new List <int>();
while (self.Read())
{
if (first)
{
if (configuration.UseHeaderRow && configuration.ReadHeaderRow != null)
{
configuration.ReadHeaderRow(self);
}
for (var i = 0; i < self.FieldCount; i++)
{
if (configuration.FilterColumn != null && !configuration.FilterColumn(self, i))
{
continue;
}
var name = configuration.UseHeaderRow
? Convert.ToString(self.GetValue(i))
: null;
if (string.IsNullOrEmpty(name))
{
name = configuration.EmptyColumnNamePrefix + i;
}
// if a column already exists with the name append _i to the duplicates
var columnName = GetUniqueColumnName(result, name);
var column = new DataColumn(columnName, typeof(object))
{
Caption = name
};
result.Columns.Add(column);
columnIndices.Add(i);
}
result.BeginLoadData();
first = false;
if (configuration.UseHeaderRow)
{
continue;
}
}
if (configuration.FilterRow != null && !configuration.FilterRow(self))
{
continue;
}
if (IsEmptyRow(self, configuration))
{
emptyRows++;
continue;
}
for (var i = 0; i < emptyRows; i++)
{
result.Rows.Add(result.NewRow());
}
emptyRows = 0;
var row = result.NewRow();
for (var i = 0; i < columnIndices.Count; i++)
{
var columnIndex = columnIndices[i];
var value = self.GetValue(columnIndex);
if (configuration.TransformValue != null)
{
var transformedValue = configuration.TransformValue(self, i, value);
if (transformedValue != null)
{
value = transformedValue;
}
}
row[i] = value;
}
result.Rows.Add(row);
}
result.EndLoadData();
return(result);
}