//表格的导入
public ActionResult Export(HttpPostedFileBase file)
{
var path = Server.MapPath("/files");
var filepath = Path.Combine(path, file.FileName);
//将文件进行保存
try
{
file.SaveAs(filepath);
var table = ToExcel.Table(filepath);
List <Student> students = new List <Student>();
for (var i = 0; i < table.Rows.Count; i++)
{
var a = table.Rows[i][6].ToString();
students.Add(new Student()
{
Name = table.Rows[i][0].ToString(),
GradeId = Convert.ToInt32(table.Rows[i][1].ToString()),
Sex = table.Rows[i][2].ToString(),
CardId = table.Rows[i][3].ToString(),
Phone = table.Rows[i][4].ToString(),
Address = table.Rows[i][5].ToString(),
EnrollmentDate = Convert.ToDateTime(table.Rows[i][6].ToString())
});
}
unitof.CreateRespository <Student>().Insert(students);
}
catch (Exception ex)
{
return(Json(new { msg = "发生了异常" }));
throw ex;
}
return(Json(new { msg = "导入成功" }));
}
private void MycmdToExcel_Click()
{
try
{
jolcode.Code.AplicarEfecto(View as Window);
jolcode.Code.DoEvents();
using (jolcode.ToExcel excel = new ToExcel())
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Files|*.xlsx|All Files|*.*";
if (sfd.ShowDialog() == true)
{
//excel.TableToExcel(sfd.SafeFileName, _T);
excel.TableToExcel(sfd.FileName, ItemsSource);
MessageBox.Show("Done!!!", "Save", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
}
catch (Exception ex)
{
MethodBase site = ex.TargetSite;
MessageBox.Show(ex.Message, site.Name, MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
jolcode.Code.QuitarEfecto(View as Window);
}
}
protected void btn_dlexcel_dc_Click(object sender, EventArgs e)
{
string s_utj = s_date.Value;
string e_utj = e_date.Value;
string where = "";
if (s_utj == null)
{
s_utj = "";
}
if (e_utj == null)
{
e_utj = "";
}
where = " where time>='" + s_utj + "' and time<='" + e_utj + "'";
if (where == " where time>='' and time<=''")
{
where = "";
}
string temp = sql;
sql = sql + where + " order by time desc";
DataSet ds2 = new DataSet();
ds2 = SqlFunction.Sql_DataAdapterToDS(sql);
sql = temp;
if (!ToExcel.CreateExcel(ds2, "导出数据记录", 1, null, null, null))
{
LayerA("当前时间段没有信息,无法导出", 5, Page);
}
}
public void test()
{
var testDataModel = new TestDataModel()
{
GooodsCode = "11111",
NameCN = "",
NameEN = "NameEN",
NameJP = "NameJP",
NameKOR = "NameKOR"
};
var testDataModel2 = new TestDataModel()
{
GooodsCode = "22222",
NameCN = "NameCN2",
NameEN = "",
NameJP = "NameJP2",
NameKOR = "NameKOR2"
};
var testDataModel3 = new TestDataModel()
{
GooodsCode = "33333",
NameCN = "NameCN2",
NameEN = "NameEN2",
NameJP = "",
NameKOR = "NameKOR2"
};
var testDataModel4 = new TestDataModel()
{
GooodsCode = "44444",
NameCN = "NameCN2",
NameEN = "",
NameJP = "NameJP2",
NameKOR = ""
};
var testDataModel5 = new TestDataModel()
{
GooodsCode = "",
NameCN = "NameCN2",
NameEN = "",
NameJP = "NameJP2",
NameKOR = ""
};
var dataList = new List <TestDataModel>();
dataList.Add(testDataModel);
dataList.Add(testDataModel2);
dataList.Add(testDataModel3);
dataList.Add(testDataModel4);
dataList.Add(testDataModel5);
var excel = new ToExcel();
var streamData = excel.DataToExcel <List <TestDataModel> >(dataList, "공백테스트");
System.IO.File.WriteAllBytes(@"C:\Users\ronal\Desktop\excel\test.xlsx", streamData);
}
static void StartExcel(ConfigSettings settings)
{
var data = settings.GridSettings?.getActualData();
var styles = new List <GridCellsAppearance>();
DefaultData.AddStyles(styles);
var rainbow = styles.FirstOrDefault(x => x.name.Equals("RAINBOW"));
ToExcel test = new ToExcel(data);
test.AsyncCreate().Wait();
test.Visible = true;
}
private void btn_export_Click(object sender, EventArgs e)
{
var f = new SaveFileDialog();
f.Filter = "*.xls|*.xls";
if (dt_excel == null)
{
MessageBox.Show("请先查询再导出！");
}
else if (f.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
dt_excel = bll.GetData(key, 10000, 1, out total, "订单ID", "总数量,总金额");
ToExcel.toExcel(dt_excel, f.FileName);
}
}
public static async void ExportExcel(Form sender, GridDataTable data, GridCellsAppearance style, bool throwAnyway)
{
ToExcel excel = new ToExcel(data);
try
{
CancellationTokenSource token = new CancellationTokenSource();
DialogExport dialog = new DialogExport();
dialog.FormClosing += (s, ev) => token.Cancel();
dialog.ButtonCancelClick += () =>
{
token.Cancel();
dialog.Close();
};
dialog.Show(sender);
excel.cancellationToken = token.Token;
excel.OnStateChanged += dialog.setStatus;
await excel.AsyncCreate();
if (style != null)
{
await excel.AsyncColorize(style, 1);
}
dialog.Close();
excel.Visible = true;
}
catch (Exception ex)
{
excel.ForceClose();
bool shouldThrow = !(ex is OperationCanceledException);
if (ex is COMException exCom)
{
MessageBox.Show(sender, "Excel Error Occured: " + exCom.Message, String.Empty, MessageBoxButtons.OK, MessageBoxIcon.Error);
shouldThrow = false;
}
if (shouldThrow || throwAnyway)
{
throw;
}
}
}
public MemoryStream GetReporteDinamico(DTOReporteDinamico reporteDinamico, string fileName)
{
MemoryStream memoryStream = null;
try
{
GenericDataRepository <CatMotivosViaje> motivosViajeRepository = new GenericDataRepository <CatMotivosViaje>();
IList <DTOReporteDinamico> reporteDinamicoList = (from encuesta in _encuestaRepository.GetList(x => x.FechaAplicacion >= reporteDinamico.FechaInicio.Date && x.FechaAplicacion.Date <= reporteDinamico.FechaFin.Date)
from derechohabiente in _derechohabienteRepository.GetList(x => x.IdDerechohabiente == encuesta.IdDerechohabiente).Take(1).DefaultIfEmpty()
from tipoDestino in _tiposDestinoRepository.GetList(x => x.IdTipoDestino == encuesta.IdTipoDestino).Take(1).DefaultIfEmpty()
from temporada in _temporadasRepository.GetList(x => x.IdTemporada == encuesta.IdTemporada).Take(1).DefaultIfEmpty()
from viaje in _tiposViajeRepository.GetList(x => x.IdTipoViaje == encuesta.IdTipoViaje).Take(1).DefaultIfEmpty()
from motivo in motivosViajeRepository.GetList(x => x.IdMotivoViaje == encuesta.IdMotivoViaje).Take(1).DefaultIfEmpty()
from genero in _generoRepository.GetList(x => x.IdGenero == derechohabiente.IdGenero).Take(1).DefaultIfEmpty()
from estado in _estadoRepository.GetList(x => x.IdEstado == derechohabiente.IdEstado).Take(1).DefaultIfEmpty()
select new DTOReporteDinamico
{
Destino = tipoDestino.Nombre,
TemporadaVacacional = temporada.Nombre,
Viaje = viaje.Nombre,
Motivo = motivo.Nombre,
Nombre = derechohabiente.Nombre + " " + derechohabiente.ApellidoPaterno + " " + derechohabiente.ApellidoMaterno,
Genero = genero.Genero,
Edad = DateTime.Now.Year - derechohabiente.FechaNacimiento.Year,
Estado = estado.Nombre,
Derechohabiente = derechohabiente.TipoDerechohabiente,
Afiliacion = derechohabiente.Afiliacion
})
.ToList();
DataTable reporteDinamicoDataTable = ToDataTable.IListToDataTable(new List <DTOReporteDinamico>(reporteDinamicoList));
memoryStream = ToExcel.ExportToExcel(reporteDinamicoDataTable, fileName);
}
catch (Exception ex)
{
throw ex;
}
return(memoryStream);
}
public MemoryStream GetReporteEstatico(DTOReporteEstatico reporteEstatico, string fileName)
{
MemoryStream memoryStream = null;
try
{
IList <Derechohabiente> derechohabientes = _derechohabienteRepository.GetList(x => (reporteEstatico.IdEstado != null ? x.IdEstado == reporteEstatico.IdEstado : x.IdEstado == x.IdEstado) &&
(reporteEstatico.IdGenero != null ? x.IdGenero == reporteEstatico.IdGenero : x.IdGenero == x.IdGenero) &&
(reporteEstatico.RangoInferior != null ? (reporteEstatico.RangoInferior <= (DateTime.Now.Year - x.FechaNacimiento.Year)) : x.FechaNacimiento == x.FechaNacimiento) &&
(reporteEstatico.RangoInferior != null ? (reporteEstatico.RangoSuperior >= (DateTime.Now.Year - x.FechaNacimiento.Year)) : x.FechaNacimiento == x.FechaNacimiento));
IList <DTOReporteEstatico> reporteEstaticoList = (from encuesta in _encuestaRepository.GetList(x => x.FechaAplicacion >= reporteEstatico.FechaInicio.Date && x.FechaAplicacion.Date <= reporteEstatico.FechaFin.Date)
join derechohabiente in derechohabientes on encuesta.IdDerechohabiente equals derechohabiente.IdDerechohabiente
from tipoDestino in _tiposDestinoRepository.GetList(x => x.IdTipoDestino == encuesta.IdTipoDestino).Take(1).DefaultIfEmpty()
from temporada in _temporadasRepository.GetList(x => x.IdTemporada == encuesta.IdTemporada).Take(1).DefaultIfEmpty()
from viaje in _tiposViajeRepository.GetList(x => x.IdTipoViaje == encuesta.IdTipoViaje).Take(1).DefaultIfEmpty()
from genero in _generoRepository.GetList(x => x.IdGenero == derechohabiente.IdGenero).Take(1).DefaultIfEmpty()
from estado in _estadoRepository.GetList(x => x.IdEstado == derechohabiente.IdEstado).Take(1).DefaultIfEmpty()
select new DTOReporteEstatico
{
Destino = tipoDestino.Nombre,
TemporadaVacacional = temporada.Nombre,
Viaje = viaje.Nombre,
Genero = genero.Genero,
Edad = DateTime.Now.Year - derechohabiente.FechaNacimiento.Year,
Estado = estado.Nombre
}).ToList();
DataTable reporteEstaticoDataTable = ToDataTable.IListToDataTable(new List <DTOReporteEstatico>(reporteEstaticoList));
memoryStream = ToExcel.ExportToExcel(reporteEstaticoDataTable, fileName);
}
catch (Exception ex)
{
throw ex;
}
return(memoryStream);
}
public ActionResult InfoExcel()
{
var path = Server.MapPath("~/files/导出.xls");
FileStream fs = new FileStream(path, FileMode.Create);
DataTable dataTable = new DataTable();
dataTable.Columns.AddRange(new DataColumn[] {
new DataColumn("姓名"),
new DataColumn("班级Id"),
new DataColumn("性别"),
new DataColumn("身份证"),
new DataColumn("电话号码"),
new DataColumn("家庭地址"),
new DataColumn("入学时间"),
});
var list = unitof.CreateRespository <Student>().GetEntityList().Select(m => new { m.Name, m.GradeId, m.Sex, m.CardId, m.Phone, m.Address, m.EnrollmentDate }).ToList();
for (var i = 0; i < list.Count; i++)
{
DataRow dataRow = dataTable.NewRow();
dataRow[0] = list[i].Name;
dataRow[1] = list[i].GradeId;
dataRow[2] = list[i].Sex;
dataRow[3] = list[i].CardId;
dataRow[4] = list[i].Phone;
dataRow[5] = list[i].Address;
dataRow[6] = list[i].EnrollmentDate;
dataTable.Rows.Add(dataRow);
}
var work = ToExcel.Excel(dataTable);
work.Write(fs);
fs.Close();
work.Close();
return(File(path, "application/vnd.ms-excel", "导出.xls"));
}
//data to Excel
private void button6_Click(object sender, EventArgs e)
{
ToExcel.dataGVToExcel(dataGridView1);
}
public ActionResult Create(FormCollection form, CEDTS_Card card)
{
System.Data.DataTable dt = new System.Data.DataTable();//为excel创建表格
dt.Columns.Add("SerialNumber", System.Type.GetType("System.String"));
dt.Columns.Add("PassWord", System.Type.GetType("System.String"));
int CardCount = int.Parse(form["num"]);
card.CreateTime = DateTime.Now;
card.ActivationState = 0;
switch (card.CardType)
{
case 0: card.EffectiveTime = "一年"; break;
case 1: card.EffectiveTime = "一月"; break;
case 2: card.EffectiveTime = "30次"; break;
default: break;
}
card.CreateUser = _card.GetUserIDbyAccount(User.Identity.Name);
for (int i = 0; i < CardCount; i++)
{
DataRow dr = dt.NewRow();
var SerialNumberList = _card.SelectAllCard(null, 4, 4, 4, 0, "").Select(p => p.SerialNumber).ToList();
string txt = Guid.NewGuid().ToString().Replace("-", "").Substring(0, 16).ToUpper();
card.SerialNumber = txt;
if (SerialNumberList.Contains(card.SerialNumber))
{
i--;
continue;
}
string pwd = Guid.NewGuid().ToString().Replace("-", "").Substring(0, 8).ToUpper();
card.PassWord = _card.HashPassword(pwd);
CEDTS_Card c = new CEDTS_Card();
c.ActivationState = card.ActivationState;
c.ActivationTime = card.ActivationTime;
c.ActivationUser = card.ActivationUser;
c.CardKind = 1;
c.CardType = card.CardType;
c.CreateTime = card.CreateTime;
c.CreateUser = card.CreateUser;
c.Discount = 1;
c.EffectiveTime = card.EffectiveTime;
c.Money = card.Money;
c.OverdueTime = card.OverdueTime;
c.PartnerID = card.PartnerID;
c.PassWord = card.PassWord;
c.SerialNumber = card.SerialNumber;
_card.CreateCard(c);
dr[0] = txt;
dr[1] = pwd;
dt.Rows.Add(dr);
}
string[] listname = { "序列号", "密码" };
string[] cols = { "SerialNumber", "PassWord" };
ToExcel.tableToExcel(dt, listname, cols);
DoExcel();
return(RedirectToAction("Index"));
}
private void CreateXLSX_Click(object sender, RoutedEventArgs e)
{
ToExcel toExcel = new ToExcel();
toExcel.GetExcel();
}
