You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
461 lines
19 KiB
Plaintext
461 lines
19 KiB
Plaintext
public class ExcelUtil
|
|
{
|
|
|
|
public static bool toExcel(string filePath, ColumnModel columnModel, TableModel tableModel, string sheetName, bool isSelect)
|
|
{
|
|
try
|
|
{
|
|
com.azkoss.excellite.CellStyle headStype = new com.azkoss.excellite.CellStyle();
|
|
headStype.VerticalAlignment = com.azkoss.excellite.VerticalAlignmentStyle.Center;
|
|
headStype.HorizontalAlignment = com.azkoss.excellite.HorizontalAlignmentStyle.Center;
|
|
headStype.FillPattern.SetSolid(Color.LightGray);
|
|
headStype.Font.Weight = ExcelFont.BoldWeight;
|
|
headStype.Font.Color = Color.Black;
|
|
|
|
com.azkoss.excellite.CellStyle cellStyle = new com.azkoss.excellite.CellStyle();
|
|
cellStyle.VerticalAlignment = com.azkoss.excellite.VerticalAlignmentStyle.Center;
|
|
cellStyle.HorizontalAlignment = com.azkoss.excellite.HorizontalAlignmentStyle.Center;
|
|
cellStyle.Font.Color = Color.Black;
|
|
|
|
ExcelFile excelFile = new ExcelFile();
|
|
ExcelWorksheet sheet = excelFile.Worksheets.Add(sheetName);
|
|
int columns = columnModel.Columns.Count;
|
|
int rows = tableModel.Rows.Count;
|
|
int jump = 0;
|
|
for (int j = 0; j < columns; j++)
|
|
{
|
|
if (!columnModel.Columns[j].Visible || columnModel.Columns[j] is CheckBoxColumn || columnModel.Columns[j] is ImageColumn)
|
|
{
|
|
jump++;
|
|
continue;
|
|
}
|
|
sheet.Rows[0].Height = 18 * 20;
|
|
sheet.Cells[0, j - jump].Style = headStype;
|
|
sheet.Cells[0, j - jump].Value = columnModel.Columns[j].Text;
|
|
}
|
|
|
|
jump = 0;
|
|
for (int i = 0; i < rows; i++)
|
|
{
|
|
if (isSelect)
|
|
{
|
|
if (tableModel.Rows[i].Cells[0].Checked)
|
|
{
|
|
for (int j = 0; j < columns; j++)
|
|
{
|
|
if (!columnModel.Columns[j].Visible || columnModel.Columns[j] is CheckBoxColumn || columnModel.Columns[j] is ImageColumn)
|
|
{
|
|
jump++;
|
|
continue;
|
|
}
|
|
sheet.Rows[i + 1].Height = 15 * 20;
|
|
sheet.Cells[i + 1, j - jump].Style = cellStyle;
|
|
sheet.Cells[i + 1, j - jump].Value = tableModel.Rows[i].Cells[j].Text;
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
for (int j = 0; j < columns; j++)
|
|
{
|
|
if (!columnModel.Columns[j].Visible || columnModel.Columns[j] is CheckBoxColumn || columnModel.Columns[j] is ImageColumn)
|
|
{
|
|
jump++;
|
|
continue;
|
|
}
|
|
sheet.Rows[i + 1].Height = 15 * 20;
|
|
sheet.Cells[i + 1, j - jump].Style = cellStyle;
|
|
sheet.Cells[i + 1, j - jump].Value = tableModel.Rows[i].Cells[j].Text;
|
|
}
|
|
}
|
|
|
|
jump = 0;
|
|
}
|
|
|
|
excelFile.SaveXls(filePath);
|
|
|
|
Logger.logger("用户导出部分数据到Excel文件");
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ExceptionHelper.Current.Handle(ex);
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
public static bool toExcel(string tableName,string filePath,string sheetName)
|
|
{
|
|
DataAccess dba = DALFactory.getDataAccess();
|
|
try
|
|
{
|
|
com.azkoss.excellite.CellStyle headStype = new com.azkoss.excellite.CellStyle();
|
|
headStype.VerticalAlignment = com.azkoss.excellite.VerticalAlignmentStyle.Center;
|
|
headStype.HorizontalAlignment = com.azkoss.excellite.HorizontalAlignmentStyle.Center;
|
|
headStype.FillPattern.SetSolid(Color.LightGray);
|
|
headStype.Font.Weight = ExcelFont.BoldWeight;
|
|
headStype.Font.Color = Color.Black;
|
|
|
|
com.azkoss.excellite.CellStyle cellStyle = new com.azkoss.excellite.CellStyle();
|
|
cellStyle.VerticalAlignment = com.azkoss.excellite.VerticalAlignmentStyle.Center;
|
|
cellStyle.HorizontalAlignment = com.azkoss.excellite.HorizontalAlignmentStyle.Center;
|
|
cellStyle.Font.Color = Color.Black;
|
|
|
|
ExcelFile excelFile = new ExcelFile();
|
|
ExcelWorksheet sheet = excelFile.Worksheets.Add(sheetName);
|
|
|
|
Dictionary<string, string> cells = new Dictionary<string, string>();
|
|
|
|
bool isOpen = false;
|
|
IDataReader reader = dba.ExecuteDataReader("select isopen from t_tables where tablename='" + tableName + "'");
|
|
while (reader.Read())
|
|
{
|
|
isOpen = reader["isopen"].ToString() == "0" ? false : true;
|
|
}
|
|
reader.Close();
|
|
|
|
//取表字段信息
|
|
reader = dba.ExecuteDataReader(string.Format("select cnname,enname,showtype from t_detail where tablename='{0}' and ispublic='1' and isprimarykey='0' {1} order by orderno", tableName, isOpen ? "and companyid='" + Utils.getUser().CompanyId + "' " : ""));
|
|
while (reader.Read())
|
|
{
|
|
//日期字段 需要单独处理 标识为$字段名称$
|
|
if ("DateTime".Equals(reader["showtype"].ToString()))
|
|
{
|
|
cells.Add("$" + reader["enname"].ToString() + "$", reader["cnname"].ToString());
|
|
}
|
|
else
|
|
{
|
|
cells.Add(reader["enname"].ToString(), reader["cnname"].ToString());
|
|
}
|
|
}
|
|
reader.Close();
|
|
|
|
//获取当前可用字段名称
|
|
string fields = null;
|
|
foreach (string key in cells.Keys)
|
|
{
|
|
string str = key;
|
|
if (str.StartsWith("$") && str.EndsWith("$"))
|
|
{
|
|
str = str.Substring(1, str.LastIndexOf("$") - 1);
|
|
}
|
|
|
|
if (String.IsNullOrEmpty(fields))
|
|
{
|
|
fields = str;
|
|
}
|
|
else
|
|
{
|
|
fields = fields + "," + str;
|
|
}
|
|
}
|
|
|
|
switch (tableName)
|
|
{
|
|
case "b_area":
|
|
if (fields.IndexOf("feestandard") >= 0)
|
|
{
|
|
fields = fields.Replace("feestandard", "(select fees from b_fees where sysid=feestandard) as feestandard");
|
|
}
|
|
break;
|
|
case "b_archives":
|
|
if (fields.IndexOf("isinstall") >= 0)
|
|
{
|
|
fields = fields.Replace("isinstall", " (case isinstall when 0 then '否' when 1 then '是' end) as isinstall ");
|
|
}
|
|
if (fields.IndexOf("isopen") >= 0)
|
|
{
|
|
fields = fields.Replace("isopen", " (case isopen when 0 then '否' when 1 then '是' end) as isopen ");
|
|
}
|
|
if (fields.IndexOf("isstop") >= 0)
|
|
{
|
|
fields = fields.Replace("isstop", " (case isstop when 0 then '否' when 1 then '是' end) as isstop ");
|
|
}
|
|
if (fields.IndexOf("ismove") >= 0)
|
|
{
|
|
fields = fields.Replace("ismove", " (case ismove when 0 then '否' when 1 then '是' end) as ismove ");
|
|
}
|
|
break;
|
|
}
|
|
|
|
int col = 0;
|
|
int row = 1;
|
|
foreach (string name in cells.Values)
|
|
{
|
|
|
|
sheet.Rows[0].Height = 18 * 20;
|
|
sheet.Cells[0, col].Style = headStype;
|
|
sheet.Cells[0, col].Value = name;
|
|
col++;
|
|
}
|
|
col = 0;
|
|
row = 1;
|
|
string wherePart = isOpen?" and companyid='"+Utils.getUser().CompanyId+"' ":" ";
|
|
reader = dba.ExecuteDataReader(string.Format("select " + fields + " from {0} where 1=1 {1} order by sysid desc ", tableName,wherePart));
|
|
while (reader.Read())
|
|
{
|
|
col = 0;
|
|
foreach (string key in cells.Keys)
|
|
{
|
|
string str = key;
|
|
sheet.Rows[row].Height = 15 * 20;
|
|
sheet.Cells[row, col].Style = cellStyle;
|
|
|
|
if (str.StartsWith("$") && str.EndsWith("$"))
|
|
{
|
|
str = str.Substring(1, str.LastIndexOf("$") - 1);
|
|
sheet.Cells[row, col].Value =toDateTime(reader[str].ToString());
|
|
}
|
|
else
|
|
{
|
|
sheet.Cells[row, col].Value = reader[str].ToString();
|
|
}
|
|
col++;
|
|
}
|
|
row++;
|
|
}
|
|
reader.Close();
|
|
|
|
excelFile.SaveXls(filePath);
|
|
|
|
Logger.logger("用户导出全部数据到Excel文件");
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ExceptionHelper.Current.Handle(ex);
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
dba.Close();
|
|
}
|
|
return true;
|
|
}
|
|
|
|
public static bool toExcel(string tableName, string filePath, string sheetName,string wherePart,string orderBy)
|
|
{
|
|
DataAccess dba = DALFactory.getDataAccess();
|
|
try
|
|
{
|
|
com.azkoss.excellite.CellStyle headStype = new com.azkoss.excellite.CellStyle();
|
|
headStype.VerticalAlignment = com.azkoss.excellite.VerticalAlignmentStyle.Center;
|
|
headStype.HorizontalAlignment = com.azkoss.excellite.HorizontalAlignmentStyle.Center;
|
|
headStype.FillPattern.SetSolid(Color.LightGray);
|
|
headStype.Font.Weight = ExcelFont.BoldWeight;
|
|
headStype.Font.Color = Color.Black;
|
|
|
|
com.azkoss.excellite.CellStyle cellStyle = new com.azkoss.excellite.CellStyle();
|
|
cellStyle.VerticalAlignment = com.azkoss.excellite.VerticalAlignmentStyle.Center;
|
|
cellStyle.HorizontalAlignment = com.azkoss.excellite.HorizontalAlignmentStyle.Center;
|
|
cellStyle.Font.Color = Color.Black;
|
|
|
|
ExcelFile excelFile = new ExcelFile();
|
|
ExcelWorksheet sheet = excelFile.Worksheets.Add(sheetName);
|
|
|
|
Dictionary<string, string> cells = new Dictionary<string, string>();
|
|
|
|
//取表字段信息
|
|
IDataReader reader = dba.ExecuteDataReader(string.Format("select cnname,enname,showtype from t_detail where companyid =" + Utils.getUser().CompanyId + " and ispublic='1' and isshow='0' and tablename='{0}' and isprimarykey='0' order by orderno", tableName));
|
|
while (reader.Read())
|
|
{
|
|
//日期字段 需要单独处理 标识为$字段名称$
|
|
if ("DateTime".Equals(reader["showtype"].ToString()))
|
|
{
|
|
cells.Add("$" + reader["enname"].ToString() + "$", reader["cnname"].ToString());
|
|
}
|
|
else
|
|
{
|
|
cells.Add(reader["enname"].ToString(), reader["cnname"].ToString());
|
|
}
|
|
}
|
|
reader.Close();
|
|
|
|
//获取当前可用字段名称
|
|
string fields = null;
|
|
foreach (string key in cells.Keys)
|
|
{
|
|
string str = key;
|
|
if (str.StartsWith("$") && str.EndsWith("$"))
|
|
{
|
|
str = str.Substring(1, str.LastIndexOf("$") - 1);
|
|
}
|
|
|
|
if (String.IsNullOrEmpty(fields))
|
|
{
|
|
fields = str;
|
|
}
|
|
else
|
|
{
|
|
fields = fields + "," + str;
|
|
}
|
|
}
|
|
switch (tableName)
|
|
{
|
|
case "b_archives":
|
|
if (fields.IndexOf("isinstall") >= 0)
|
|
{
|
|
fields = fields.Replace("isinstall", " (case isinstall when 0 then '否' when 1 then '是' end) as isinstall ");
|
|
}
|
|
if (fields.IndexOf("isopen") >= 0)
|
|
{
|
|
fields = fields.Replace("isopen", " (case isopen when 0 then '否' when 1 then '是' end) as isopen ");
|
|
}
|
|
if (fields.IndexOf("isstop") >= 0)
|
|
{
|
|
fields = fields.Replace("isstop", " (case isstop when 0 then '否' when 1 then '是' end) as isstop ");
|
|
}
|
|
if (fields.IndexOf("ismove") >= 0)
|
|
{
|
|
fields = fields.Replace("ismove", " (case ismove when 0 then '否' when 1 then '是' end) as ismove ");
|
|
}
|
|
if (fields.IndexOf("isdelete") >= 0)
|
|
{
|
|
fields = fields.Replace("isdelete", " (case isdelete when 0 then '否' when 1 then '是' end) as isdelete ");
|
|
}
|
|
break;
|
|
}
|
|
int col = 0;
|
|
int row = 1;
|
|
foreach (string name in cells.Values)
|
|
{
|
|
|
|
sheet.Rows[0].Height = 18 * 20;
|
|
sheet.Cells[0, col].Style = headStype;
|
|
sheet.Cells[0, col].Value = name;
|
|
col++;
|
|
}
|
|
col = 0;
|
|
row = 1;
|
|
|
|
reader = dba.ExecuteDataReader(string.Format("select " + fields + " from {0} where companyid="+Utils.getUser().CompanyId+" {1} {2} ", tableName, wherePart,orderBy));
|
|
while (reader.Read())
|
|
{
|
|
col = 0;
|
|
foreach (string key in cells.Keys)
|
|
{
|
|
string str = key;
|
|
sheet.Rows[row].Height = 15 * 20;
|
|
sheet.Cells[row, col].Style = cellStyle;
|
|
|
|
if (str.StartsWith("$") && str.EndsWith("$"))
|
|
{
|
|
str = str.Substring(1, str.LastIndexOf("$") - 1);
|
|
sheet.Cells[row, col].Value = toDateTime(reader[str].ToString());
|
|
}
|
|
else
|
|
{
|
|
sheet.Cells[row, col].Value = reader[str].ToString();
|
|
}
|
|
col++;
|
|
}
|
|
row++;
|
|
}
|
|
reader.Close();
|
|
|
|
excelFile.SaveXls(filePath);
|
|
|
|
Logger.logger("用户导出全部数据到Excel文件");
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ExceptionHelper.Current.Handle(ex);
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
dba.Close();
|
|
}
|
|
return true;
|
|
}
|
|
|
|
private static string toDateTime(string dt)
|
|
{
|
|
try
|
|
{
|
|
return Convert.ToDateTime(dt).ToString("yyyy-MM-dd");
|
|
}
|
|
catch
|
|
{
|
|
return dt;
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private void OnExportClick(object sender, EventArgs e)
|
|
{
|
|
ToolStripMenuItem menuItem = (ToolStripMenuItem)sender;
|
|
SaveFileDialog fileDialog = new SaveFileDialog();
|
|
fileDialog.Title = "请选择文件存放位置";
|
|
fileDialog.FileName = "菜单信息表.xls";
|
|
fileDialog.Filter = "Excel文件|*.xls";
|
|
fileDialog.FilterIndex = 1;
|
|
fileDialog.RestoreDirectory = true;
|
|
|
|
switch (menuItem.Name)
|
|
{
|
|
case "PagerMenu":
|
|
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
|
|
{
|
|
string path = fileDialog.FileName;
|
|
WaitWindow.Show(this.ExcelExportMethod, "数据导出中....", new object[] { path, false });
|
|
}
|
|
break;
|
|
case "AllMenu":
|
|
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
|
|
{
|
|
string path = fileDialog.FileName;
|
|
WaitWindow.Show(this.ExcelExportAllMethod, "数据导出中....", new object[] { "t_module", path, "菜单信息表" });
|
|
}
|
|
break;
|
|
case "SelectMenu":
|
|
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
|
|
{
|
|
string path = fileDialog.FileName;
|
|
WaitWindow.Show(this.ExcelExportMethod, "数据导出中....", new object[] { path, true });
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private void btnExport_Click(object sender, EventArgs e)
|
|
{
|
|
SaveFileDialog fileDialog = new SaveFileDialog();
|
|
fileDialog.Title = "请选择文件存放位置";
|
|
fileDialog.FileName = "菜单信息表.xls";
|
|
fileDialog.Filter = "Excel文件|*.xls";
|
|
fileDialog.FilterIndex = 1;
|
|
fileDialog.RestoreDirectory = true;
|
|
|
|
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
|
|
{
|
|
string path = fileDialog.FileName;
|
|
WaitWindow.Show(this.ExcelExportMethod, "数据导出中....", new object[] { path, false });
|
|
}
|
|
}
|
|
|
|
private void ExcelExportMethod(object sender, WaitWindowEventArgs e)
|
|
{
|
|
ExcelUtil.toExcel(e.Arguments[0].ToString(), this.columnModel, this.tableModel, "菜单信息表", Convert.ToBoolean(e.Arguments[1]));
|
|
}
|
|
|
|
private void ExcelExportAllMethod(object sender, WaitWindowEventArgs e)
|
|
{
|
|
ExcelUtil.toExcel(e.Arguments[0].ToString(), e.Arguments[1].ToString(), e.Arguments[2].ToString());
|
|
} |